Changing server name and db name

• Layne-812700

  SSC Enthusiast

  Points: 168

  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.

• Alvin Ramard

  SSC-Forever

  Points: 41190

  More actions

  March 2, 2011 at 3:45 pm

  #1293788

  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: 259015

  More actions

  March 3, 2011 at 5:39 am

  #1293939

  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: 168

  More actions

  March 3, 2011 at 1:27 pm

  #1294304

  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: 182276

  More actions

  March 3, 2011 at 1:30 pm

  #1294305

  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