0

I have some SSIS packages to copy some tables from Server1 to Server2. Now I need to update this packages to Replace Server1 for Server3. Can I do this or I have to delete the package and create again?

Thanks in advance Eliana

Eliana
  • 171
  • 2
  • 3
  • 13

1 Answers1

0

I may have misunderstood your original request. Preserving the old answer but I have added a different approach below.

Original Approach

To expand on @praveen's comment

When you run an SSIS package, there are two typical mechanisms for doing so. dtexecui and dtexec. The former is what is launched when you double-click a package in Windows Explorer while the latter is used from the command-line or SQL Agent. I find it's easier to use a UI (either SQL Agent or the the Windows GUI) to build up the command line. In your case, you will overwrite the value of the connection string on Server2. See also Executing the same SSIS Package with different parameters at different time

If the package needs to copy data from Server1 to two destinations (Server2 and Server 3) then you'll want to either run the job twice using configuration to switch destinations or modify the package to write to both destinations simultaneously.

  1. Create a new SSIS project in Visual Studio/SSDT
  2. Download the package from SQL Server. Copying SSIS packages deployed on SQL Server back to Visual Studio 2008
  3. Add the downloaded package into the project
  4. Open the package and add a Connection Manager to the Server3
  5. Inside the Data Flow Task, insert a Multicast transformation prior to the existing destination.
  6. Reconnect the existing destination to the Multicast
  7. Drag a new destination component (most likely OLE DB Destination) and wire it up to the Multicast
  8. Test
  9. Deploy package back to SQL Server

Updated Approach

In rereading the request, I believe your desire is to change the source, not add an additional destination. Current process is data from Server1 goes to Server 2. Desired state is Server3 to Server2. Your package probably looks something like

enter image description here

What is important is the name of the connection managers. Currently, the name of Server1 is embedded in SourceConnectionOLEDB. You will want to have the SQL Agent override that value when it runs.

Inside the current SQL Agent Job, click on the Data Sources tab (unless you're on 2012, then you'll be looking for the Advanced tab) There check the SourceConnectionOLEDB connection manager and then replace the value of Server1 with Server3

enter image description here

The Command Line tab should then look something like 

/FILE "\\serverB\c$\temp\Simple.dtsx"  /CONNECTION SourceConnectionOLEDB;"\"Data Source=SERVER3;Initial Catalog=SRC;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=false;\"" /CHECKPOINTING OFF /REPORTING E
Community
  • 1
  • 1
billinkc
  • 59,250
  • 9
  • 102
  • 159
  • An SSIS is an SSIS package. It matters not whether it was created in Visual Studio, the Import Export Wizard or directly through the SSIS object model. – billinkc Aug 09 '12 at 14:26
  • Updated my response, should alleviate the need to change your dtsx – billinkc Aug 09 '12 at 15:09