December 11, 2014 at 11:00 am
Is there a good way to incorporate a ForEach Loop container for a scenario where I have about 100 different flat files, all with different formats into 100 different tables? I can do it fine if it was the same format. The only thing I could think of was doing a script with a format file or something similar to change the formatting. Or maybe even a reference table.
December 11, 2014 at 11:48 am
Dynamically changing the formatting would be pretty tough as SSIS likes to validate the metadata.
Two easiest methods I can think of on top of my head:
1) Foreach loop that runs a child package, dynamically switching which child package based on the file. This would let you loop through all the files in one process, and have proper metadata everywhere, but would force you to have a package for each file-type + destination table format.
2) Load into a staging table with a filename field and a text field holding all the fields of the file, and then use T-SQL to split the fields and assign them to the tables. This is probably most like what you want, but could get a bit hairy with implicit type conversions all over the place, and wouldn't really let you validate much.
December 11, 2014 at 11:58 am
Nevyn (12/11/2014)
Dynamically changing the formatting would be pretty tough as SSIS likes to validate the metadata.Two easiest methods I can think of on top of my head:
1) Foreach loop that runs a child package, dynamically switching which child package based on the file. This would let you loop through all the files in one process, and have proper metadata everywhere, but would force you to have a package for each file-type + destination table format.
2) Load into a staging table with a filename field and a text field holding all the fields of the file, and then use T-SQL to split the fields and assign them to the tables. This is probably most like what you want, but could get a bit hairy with implicit type conversions all over the place, and wouldn't really let you validate much.
These are the other things I went through to try to figure it out but it seems messy to do it either way.
December 12, 2014 at 7:54 am
You're trying to get a pig out of the mud. Not messy is likely not an option 🙂
And actually the first isn't messy at all, it is just almost as much work as not using a loop at all.
My only other suggestion would be looking into BIML. If the file formats are all the same style, and the ETL pattern is always the same, and you can determine the file format from the table structure, you may be able to generate the child packages for solution #1 with BIML, making it less work intensive and messy.
December 17, 2014 at 2:21 am
Are you buliding the query , reading the data from flat file ?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply