May 10, 2012 at 3:53 am
First Question
How do I create a For each loop which can import 28 multiple flat files into their own table in SQL Server when each file has a different metadata/ column layout? The flat files are obtained from a network folder.
Second Question
The flat files are obtained from a network folder and parts of the filenames like below will keep changing, the file names will change depending on which of the 28 files been imported.
10.01_20052012_UK
10.02_21052012_US
10.01_22052012_UK
The only constant will be the first numbers before the underscore in bold which also indicates the column layout. How do I make SSIS pick the first part of the name from the network folder?
Any help greatly appreciated since I cannot find any information on multiple file import with different column layout anywhere on the internet.
Thank you.
May 10, 2012 at 7:05 am
SSIS doesn't support changing metadata.
You'll need to create a seperate dataflow for each distinct set of metadata.
Regarding the filename:
in a for each loop you can specify a wildcard for the filename.
This would become:
10.01*
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 10, 2012 at 7:38 am
I saw a presentation last year at the Boston SQL Saturday on using the data profiling task to create a meta data definition that could be used later in a data flow. The presenter was Ira Whiteside. Here is the link to the thumbnail of the presentation.
http://www.sqlsaturday.com/viewsession.aspx?sat=71&sessionid=3246
In a quick search I couldn't find any posts of his presentation. I suggest you Google a bit more than I did, or even contact Ira directly.
I think the short answer is that it is possible, but it is not easy. If you have a limited number of data formats you may find it easier to develop some testing or each file and then choose the correct data flow.
May 10, 2012 at 11:44 am
Daniel Bowlin (5/10/2012)
I saw a presentation last year at the Boston SQL Saturday on using the data profiling task to create a meta data definition that could be used later in a data flow.
Sounds interesting, but I would rather parse the flat file quickly with a script task to discover the metadata.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 11, 2012 at 9:42 am
Just create one column in flat file and use script component to split each row based on column delimiter. That should work.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply