December 19, 2005 at 3:09 pm
Hi experts,
I am creating a DTS package to:
1. load the summary data in a Sales_Summary table, from DW database to ODS database. (both in Server 1)
2. update Sales table on Server 2, SLIM database.
The current tasks in my DTS package:
(1st step) a Microsoft OLE DB Provider for SQL Server connection links to another a Microsoft OLE DB Provider for SQL Server connection by Transform Data Task. This step load summary data from DW database to ODS database.
Then what should I do to connect the update action?
I tried to have an Execute SQL Task which executes the update statement to the linked server, and how to design the DTS to connect this Execute SQL Task item in this package? It's not allowed me to link or connect this Execute SQL Task to a Microsoft OLE DB Provider for SQL Server connection.....
Error: Defining precedences between the selected items is not valid.
I understand that I should link 2 task items, not a connection item with a task item, so what should I do?
Please assist me. Thank you.
December 19, 2005 at 10:34 pm
Based on the information provided, it looks like all you have to do is to create a connection to Server2 as OLEDB Provider for SQL Server, and then create an Execute SQL Task on this connection. The task would be linked by a precedence constraint to the end of your transform data task, and the new connection will just look stand-alone (not linked to anything).
Hope this answers your question.
Anatol Romanov
MCP, Sydney, Australia
December 22, 2005 at 2:25 pm
I have a number of tutorials written up on DTS all of which may be accessed from my web site, http://www.angelfire.com/vt2/hodentek/
There are many ohter tutorials on database related topics. I agree with Anatol in general.
January 2, 2006 at 11:53 pm
Another option would be
1. Create "Main" Package
2. Create "Child-1" package, which will do SQL-SQL data transfer
3. Create "Child-2" package, which will do SQL Update Stuff (Linked Server task)
4. In "Main" paclage, call "Child-1" package, then on-sucess, call"Child-2", else ERROR out
paul
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply