0

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!

Kelly
  • 1

2 Answers2

0

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.

cdbullard
  • 143
  • 2
  • 10
  • Can I redeploy with a new name? I don't want to kill the current job until I know the new connection works. I am only changing the UNC path. Thx. – Kelly Apr 20 '22 at 21:11
  • If you're able to run the package locally, you could test to ensure your connection string is valid before you redeploy the package. I would recommend doing it that way and then redeploying over the existing package as opposed to renaming it to avoid confusion in the future. – cdbullard Apr 21 '22 at 04:13
  • Thank you I edited the original project in VS then repackaged and redeployed. I also edited the connection string and had to create a Share on the new server. Success! Thank you everyone for quick response! – Kelly Apr 22 '22 at 14:43
  • Absolutely! Glad that was successful for you and that the issue is resolved. – cdbullard Apr 22 '22 at 22:15
0

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.

enter image description here

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.

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • The name is static and I use an UNC path. It is the UNC path that is changing. I can easily edit the ConnectionString. I just didn't know if I had to redeploy it. – Kelly Apr 20 '22 at 21:09
  • Nope, once you set the Configuration, subsequent executions will use those default values (until explicit values are provided in the job definition) – billinkc Apr 20 '22 at 21:51
  • Thank you I edited the original project in VS then repackaged and redeployed. I also edited the connection string and had to create a Share on the new server. Success! Thank you everyone for quick response! – Kelly Apr 22 '22 at 14:44