1

We just moved our sql server to a hosting company, and now all my dtsx packaged don't work.

I know that in a specific query you can use

    use SecondDatabase

but is there a way to do that with the server, too?

Goal: Not have to rewrite my code and paste [eih-dr01.db.org].livendb.dbo. infront of every table.

  • 1
    You can look into using SQL Alias. mssqltips.com/sqlservertip/1620/… Or you can edit all of the connection strings in your packages. – Jonathan Fite Jul 20 '18 at 12:44
  • 1
    Are synonyms an option? - CREATE SYNONYM [dbo].[OriginalTable] FOR [RemoteServer].[RemoteDatabase].[RemoteSchema].[RemoteTable] – Scott Hodgin Jul 20 '18 at 12:50
  • Both of those are options. Onwards to google! – James Jul 20 '18 at 13:01
  • 1
    @James - If your dtsx packages are stored in the file system, you could use Powershell to read each one, search and replace the connection string and rewrite the updated file. – Scott Hodgin Jul 20 '18 at 13:07
  • @ScottHodgin woah. Powershell is awesome. – James Jul 20 '18 at 13:10
2

Two options come to mind:

If your dtsx packages are stored in the file system, you could use POWERSHELL to read in each file, search and replace the connection string and rewrite the file. Here is an example:

(Make sure you make a copy of the folder BEFORE running the Powershell script, just in case):

$DtsxFiles = Get-ChildItem C:\Users\uswsh\Documents\*.dtsx -rec
foreach ($file in $DtsxFiles)
{
    (Get-Content $file.PSPath) |
    Foreach-Object { $_ -replace "localhost", "localhost1" } |
    Set-Content $file.PSPath
}

If your packages are not stored in the file system, you could dynamically generate CREATE SYNONYM commands that would point to the new location.

CREATE SYNONYM [dbo].[OriginalTable] FOR [RemoteServer].[RemoteDatabase].[RemoteSchema].[RemoteTable]
1

You should open it using SQL Server Data Tools (microsoft developer environment). This would allow you to specify a new connection string for this package.

1

Alias your connections to the new SQL Server using CLICONFG.EXE

Don't to forget about Client Config (CLICONFG.EXE). Client Config acts as a host file pointer system for SQL Server connections by aliasing the old SQL Server name with the new one. This is probably the fastest way to get up and running.

It must be configured where on the SQL Server where the DTS packages are running. It essentially says anywhere you'd connect to server A, connect to Server B instead. Be warned though this is one stop shopping. All connections from A will be passed to Server B as shown in Figure A.

p.s. CLICONFG.EXE is built into the Windows O/S.

Figure A: Cliconfg.exe allows you to alias your old connections to a new SQL Server

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.