During my last SQLLunch, Introduction to SSIS, I was asked an interesting question by one of the attendees. He asked, how do you edit a package once it’s saved in the SQL Server package store? My response was to open your SSIS project and edit the package from Business Intelligence Development Studio (BIDS). His response was, I did not save the project. Huh, what? That’s what I thought. He tried to explain further, but I did not quite understand. He said I will be at the user group on Wednesday night, which I spoke at, and elaborate a little.
As promised he arrived on time at the user group meeting ready to explain his problem and expecting a solution. After we chatted for a little I realized the problem. He asked me to open SQL Server Management Studio (SSMS) and connect to my local SQL Server Integration Services (SSIS). Then he said expand the Stored Packages folder then the MSDB folder.
He then said, see there they go, but how do I edit them. Then I understood. When he completed a package it was saved to the SQL Server, and once he was done he closed the project without saving. As a result, the packages resided on the server, but he did not have the project that contained his packages. So he could not edit them.
I explain to edit them, open BIDs and create a new SSIS project. Then right-click on the SSIS packages folder in the Solution Explorer and select Add Existing Package and the following dialog box will appear.
Since his packages were saved on the SQL Server, I told him to accept the default, SQL Server, for the Package location. Then I told him to choose his server name, and finally click the ellipses button next to the textbox labeled Package path and the following dialog will appear:
Select the package you want to edit and it will be included in the project. Once complete double-click the new package in the solution explorer and you can begin editing the package. I explained to him to always save his SSIS projects when complete so he could always open the project for editing to avoid the extra steps.
So for all of you that don’t save your SSIS projects and you want to edit them after they have been deployed, follow these steps.
Talk to you soon
Patrick LeBlanc, MCTS
Founder www.TSQLScripts.com and www.SQLLunch.com.
Visit www.BIDN.com, Bring Business Intelligence to your company.
When I add existing package, it always creates a copy of the package that is stored in sql server and saves it on the file system. How can I edit the actual package (not a copy) that is saved in sql server?
ReplyDeleteThanks! I saved a package with the import/export wizard and then was like HOW DO I OPEN THIS THING? haha - glad you took the time to write this up, thanks!
ReplyDelete