April 20, 2017 at 1:27 am
Hi I have one doubt in ssis.
how to load multiple source data with multiple destination(sql server databases).
source system have onefolder.in side folder have 3 flat files.eachfile represent one client informaon.
Foldername: Clients
clients folder have files like
source files:
empclient1_20170406.txt
empclient2_20170406.txt
empclient3_20170406.txt
source data for empclient1_20170406.txt
id | name|sal
1 | ab |10
source data for empclient2_20170406.txt
id | name|sal
2 | vb |20
3 | un |30
source data for empclient3_20170406.txt
id | name|sal
6 | ub |50
9 | in |30
Destination have 3 databases :Databasenames like client1 ,client2 and client3
Eatach database have one table is empclient and table structure also same.
excepting after data loading in empclient1 database
Datbase:client1..> table name :empclient have like below
id | name|sal
1 | ab |10
excepting after data loading in client2 database have empclient like below
id | name|sal
2 | vb |20
3 | un |30
excepting after data loading in client3 database have empclient like below
id | name|sal
6 | ub |50
9 | in |30
I need to implement one dataflow task dynamicaly using foreachloop for all cients
I Have Tried using 3 dataflow task/1 dataflow task, each client I configures soure file connection as well as destination connection.
That time connection have more in my package 3 source connection and 3 destination connections.
if I get multiple files that time also need to load using one dataflowtask.
example: source folder : clients have files like
empclient1_20170606.txt
empclient1_20170706.txt
then need to load two files related data load in client1 database related table.
I want to use only one source connection and one destination connection to load data for all cients related database tables.
please tell me how to resolve this issue in sql server
April 20, 2017 at 2:17 am
Why do you need to do this with just one connection manager for each source and destination? If you have one for each client, you can do your loads in parallel? Is this a coursework question - that's the most likely place to see arbitrary requirements like that?
John
April 20, 2017 at 2:45 am
I have to wonder why you have a database by client. This screams poor design on so many levels.
@john-2, the only reason I can imagine that the OP doesn't want to separate into many dataflows is because it isn't that they only have 3 databases, but could have an indeterminate amount of client databases. So although they've only stated they have databases [Client1], [Client2], [Client3], they might end up at a later point with a database called [Client499] (WHY!? >_< ). All of these have a single table, empclient. :sick:
You could achieve this, with looping, using expressions on your source and destinations. You could extract the database name using the file name by doing something like:SUBSTRING(LEFT( @[User::FileName], FINDSTRING(@[User::FileName],"_", 1) -1),4,LEN( @[User::FileName]) - 3 - 12)
This would translate "empclient1_20170420.txt" to "client1", "empclient499_20170420.txt" to "client499".
Hopefully you can work out where to go from there.
Honestly though, why do you have a different database (with a single table) for each client. Use a single database and denote your client in your table. What you have right now is awful practice, and will only cause you problems.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply