SSIS Import

Question

Post reply

SSIS Import

anthony.hart

SSC-Addicted

Points: 417
More actions
October 20, 2010 at 2:39 am

#230102

Hi
Is it possible to import an incrementing file in SSIS using a different file name each day?
For example – on Monday I want to import “file1” on Tuesday I want to import “file” and so on.
This is so I can set a package up in SQL server to run everyday using a different file.
If not, are they are similar work arounds?
Thanks,
Ant

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

you can , as long as the files are the same format.

you need to add an expression to the file connection manager and make the connection string property dynamic.

anthony.hart SSC-Addicted Points: 417 More actions · Answer 2

Hi

the files are the same format and will be called something like "file1", "file2", "file3", "file4" and so on.

How would i created the file connection manager string dynamic?

thank you

steveb. SSC-Forever Points: 46733 More actions · Answer 3

First of all you will need a way to work out the filename based on the day of the week and then assign this name to a variable. You could possible use a lookup table with a query that uses getdate() as a filter, or you could build some logic in a script task to assign the correct name to the variable

After you have the variable populated then you need to

Right click on the file connection in the connection manager then select properties and expand the Expression node. in here add an expression for the connection string that will include the static information such as drive and directory and will also include your variable that you have created. This will be used to build the connection string

something like

"D:\\LoadFiles\\" + @[User::varFileName]

should work.

a much better explanation can be found in the last part of this article

http://www.mssqltips.com/tip.asp?tip=1084"> http://www.mssqltips.com/tip.asp?tip=1084

anthony.hart SSC-Addicted Points: 417 More actions · Answer 4

i've done the expressions for the connection string and that works fine - but how would I could go about running incremental files ...so i would run the job one day - it runs 'file1', the next day it ran 'file1' is that possible?

failing that is there a way to pick the last added flat file from a folder based on a date?

if that makes sense?

thanks

steve block SSCertifiable Points: 7698 More actions · Answer 5

As previously mentioned, you can assign a variable in a script task which is a Visual C++ or Visual Basic program.

You assign the variable to the Script Task ReadWriteVariables property and then you Edit Script.

You could use a date function on the current date to pull out the day of the week and then assign the appropriate value to you variable like:

' Visual Basic example

Dim thisDay As Integer = Microsoft.VisualBasic.DateAndTime.Day(Now)

sDay = CStr(thisDay) ' assign day of week

Dts.Variables("varFileName").Value = "file" & sDay

Steve