Reputation: 33
I have a SSIS package where I am trying to load data from an Excel filesource into an OLE DB destination. I want to load the file name as well into the target table rather a part of the original filename.
Can anyone provide some tips for this?
Screenshot of the SSIS package
Upvotes: 3
Views: 2815
Reputation: 306
If you don't want to parameterize, then using the Script task, capture the Connection String and then extract the ExcelFileName from the Connection string using Substring.
Here are the steps :
Use this script in your script task
public void Main()
{
// TODO: Add your code here
ConnectionManager con = Dts.Connections["EX_SRC_CON"];
string conStr = con.ConnectionString;
int firstCharacter = conStr.LastIndexOf("\\");
int lastCharacter = conStr.IndexOf(".xlsx");
string fileName = conStr.Substring(firstCharacter + 1, (lastCharacter - (firstCharacter + 1)));
Dts.Variables["User::FileName"].Value= fileName;
Dts.TaskResult = (int)ScriptResults.Success;
}
Note that "EX_SRC_CON" is your Excel Connection Manager, from this only, we will extract the Excel File Name. From the script, you can see that we are writing the Excel File into string "FileName"
Next, connect the Script task to your Data flow task (DFT). In the DFT, in the Excel Source, select the Data access mode : "SQL Command" and use the following script
SELECT *, ? FROM [PolicyList$]
PolicyList$ is your sheet name and we need to map the variable "FileName" to ? under Parameters tab.
Next connect the columns from Excel source to your OLE DB Destination.
Upvotes: 2