So, you have inherited a set of SQL Server 2012 Integration Services projects that have been deployed on a server. Your company do not use source control so you can’t easily see what was last published (Oh no!!). Or, you have deployed something on the server but you are not sure if it is the right version. Bottom line is you want to inspect or to edit a package that is already published on the server. Unfortunately at the moment there is no way of just quickly downloading that single SSIS package.
SQL Server 2012 Integration Services now is now making use of Project Deployment Model. For more information, read it here. Back to the issue at hand, if you want to get a copy of what is in the production, in summary, there are two “easy” ways of doing so.
A. Creating a new project in SQL Server Data Tools (SSDT) and import the project from existing catalog.
B. Exporting the project into ispac file via SQL Server Management Studio (SSMS).
Caveat: Both these options require access to the project on the server.
Option A: New Project
1. In the New Project dialog box of SSDT, browse to Installed Templates > Business Intelligence > Integration Services on the left navigation pane.
2. Choose Integration Service Import Project Wizard option, enter the Name, Location and Solution Name accordingly. Then click OK to proceed to the next step.
3. Click Next on the Integration Services Import Project Wizard window
4. On the Select Source dialog, choose Integration Services Catalog. Then provide the details of the project on the server that you wish to import.
5. Follow the rest of the instruction.
6. Once the project is created, you can browse to the package(s) that you wish to inspect. In my example here, I have 4 connection manager files and 9 dtsx files.
This option is one of the safest / simplistic ways of getting SSIS project from the server. All the components in the Project, such as Project Variables and Project Connection Managers are kept in tact.
Option B: The shortcut
1. Navigate to the SSIS project on SSMS; i.e. [Server Name] > Integration Services Catalogs > SSISDB > [Project Folder] > Projects > [Project Name] as shown on the picture below.
2. Right click on the [Project Name], then choose Export….
3. Choose the destination folder on “Save As” to save the .ispac file
4. Navigate to the folder where the file has been saved and rename the file extension to .zip file.
5. Browse the zip file and you should be able to see the .dtsx file(s). Please note that in this example, I have 4 connection manager files and 9 dtsx files.
6. Now you can extract the desired .dtsx file(s) from the zip file.
Note: If the server is in Production, you could ask your DBA to export the .ispac file of the project (Step 1 & 2). Then you can continue with Step 3.
Special thanks to Josh Fennessy (b | t), for providing Option B.
Would it work for SSDT Visual Studio 2012?
Update – 6 March 2013: Both options work for Visual Studio 2012 too. (Read Microsoft announcement on SQL Server Data Tools – Business Intelligence for Visual Studio 2012). At the time of writing (a few minutes of exploring SSDT Visual Studio 2012), everything works the same for Option A. There is a major face-lift with monochrome look.
Wrap Up
Integration Services in SQL Server 2012 introduces Project Deployment Model. This means that accessing a single SSIS package that has been published from the server is not as trivial. This article describes two ways of accessing the desired package(s) or the project as a whole from a server. Option A, which is importing the package from the Integration Services Catalogs is the safest way. Option B, is a shortcut method that is recommended only for advanced level users.
This article does not describe how the pros and cons of the new Integration services Project Deployment Model as it is covered in a couple of blogs as listed on the Further Reading section.
Further Reading
Can I deploy a single SSIS package from my project to the SSIS Catalog? by Matt Masson
Short Review on .ispac SSIS Deployment by Julie Koesmarno – my thoughts on Matt’s article above.
What’s New (Integration Services) on MSDN
28 Responses
Hello Ms SQL Girl,
Thank you very much for this article.
It saved me a lot of time reconstructing a deleted dtsx 🙂
Have a nice day
Best regards
Steen J, DK
Hi Steen,
Great to hear that the article helped. Thank you for stopping by and leaving a comment.
Have a nice day to you too.
Julie
Thanks soo much, I bumped around for a while and could not figure this out. You’re a life-saver!
Special thank’s , i spend so much time for Part B to export packages from catalog services . this article really helped me sooo much …..
Thank you so much, this has saved me hours of research. Are you presenting at PASS this year?
Hi John,
Thank you for stopping by – and massive apologies for the delayed reply. It’s been a very busy year.
I will be attending PASS Summit 2014. The session selection for PASS Summit has not yet been announced – fingers crossed! Hope to see you at PASS Summit if you’re attending too 🙂
Julie
Thank-you so much for this article, been looking all morning on how do to this.
Can we deploy a single SSIS package from my project to the SSIS Catalog.please any other ways.
Hi Srinu,
As far as I know there is not an easy way of doing it. You may want to check this thread on MSDN: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/da414fc4-5f17-4378-b408-450d4d18038e/sql-2012-ssis-single-package-deployment?forum=sqlintegrationservices
(Apologies for the delayed reply, it has been a hectic month)
Hope this helps.
Julie
Thanks is small word in front of what i have gained from this post. I have done add existing package tons of time prior to 2012 and was repeating the same step only to find out that i can do that for package on a file system or MSDB. I hope in near future we have something similar to add a package from SSISDB . I am a big fan of yours now and so glad found you. Thank you for all the working u doing and looking forward to see many more amzing and useful stuff .
You’re welcome.
Thank you for stopping by and leaving such kind words.
Hope you have a great weekend,
Julie
YOU SAVED MY DAY… thanks a ton for the wonderful post. I am pretty new to SQL 2012 and your post just helped me a lot
Thanks for quick and information information.
Thanks for you help. Save me a lot of time!
Really helpful. thank you
A belated thanks for posting this article. Around here, some folks would consider the packages, connections, and parameters within as your backup 🙂
Hello Julie,
I am having issue on finding package into SQL 2012 integration Services catalogs. I don’t see some pacakges are out there in SSISDB folder unter catalogs, but not the one I am looking, However I see them when I browse them under scheduled job : Steps–> Edit –> Package. server location is localhost (.) so I would like to import them into SSDT. any help will be appreciated
Hi RK,
You may need to ask your DBA to export the Project where the desired packaged lives, from Integration Services Catalog into an ispac file. From there, you can import the project to SSDT and edit it.
By the way, if the project in the Integration Services Catalog has been deleted, you would have been receiving an error similar to the following when you try accessing the step in the SQL Server job.
Invalid package path “\SSISDB\SQLNinja\SQLNinja\SimplePackage.dtsx” on the server “.\”. It cannot display the configuration of the package. (Microsoft.DatatransformationServices.DTSExecUI.Controls)
Let me know if you’re still experiencing an issue. May be you could send me a screenshot.
Thanks,
Julie
Thanks Julie, Finally I was able to see them using different account, issues was related to privilege. Thanks for your reply.
Thanks for the great article!
Thank you! Super helpful!
Thank you for posting this. Is it possible to write a SQL select statement to get the connection string information of all packages that are deployed to SQL Server 2014?
1.) load all of the *.dtsx packages that are deployed to the Integration Services Catalog
2.) Place the data into an XML column
3.) query the connection string of the column.
Hi Jeff!
Thanks for the visit and leaving a comment. Apologies for the delayed reply.
Great question!
I think this query might be able to provide what you need. This gives the latest version of the default connection string (which can be overwritten during execution).
WITH LatestProjectVersion AS (
-- Get the latest version of each project
SELECT xpr.project_id, MAX(xpr.object_version_lsn) AS object_version_lsn
FROM [internal].[projects] xpr
WHERE xpr.name = '
GROUP BY xpr.project_id
)
SELECT pr.name AS [ProjectName]
, pr.description AS [ProjectDescription]
, pr.last_deployed_time AS [ProjectLastValidated]
, pr.validation_status AS [ProjectValidationStatus]
, op.object_name AS [PackageName]
, op.design_default_value AS [DefaultConnectionString]
FROM [internal].[object_parameters] op
INNER JOIN [internal].[projects] pr
ON pr.project_id = op.project_id
AND pr.object_version_lsn = op.project_version_lsn
INNER JOIN LatestProjectVersion lpv
ON lpv.project_id = pr.project_id
AND lpv.object_version_lsn = pr.object_version_lsn
WHERE op.parameter_name LIKE '%.ConnectionString'
Let me know how you go with it.
Julie
Julie,
Sorry for the late response myself. When I came back to this blog after my initial post and I think I saw it deleted, I thought ‘Rude’! Lol, I rechecked this page today going through old web-links.
ANYWAYS, I ran your query and it did not come back with any results. After hashing at it for a few minutes, I removed the INNER JOIN to LatestestProjectVersion and received results. This query was helpful and it is a groovy approach to grabbing the connect string. Thank you for providing it.
NOTE: I ended up reverse engineering the whole *.dtsx package (a 225 MB monstrosity) and corresponding SQL Server agent job in order to figure out how data was being moved around. Basically, it was a slave DTSX package that moved data into a staging database only and then Stored Procs were called within the DTSX package to move data to one database and then another STEP within the SQL Server Agent job that called an identical set of Stored Procs on another database to move data into that second resident data warehouse. I have never seen anything like this. Hopefully, this will shine some light on why I was asking my initial question.
None the less, your original post was most useful as I find myself extracting packages from SQL Server quite often as our DBA opts to deploy packages from the network drive, as opposed to from TFS.
Thanks,
Jeff
Jeff, thanks for coming back to my blog post and adding more details about your approach! Much appreciated. It sounds like you have SSIS under control! Very good to hear.
Have a great day,
Julie
Fantastic post!
Thank you, Ms SQL Girl. This was immensely helpful!
Thank you very much! It saved me a lot of time.