Import data from Excel using SSIS without knowing the file name

Question

I'm working on an SSIS package that will be used to import data from an Excel file into SQL Server. My current struggle is figuring out how to make the SSIS package bring in exactly one excel file without knowing the name of it beforehand. I have a directory that will contain between 0 and n excel files at the same time. I want to pull in only the file with the oldest creation time. Is this possible?

I'm using Visual Studio 2015 to build the SSIS package. My DB is in SQL Server 2016.

C B · Accepted Answer · 2017-10-18 13:42:01Z

1

To create a dynamic file connection:

Create a new Variable (Name Example: 'SourceFile') of datatype String.
In a 'For Each Loop Container' map that variable under the 'Variable Mapping' Tab and set the 'Enumerator Configuration' to the correct folder and file extension. The 'For Each Loop Container' will read the file from the location and assign the name of the file to the variable.
In the Expressions Properties of your file connection set the ConnectionString property to @[User::SourceFile]

This should make your file source dynamic. It will pick up the file no matter what it is named, but the format of the file will have to be consistent.

Using just SSIS tasks, I am not aware of how to utilize the create date of the files to pick the oldest file, but if the file name contains the create date of the file you could substring the date out of @[User::SourceFile] variable and store it in another variable with each execution of the 'For Each Loop Container' to determine which file is oldest.

edited Oct 18, 2017 at 13:42

answered Oct 17, 2017 at 20:33

C B

1,7176 gold badges20 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

M C Over a year ago

Thank you for your answer. The problem that I'm having is that I only want to bring in a single file at a time, not all of the files in the folder. If I do this, won't the package bring in all files in that location?

C B Over a year ago

If no other tasks are inside the For Each Loop it will only read the file name and assign it to @[User::SourceFile]. It won't move any files or do any other tasks unless you put those tasks in the loop. You could use the loop to only determine the name of the oldest file and once you know the name of the oldest file, assign that value to another variable (example: @[User::SourceFileMaxCreateDate]) and use that new variable in the ConnectionString property of the file connection

M C Over a year ago

Huh. That did not occur to me. Thanks!

Collectives™ on Stack Overflow

Import data from Excel using SSIS without knowing the file name

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related