Home > SSIS > Dynamic file name for Excel Connection Manager – SSIS

Dynamic file name for Excel Connection Manager – SSIS


I recently worked on a project that uses lot of excel files of same format. Those files are located in a local folder and my task is to upload all data into a table. It is not a one time task to complete this task using Import and Export wizard. So, I decided to design a SSIS package to handle this task for now and later.

Implementation:

I have a folder named ExcelFiles\ that contains my excel files. I am going to design a package with for each loop to iterate files in that directory and pass the file name to the excel connection string.

Excel file used in this example : Sample1.xlsx

Step 1: Create a package with an Excel connection and connect to the sample1 excel file.

pic1Step 2: Declare a variable “ExcelFileName” as string and Assign static value to the package variable. This value should be sample1.xlsx file name with full path.

variable

Step 4:  Add For each loop container and configure the task as given below.

pic2

Variable Mapping: assign fully qualified file name of the Foreach Loop container value to the package variable.

pic3Step 3: Configure Excel Connection Manager property to pass dynamic file name to excel connection string during run-time.

a. We have to set the DelayValidation property to true. This will tell the package loader not to validate the connection or file availability.

b. Set the ExcelFilePath property with package variable in the expression property.

pic4

Step 5: Add Data flow task in to the Foreach loop container

pic5Now, Go to Data Flow design interface then Add Excel source , OLE DB destination task and connect them together for data upload.

Add Excel Source task:

pic6 Configure Excel Source:

1. Select sheet name for excel data source

pic72. Add OLE DB Destination and connect to a database where you want to store the excel data. I have used AdventureWorks2012 database to store data in a dummy table.

pic8Step 6: Set DelayValidation property of Data Flow task to True. This will avoid validation  package loading/validation error. Because, we are using expression for excel source connection.

Step 7: Set the package execution mode to 32 bit.

pic9

Step 8: We have completed our package design. Now, Execute the package.

pic10If you have any questions, please write in the comment section.

Advertisements
  1. April 14, 2013 at 11:44 pm

    How do you do this when your tab name is something like “Data 12”, that is your tab name contains a space character. I can’t get this to work with brackets, single quotation marks, double quotation marks or anything! I have an Excel Source set up in a DTS Data Flow Task where I’m trying to set the SQL Access Mode to “Table Name or View Name Variable.” I also have a variable called tabname. I’ve tried setting the variable to [Data 12$], [”Data 12$”], [‘Data 12$’], “[Data 12$]” . . . Nothing I do works! Any suggestions?

    • April 14, 2013 at 11:52 pm

      Wow, I’m sorry! As it turned out, I didn’t need either the quotation marks or the brackets! When I stored the value variable as just plain Data 12$ with no brackets and no quotation marks, it worked fine!

  2. sidhu
    September 13, 2013 at 10:41 am

    How to design the ssis package if the Sheet names are changing… I have a scenario where the sheet name is changing daily.. the package is failing as it is unable to identify the sheet which I first mentioned…

  3. ActionT
    December 10, 2013 at 4:41 am

    Thank you Ayyappan Thangaraj this works well.

  4. ActionT
    December 10, 2013 at 4:59 am

    Does anyone know how to import password protected Excel files with SSIS. The password is always the same but unfortunately this is the only way our vendor gives us the data in.

    • December 19, 2013 at 6:39 am

      You can use Excel interop to disable the password or make a copy of the excel file without password and read it in SSIS

  5. December 1, 2014 at 7:47 am

    In section 3 a/b above I can not get to see all of the properties / expressions of the ‘Excel Connection Manager’. I right click the ‘Excel Connection Manager’ go to ‘properties’ and I only see this – http://bit.ly/11ICEUo – any help appreciated!!

  6. RJB
    April 20, 2015 at 12:32 pm

    I don’t know where or how to assign the sheet name variable during execution. What I usually do is to look at the sheet name in a script task and if it not what it should be I change it an save the workbook. Here the code, be sure to add an Excel COM reference to the script task.
    Public Sub Main()
    ‘This code will make sure the first spread sheet in the workbook is the name the connection manager is looking for “Opt_Out”.
    ‘If it’s not this code will update the sheet with the correct name.
    Dim filename As String = Dts.Variables(“FileName”).Value.ToString
    Dim xl As New Microsoft.Office.Interop.Excel.Application
    Dim xlsheet As Microsoft.Office.Interop.Excel.Worksheet
    Dim xlwbook As Microsoft.Office.Interop.Excel.Workbook
    xlwbook = xl.Workbooks.Open(filename)
    xlsheet = xlwbook.Sheets.Item(1)
    xl.DisplayAlerts = False
    If xlwbook.Worksheets(1).name = “Opt_Out” Then
    xl.ActiveWorkbook.Close(False, filename)
    Else
    xlwbook.Worksheets(1).name = “Opt_Out”
    xl.ActiveWorkbook.Close(True, filename)
    End If
    xl.Quit()

    xlwbook = Nothing
    xl = Nothing
    End Sub

  7. BAB
    June 24, 2015 at 2:20 pm

    Hello I followed this tutorial, must say a really good post. But that being said data from only the first spreadsheet is getting pulled several times what am I doing wrong?

    • June 30, 2015 at 10:07 am

      Configuration is set for dynamic file name only and sheet is always the first sheet. As you are saying that it uploads data several times then please check how many time does the loop of program executes.

  8. Khaja Moinuddin
    January 17, 2016 at 10:56 am

    Can u tell me Excel Dynamic Connection manager in your senario

  9. Richard
    March 16, 2016 at 11:09 am

    I ran into exactly the same problem as BAB, with the first Excel file being brought in 5 times and the filename parameter returning each of the 5 file names in the destination folder alongside each iteration in the Derived Column I had set up.

    My personal mistake was that I had not set the ExcelFilePath/Connection String to the filename variable.

    However, I have since corrected this and replicated the tutorial eactly, but the filename variable does not appear to work as the connection manager cannot find any of the Excel sheets…

    Any help would be appreciated, thank you.

    • March 17, 2016 at 11:17 am

      I believe you have set the delayvalidation property to true and Excel sheet name is not dynamically configured. But, if the all excel file has same sheet name than it will be easy to configure once and used for different file name.

      if you want to dynamically configure the excel sheet name then you have to use script task with Excel interop to get the sheet name and use a variable the package variable for excel sheet name.

      • BAB
        March 17, 2016 at 1:33 pm

        Finally after several trial and error steps later I figured out is there needs be a variable the actually holds each file name which of course will be dynamic in this case, which comes from for each loop. Also to take into consideration here is the delay validation has to be set to true for all (Loop and the dataflow)
        Hope this helps
        BAB

      • March 17, 2016 at 3:02 pm

        Awesome!

  10. December 25, 2016 at 5:13 am

    Not worked 😦

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: