Export a .dtsx file and add to a new project in visual studio

Question

I have a .dtsx file which I have located within MSMS. I want to export it and then add as a new package in visual studio. Once I export agencySales, it becomes an .ispac file that cannot then opened or added to my solution.

I cannot quite remember how to do this - I believe we may have to download the whole project in MSMS but cannot remember exactly.

I'm very new to SSIS so please don't hate. Cheers

Answer 1

I'm guessing you're going to have a "bad time" with this project. Prior to the 2016 release, backwards compatibility wasn't a thing with SSIS projects. Even now, it's still hit or miss because of custom components, version dependencies, etc.

The differences in 2012-2019 packages is incremental whereas the jump between 2008 and 2012 can be the width of the ocean. 2012 introduced the Project Deployment Model which your screenshot indicates your source uses that development and deployment model. Which was a godsend as we could package up project parameters, connection managers, parameters etc into a single deployable quantum called the .ispac Those concepts did not exist in the 2008 release so going backwards is generally not doable as you might have concepts in the new that don't exist in the old (as well as tasks like Expression Task or just the binaries of the Script Task/Components that won't move well)

Now that I've laid out all the pain points you're going to experience, let's talk about getting that package out of the SSISDB. This is a multi-hop problem. It sounds like you don't have the original project AgencySales that created it. No problem - open a current version of Visual Studio that has SQL Server Data Tools installed and Add a New Project and under the Integration Services specific section (not the default Business Intelligence section), choose Integration Services Import Project Wizard. This will pull the entire project from the SSISDB and turn it into a complete SSIS project.

Select Integration Services Catalog and fill out the server and folder/project path AgencySales After you click through everything, you have a complete version of your project.

Now comes the hard part - backporting work. If it's a trivial package - one data flow task in the control flow and that being a Source to Sink type of data processing, you'll be better off just rewriting for 2008.

If it's something more complex than that, then you're going to need a lot more work and maybe it's better to continue hunting down all the nooks and crannies the previous developer took. I would not use that approach and instead, I'd use Biml Biml is a meta language that describes business intelligence objects - in this case, an SSIS package. Install the free add-on BimlExpress to your existing Visual Studio instance. Now you can right click on the package and select "Convert SSIS packages to Biml" When that completes, a BimlScript.biml file will be in the Miscellaneous folder.

Biml is Biml is Biml. It describes the SSIS elements as they are -> this is an "Execute SQL Task." How it's physically implemented in an SSIS package is quite version dependent so the Biml Engine takes care of translating ExecuteSQL into version specific package XML. Very cool concept that I've been working with for 7? years now.

Now, you're in a bit of a pickle as I don't remember whether Visual Studio 2010 was the bridge solution for 2012 packages or whether it was just the spruced up interface for 2008. If it targeted 2008, then grab the BimlExpress version that specifies "Download for Visual Studio 2010" Install that and in your "Integration Services Project 2" you should have a right click menu available at the Project level that says "Add new Biml File". Do that. In the empty BimlScript.biml that opens up, copy the contents from the VS2012+ version of BimlScript.biml into the empty file. Save. Right click on BimlScript.biml and choose Generate SSIS Package.

The paid-for product, BimlStudio greatly simplifies this rigmarole as you import the project into it and simply change the selector to emit 2008/R2 specific packages.