May 20, 2014 at 8:33 pm
i am very new to ssis and wondering using foreach loop and inside of it data flow task can load the data from a file into multiple databases table. thanks in advance..
May 20, 2014 at 8:36 pm
The same exact data into multiple tables?
or
Different pieces of data into different tables depending on the criteria?
There is so little information here, we'd really just be throwing around guesses trying to help you.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 21, 2014 at 4:34 am
ihpk74 (5/20/2014)
....... load the data from a file into multiple databases table. thanks in advance..
If you really mean that then you'll need to look at the multicast transformation.
Regards
Lempster
May 21, 2014 at 3:12 pm
i do apologize for not being very detailed.
i have multiple databases in my sql server instance. in my case sample & tsql2012 is my target not all the databases. so in my foreach loop i am using data flow task to load the data from a file into those two databases table (in my case we called those tables tblLoadData with same name and structure in both databases). i want to know how i can make this loading process from a file to those two databases table dynamic.
as per SSCommitted, i am using multicast for it now to load the data in two different databases tables(tblLoadData), but wondering is this the right approach or industry standard or i am doing it wrong. just want to let you know guys again that i am very new to ssis and just started using it not less than 72 hours.
May 21, 2014 at 3:17 pm
Multicast is the appropriate transformation to use.
From the additional information, it sounds like the same approach I would take.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 21, 2014 at 3:26 pm
do appreciate your prompt response. also wondering, after loading data into tables, i do want to go back to control flow and run the stored procedure in both databases. so the question is how i can say i am running that stored procedure only on those two databases.
coss in select statement we can use [DATABASEname].[schema].tablename.
can i do it same in stored procedure like EXEC [DATABASEname].[schema].storedprocname
May 21, 2014 at 3:29 pm
ihpk74 (5/21/2014)
do appreciate your prompt response. also wondering, after loading data into tables, i do want to go back to control flow and run the stored procedure in both databases. so the question is how i can say i am running that stored procedure only on those two databases.coss in select statement we can use [DATABASEname].[schema].tablename.
can i do it same in stored procedure like EXEC [DATABASEname].[schema].storedprocname
Just create a couple of Execute SQL tasks and assign a connection string to each SQL Task that only attaches to those databases.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 21, 2014 at 3:33 pm
so you are saying i CANNOT use EXEC [DATABASEname](which is a variable in my case).[schema].storedprocname
it. it supposed to be two different execute sql tasks separately it won't be dynamic, correct..
May 21, 2014 at 3:42 pm
thx for all your help guys, i do appreciate it very very much...
May 21, 2014 at 3:44 pm
You have used multicast to send data to two databases at the same time. You want to execute a stored procedure in each of those databases after the data has been loaded, correct?
If you need to execute a single execute SQL task in two databases, you either need to loop to do it, or you need to create two execute sql tasks. The connection properties in the connection manager can be overridden with variable values. To do so, you should utilize an expression.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 21, 2014 at 3:55 pm
again, i do appreciate all your help and feedback. can't thx ya enough for that 🙂
May 21, 2014 at 4:05 pm
you are welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 22, 2014 at 3:18 am
ihpk74 (5/21/2014)
so you are saying i CANNOT use EXEC [DATABASEname](which is a variable in my case).[schema].storedprocnameit. it supposed to be two different execute sql tasks separately it won't be dynamic, correct..
If I understand the scenario correctly, your stored procedure execution is not truly dynamic is it, in that you want to execute it against two databases, but the names of those databases will not change, correct?
If so then what Jason has suggested is exactly the approach I would take. Yes, you can create connection strings dynamically, but it's very easy to over-complicate and get yourself tied up in knots. Try and keep things simple is my advice!
BTW, as a newcomer to SSIS, have a look at Andy Leonard's 'Stairway to Integration Services' on this site.
Regards
Lempster
Edited to add info about Stairway.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply