1

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.

Improve this question
1
  • If that is the only file in that directory that starts with "ABC " then I would use a foreach loop. Commented Feb 20, 2018 at 14:51

2 Answers 2

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

In my experience, you are not getting the file on a specific date (especially today). It is usually back dated.
1

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.
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.