Changing server name and db name

Question

Changing server name and db name

Layne-812700

SSC Enthusiast

Points: 178
More actions
March 2, 2011 at 3:39 pm

#235948

Hi,
We're in the process of migrating from SQL 2005 to 2008, and we have quite a number of packages. Can we script them out, then do a find-replace to change the server name and db name?
How do you suggest we go about changing all the connection managers of each SSIS package?
Thanks for all your help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

Layne-812700 (3/2/2011)
Hi,
We're in the process of migrating from SQL 2005 to 2008, and we have quite a number of packages. Can we script them out, then do a find-replace to change the server name and db name?
How do you suggest we go about changing all the connection managers of each SSIS package?
Thanks for all your help.

Sorry, but your packages need to be upgraded from SQL2005 to SQL2008. I would avoid trying any shortcuts. I would open each one in the 2008 edition of BIDS and make the required changes.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Layne-812700 (3/2/2011)
Hi,
We're in the process of migrating from SQL 2005 to 2008, and we have quite a number of packages. Can we script them out, then do a find-replace to change the server name and db name?
How do you suggest we go about changing all the connection managers of each SSIS package?
Thanks for all your help.

As Alvin said, do an upgrade of the packages.

If you have any package configurations that update the connection managers, you need to change the OLE DB providers (if you have OLE DB connections of course) in the configuration table/file.

SQL2005: SQLNCLI.1

SQL2008: SQLNCLI10.1

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Layne-812700 SSC Enthusiast Points: 178 More actions · Answer 3

Thanks guys, so no shortcut for it, guess I'll just have to make the painful task of opening and saving using BIDS 2008.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Opening an SSIS package in a text editor and changing the server name does work, I have done it that way to do changes en masse.

I do not know your situation but for the record not all upgrade paths require you to upgrade your SSIS packages from the 2005 format to run them against SQL Server 2008. Check out the dtexec notes in this article: http://technet.microsoft.com/en-us/library/cc280546.aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato