June 16, 2016 at 2:27 pm
I am running SQL Server 2012.
For each run, I have about 6 - 7 xlsx files with different data and these get loaded into 6 - 7 tables in the db.
While table names do not change from one run to the next, the xlsx files names do change.
I understand that i have to create 6 data flows - one for each file and run then in sequence.
But how do I make it so that the xlsx file name varies from one run to another and I have to give that as a parameter?
Thanks in advance for your help.
June 16, 2016 at 5:25 pm
Can you give us examples of how the file names vary? Is it just a datestamp added to a base file name? Examples will help drive the solution.
June 16, 2016 at 7:47 pm
Yeah, it is the date and time stamp of when the file was created.
June 17, 2016 at 3:26 am
Create a For Each Loop for each XL file and set the enumerator to Foreach File Enumerator (usually the default).
Set the Folder and Files - these should really be set from package variables using expressions.
Output the the fully qualified name into a package variable and use this variable in your XL connection manager.
Jez
June 17, 2016 at 8:08 am
If the files were all the same format, then the for each loop method would be my choice as well. But I'm guessing since you said they are different files and map to different tables, that they are all different in format.
If each of the 6-7 files are different in format and going to different destinations, I would not use a for each loop. If you go that route, you'll still need 6-7 data flows inside your for each and then logic to determine which data flow to use to process that specific file format.
I would create a variable for each of the file versions and then use an expression to combine the file base name plus the date format. Then, create individual data flows for each file. This method will process all files at the same time. This is assuming that all files are available at the same time. If your source files have different frequencies of when they become available, you'll need some logic to determine if the file is available before you move to a data flow. This can be done easily with a script task. Another route you could go in this scenario is to create individual packages for each file and only execute them when the files are fresh.
June 17, 2016 at 8:30 am
To clarify, I would have one ForEach loop for each different file with a single DFT with a single source and destination. No logic required within the DFT.
Jez
June 17, 2016 at 8:57 am
Yes, Jez, that would work just fine. Eliminates the need to check for file existence. Plus, you can use the for each setup to include the file mask for the base file names.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply