July 27, 2006 at 1:49 pm
A little advance warning... I've been working in DTS for years and I'm starting my first SSIS package. So, I'm not up to speed on SSIS development so forgive me if this question stinks.
Here is the problem. Originally I had two databases on the same SQL Server 2000 server ("A" and "B"). I had a DTS package that processed data in "A". The pkg called "A" stored procedures that did stuff with the data in that database. Eventually the package executed a SQL Task that executed a stored proc in "A" that had a query like the following (query made simple to illustrate point):
--Update table in "A" when data in "A".table is present in "B".table
UPDATE dbo.tblMyTable SET MyField = 'SomeValue'
WHERE EXISTS (SELECT SomeField FROM B.dbo.tblOtherData
WHERE OtherDataID = dbo.tblMyTable.MyTableID)
The problem now comes in. We have moved database "A" to our new 2005 server. Database B still resides on a 2000 server. We have migrated the DTS package to SSIS and everything is working expect for this one stored procedure and the problem is obvious. "A" and "B" are no longer on the same server and therefore the proc fails when the UPDATE stmt above is executed.
My first choice for dealing with this is to simply include the server name in the SELECT stmt and be done with it. But we try to avoid this because as this proc moves from dev to test and to prod, we will have to make sure to change it. Not the best idea!
So my question is how could we redo this package and take advantage of new functionality in SSIS? It would seem to me there should be something in the data flow tasks that could take these two sources of data, combine them based on the conditions, and then update the table. If the sources were based on connections, then as the package moves from dev to test and onto production, we are simply updating the connections.
Thanks in advance.
July 31, 2006 at 8:00 am
This was removed by the editor as SPAM
August 2, 2006 at 5:01 pm
One possible solution would be to create a Synonym in your 2005 db pointing to the 2000 db - that way you'd only have to maintain the definition of the Synonym if the location of the 2000 db ever changes.....
August 3, 2006 at 6:58 am
Well, it appears to me that you have this problem, regardless of SSIS, because the SP needs to address DB's on 2 different server's.
Have a look at setting up 2 connections (DB_A and DB_B), and then using the lookup object. This allows the resultset to 'lookup' against the DB_B database.
Since you could drive the DB connections via configuration files, you would, in theorey, only have to update comfiguration files to move the db connection. In addition, in theorey, you could have several configuration files, and use the relevant one in the relevant environment (dev, test etc.).
You'll see me say 'in theorey' alot, because I've only writeen 3 SSIS packages, none of them have been delviered to produ yet, so I'm basing this on my limited experience and understanding of SSIS.
I did try and mock up a sample like this - it seems to be more-or-less the right way to go, but read up on the LOOKUP task....
HTH
August 3, 2006 at 8:29 am
Thank you TomT and Wanderer for your responses. Since the time I have posted this, I have came to both of your conclusions. We are currently leaning more to using synonyms, for several reasons. I haven't sat down to try using the Lookup object, but I see where it could probably work.
Thanks again guys...
August 3, 2006 at 8:37 am
Let us know which way you go, and why, if possible. That way I can learn a bit as well 🙂
have fun and good luck
August 4, 2006 at 8:15 am
I'm finding synonyms to be very useful, as well as many of the new features in 2005....
Good luck
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply