Lift and shift SSIS package

We use Sql Server Integration Services (SSIS) to import text files into our database. The dtsx files are deployed into the MSDB on the Sql Server. Everything works well with deployment with a powershell DSC script. But times change and we must deploy the packages to the catalog. Here is what we did.

Convert

The first step is to convert the project. We used the “Convert to Project Deployment Model” wizard that is started from the Project menu or the context menu in the Solution Explorer. Because this is a lift-and-shift we don’t replace the configuration bij parameters, so in the step “create parameters” we unselect everything. After the conversion we can still open the “Package Configuration” and see the properties are set from there.

Build

The reason we converted the project is because we need an .ispac file for the release to the catalog. During the build the ispac file is created. We gather alle dtsx and ispac files in an atrifact.

Deploy

Deployment will take the new ispac files in the artifact. With the code sample from microsoft (see references) we install the ispac into the catalog with DeployProject. This change is in the powershell DSC.

Conslusion

Our SqlJobs had to load the package from the catalog now. This was a minor change:  

SET @Command = N'/SQL "\"\FOLDER\PACKAGENAME\"" /SERVER "\"' + @Server + '\"" /CHECKPOINTING OFF /REPORTING E’
SET @command=N'/ISSERVER "\"\SSISDB\FOLDER\PROJECTNAME\PACKAGENAME\""  /SERVER "\"' + @Server + '\"" /CHECKPOINTING OFF /REPORTING E'

After these steps the SSIS packages are in the catalog. The impact on the system is minimal because the sqljob remains on the same location and handles the location change. Happy dev, happy dba.

References

About erictummers

Working in a DevOps team is the best thing that happened to me. I like challenges and sharing the solutions with others. On my blog I’ll mostly post about my work, but expect an occasional home project, productivity tip and tooling review.
This entry was posted in Development and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.