A walkthrough and learning how to export & import ISPAC files from SSISDB and Visual Studio. ISPAC file referred to as a deployable output file generated from SQL server integration projects. When we build SSIS project it creates an ISPAC file under the bin folder. Besides, while deploying SSIS projects from Visual studio is equivalent to deploying ISPAC files in the back hood.
Assuming a scenario when we have already deployed SSIS project under the SSISDB catalog. Further, we want to deploy the same project to the other SQL server instance. Either we can deploy it from a Visual Studio, oppositely we can export an ISPAC file from SSISDB and Import the file on another server, as manageable as that.
Here, we have an AdventureWorksStaging project deployed under SSISDB. We want to create ISPAC directly from SSMS so that I can import it on another SQL server instance. Using the same ISPAC file you can re-create SSIS solution if necessitated using Visual Studio.
How To Export ISPAC file from SSISDB in SSMS
1.In SSMS, go to Integration Services Catalogs > expand the folder and navigate to your SSIS project.
2.Right-click on the project > Export.
3.Save the project as “AdventureWorksStaging.ISPAC” file.
Your ISPAC file is ready to be migrated to another SSISB integration catalog on the same or different SQL server instance.
How to Import ISPAC file in Visual Studio
We have had situations where we needed to create a solution from an ISPAC file. To investigate what SSIS package we had deployed. This can also help if you have accidentally deleted the original SSIS project solution and want to re-create it from an ISPAC file.
1. Go to Visual studio File > New > Project.
2. Navigate to Business Intelligence > Integration Services Import Project Wizard.
3. Give Name to your Project & Solution > OK.
4. In Integration Services Import Project Wizard > Next > Select Project deployment file > browse AdventureWorksStaging.ISPAC.
5.Click Next > Import. Your project is successfully imported in Visual Studio.
Following the above steps, we are able to re-create the SSIS project solution from imported ISPAC file.
Summary & Read More
In nutshell, we have learned how to export and import the ISPAC file in SSISDB and Visual Studio. This really helps to avoid rebuilding the project over and over again. Instead, we can quickly export ISPAC and import on other server instance. We also have the option to re-create the SSIS project using ISPAC file.
Read More and browser through more posts related to SQL Server on Tech-Recipes.