CODEWITHSUNDEEP
ssisssis-2008
Danny
Danny

Reputation: 65

How to extract data from excel file into database with dynamic file name SSIS

Can you guy please help?

I have a problem to load data from excel into database with dynamic file name in my source files.

For example, for this month, my filename is ABC 31122017.xlsx. I successfully loaded data from each tab in this excel file into database.

But how do I make it dynamic? For example next month I have excel file ABC 31012018.xlsx. How to make the job dynamic to pick up the new file?

I able to put the date in variable, but I don't know how to proceed with the filepath in SSIS.

@[User::InputPath] + "ABC " +  @[User::Report_DT_DDMMYYYY] + ".xlsx"

I used this in Expressions in the Connection already, set up ExcelFilePath, but it couldn't work.

As in Excel Source connector in SSIS, I already chose the 31122017.xlsx and chose the first tab. But after I put in the Expressions, it couldn't find the first tab I chosen already.

Please help me guys. Thank you.

Upvotes: 1

Views: 1551

Answers (2)

KeithL
KeithL

Reputation: 5594

Using a foreach:

  1. Set up a string variable fileName (put in the actual file path / file name so you can develop your package.
  2. Add a Foreach Loop (File Enumerator which is default)
  3. Set Expression for Directory = @InputPath
  4. Set Files to the proper mask for your excel file (i.e. "ABD *.xlsx")
  5. Go to variable mappings and link to fileName
  6. Add an Excel connection and connect to your actual file.
  7. Set an expression on properties to ExcelFilePath = @fileName
  8. Delay Validation
  9. Develop your data flow(s) as normal.

Upvotes: 1

DatabaseCoder
DatabaseCoder

Reputation: 2032

May be below explanation will help you in overcome this issue (I have SSIS 2012) -

  1. First SSIS variable will hold date value i.e., "20180218". Variable Name- TodayDate. This variable value will be change according to today date.
  2. Second SSIS variable will hold FileName i.e., ""D:\SSIS\StackOverFlowTest1\InputFiles\AB " + @[User::TodayDate] + ".xlsx". Variable Name- FileNameExcel.
  3. Create connection manager for excel and under its properties window change expressions and set ExcelFilePath to "FileNameExcel".enter image description here
  4. Change "Delay Validation" to True under "Data Flow Task" property.enter image description here

Upvotes: 1

Related Questions