November 3, 2014 at 3:27 am
Hi all,
I have about 30 different files and each have to be loaded into its own respective table. I have a Foreach Loop container which loops through the directory and extracts the data from each file and is supposed to load it.
The thing is that I want to do this with one package. So my Data Flow component would consist of a Flat File source and an OLE DB Destination. In the OLE DB Destination I use Table name and view name variable - Fast load as the Data Access Mode option. Of course the table name would be loaded by a variable name, call it DestTable. Actually I've googled and found a question that fits my scenario: http://stackoverflow.com/questions/10988337/ssis-ole-db-destination-dynamic-table-name
The answer to this question is that you cannot do that with native SSIS components. I am using SQL Server 2008 R2 Enterprise and was wondering whether it is necessary to use some exotic 3rd party component that probably won't get approved by my client (a big multinational company). Is it really impossible to accomplish this with the tools that Microsoft gives? Any guidance would be appreciated.
November 3, 2014 at 4:12 am
TheComedian (11/3/2014)
Hi all,I have about 30 different files and each have to be loaded into its own respective table. I have a Foreach Loop container which loops through the directory and extracts the data from each file and is supposed to load it.
The thing is that I want to do this with one package. So my Data Flow component would consist of a Flat File source and an OLE DB Destination. In the OLE DB Destination I use Table name and view name variable - Fast load as the Data Access Mode option. Of course the table name would be loaded by a variable name, call it DestTable. Actually I've googled and found a question that fits my scenario: http://stackoverflow.com/questions/10988337/ssis-ole-db-destination-dynamic-table-name
The answer to this question is that you cannot do that with native SSIS components. I am using SQL Server 2008 R2 Enterprise and was wondering whether it is necessary to use some exotic 3rd party component that probably won't get approved by my client (a big multinational company). Is it really impossible to accomplish this with the tools that Microsoft gives? Any guidance would be appreciated.
It can be done with native components, but only if the file structures and table structures are all the same. I am guessing that is not the case.
So it's not achievable in the way you desire – you'll need a separate data flow for each table. But you can, of course, keep all of these data flows in a single package, if that is what you want (which goes against most guidelines I have seen).
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 3, 2014 at 4:56 am
Yeah I'm not looking to create 30 data flows in the package as that would just be the same development effort for me. I was hoping to be able to loop through the directory load the data. I've been doing it the old way (separate package/data flow for each file) but I was thinking of a way to develop more efficiently.
November 6, 2014 at 4:42 am
If they are 30 different structured files going to 20 different structured tables then as Phil said there is no magic wand for this in SSIS.
However there is another way to crack this nut.
If you want to do this and keep doing this sort of thing then there is a way. BIML!
You would need to create a list of the files and their structure , the tables and their structure. Then using BIML you can loop through this and produce 30 different SSIS packages easily. Would it be worth doing it for 30 tables - possibly not. But if you created the code then it would be re-usable so next time you want to load another 20 files to different table you would only need to add the file and table details.
There is a stairway to BIML on SSC. Its a useful tool for mass producing similar SSIS packages. Chances are you could do the 30 by hand quicker but if you are repeating this again and again then BIML would be your friend.
See Stairways on the left and have a look.
E
November 6, 2014 at 4:55 am
Ells (11/6/2014)
If they are 30 different structured files going to 20 different structured tables then as Phil said there is no magic wand for this in SSIS.However there is another way to crack this nut.
If you want to do this and keep doing this sort of thing then there is a way. BIML!
You would need to create a list of the files and their structure , the tables and their structure. Then using BIML you can loop through this and produce 30 different SSIS packages easily. Would it be worth doing it for 30 tables - possibly not. But if you created the code then it would be re-usable so next time you want to load another 20 files to different table you would only need to add the file and table details.
There is a stairway to BIML on SSC. Its a useful tool for mass producing similar SSIS packages. Chances are you could do the 30 by hand quicker but if you are repeating this again and again then BIML would be your friend.
See Stairways on the left and have a look.
E
Sounds like you're quite familiar with BIML. May I ask what, if any, tools you use to write it, assuming you found Mist to be a bit pricey?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 6, 2014 at 5:06 am
Phil Parkin (11/6/2014)
Ells (11/6/2014)
If they are 30 different structured files going to 20 different structured tables then as Phil said there is no magic wand for this in SSIS.However there is another way to crack this nut.
If you want to do this and keep doing this sort of thing then there is a way. BIML!
You would need to create a list of the files and their structure , the tables and their structure. Then using BIML you can loop through this and produce 30 different SSIS packages easily. Would it be worth doing it for 30 tables - possibly not. But if you created the code then it would be re-usable so next time you want to load another 20 files to different table you would only need to add the file and table details.
There is a stairway to BIML on SSC. Its a useful tool for mass producing similar SSIS packages. Chances are you could do the 30 by hand quicker but if you are repeating this again and again then BIML would be your friend.
See Stairways on the left and have a look.
E
Sounds like you're quite familiar with BIML. May I ask what, if any, tools you use to write it, assuming you found Mist to be a bit pricey?
I write BIML directly in Visual Studio. If the Intellisense starts to fail (after using C# too much), I might edit in the browser at BIMLScript.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 6, 2014 at 5:28 am
I am in no way highly experienced in BIML. I have just used it to build some simple repeatable templates. Visual Studio works for me. Yes it is not brilliant. Thanks Koen I will look at the BIMLScript Editor
E
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply