CODEWITHSUNDEEP
ssis
user3546085
user3546085

Reputation: 33

Excel file source SSIS, Excel file name retrieval

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

Answers (1)

MnM
MnM

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 :

  1. First create a variable "FileName" of type String.
  2. Next, use a Script task and add the above created variable in ReadWriteVariables

  3. 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"

  1. 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.

  1. Next connect the columns from Excel source to your OLE DB Destination.

Upvotes: 2

Related Questions