I have a production SSIS project and I need to change the destination server. Can I just edit the project or do I need to repackage it?

Question

I have a production SSIS project in SQL Server 2016 that creates and exports a flat file to another server. The destination server has reached end-of-life and I need to change the destination path to the new server so we can decommission the old server. Can I just edit the package or project in Visual Studio or do I need to recompile (redeploy? republish?)? I have never edited before, only created new projects however that was a few years ago and I am a little rusty.

Alternatively, I could copy the existing job, edit the copy, then run them in parallel first. Then I can disable the old project/package once I am confident the new one works. I'm not having much luck figuring out how to do this either.

Any help would be appreciated. Thanks!

Answer 1

If I'm understanding your question correctly, it sounds like your goal is to simply change the destination location for your report. You would need to update the connection manager for your flat file connection inside of your SSIS package.

You would need to edit your flat file connection manager's connection string (by loading up the connection manager properties and changing the value yourself or by changing the expressions, if it is parameterized). Once you have verified it is now pointing at the right server's location to save the report you are generating, you would rebuild the entire package and redeploy it, which would essentially overwrite the .ispac and .dtsx files on the deployment server with your updates.

Answer 2

Standard caveat of "it depends" but the most common case is that you can solve this through Configuration.

Right click on the Package (or Project) depending on how things are set up. In the "Connection Managers" tab, find the connection manager that corresponds to the flat file output (a strong naming standard helps). I have selected SO_61794511.dtsx and the Name is Flat File Conn... which then allows the right side menu to be populated.

Of interest here is ConnectionString. I am going to directly edit this to change from C:\ssisdata\input\so_61794511.txt to my new path D:\path\here\something\so_newthing.txt

Click OK 2x and the next time the package runs, it will use the configured value.

That's the easiest approach. You could accomplish a similar thing if you edit the job that runs the package to set the value at every execution but this just does it at a global scale.

Where this can go off the rails is if there's a expression applied to the ConnectionString property, e.g. the output file has a dynamic date in the file name. This is why I advocate for exposing Package or Project level parameters of a "base file path" concept. This allows me to change the path from C: (local development) to D: (server deployment) or even to a UNC path \server\share by setting a configuration instead of hard coding a path into the packages themselves.