August 23, 2012 at 1:36 pm
Hello -
I'm creating an SSIS Package my end goal is for it to do the following.
1. Execute a store procedure (Source) on SQL Server A: were the results will go into a temp table on that (Source) SQL Server A:
then
2. Load the results of that temp table into a table on a (Destination) SQL Server B:
I tried a Data Flow Task with OLEDB Source within it but cannot define the Columns. Any insigt on what I can do to reach my end goal would be greatly appreciated.
Regards,
David
August 23, 2012 at 2:31 pm
I haven't done it in a while, but I think that you'll have trouble trying to use a temp table like that from SSIS, (because the second step may not be able to find the temp table created by the first step).
You might need to have your stored procedure return the results in a dataset to the SSIS job, and then have the second step consume that.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 23, 2012 at 2:36 pm
Lots of stuff out there to exactly do the thing you are rtying to achive. But in short :
1)Have an execute sql task running your SP. This SP will put the results in a temp table(which should really be an actual table) as accessing #tmp or ##tmp tables are not so straightforward to deal with within SSIS
2)Have a DataFlow Task set up. Inside the dataflow Task, have a datasource setup. In your case, I think you already have an OLEDB source. Specify the table(the same table as in step 1 you inserted records into). You can also specify a query btw just in case you want to filter the results.
3)Specify the cols by going on the cols tab.
4)Have a DEST set up (ADONET or OLEDB any). Click on the source connection....drag tghe green arrow on to the dest connectior.
5) Specify the mappings(src col to dest col) on the mapopings tab of the DEST connector
6)Run the package. the DEST table should have the required data.
Once again this is a brief desc of teh whole process. You can do a lot of other things in transformation like lookup, derived col etc.
Hope this helps.
August 23, 2012 at 11:57 pm
The easiest solution would be executing the stored procedure in the OLE DB Source, but not storing the results in a temp table but sending them to the dataflow. That way you can immediately write them to the destination.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 24, 2012 at 1:42 am
Koen Verbeeck (8/23/2012)
The easiest solution would be executing the stored procedure in the OLE DB Source, but not storing the results in a temp table but sending them to the dataflow. That way you can immediately write them to the destination.
Agreed.
Or you could use a persisted table rather than a temp table.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply