How to add SSIS Package to MSDB
How many of you know that you can add a developed SSIS package in MSDB? What are the advantages of this.
Consider a Scenario where you have stored a SSIS package on Disk(File System) and you are using this package to run a SQL Server Agent Job. Your job started failing and you dont know why. One of your DBA comes to you and says that yesterday there was a error and I modified the SSIS package and he had not taken a backup and he does not know where all he has done the changes, and he has not taken a backup of the Package. Now what, you need to sit for hours and troubleshoot and fix, isnt it time consuming.
There is simple solution for this. Add the package to MSDB and then make sure you backup MSDB Database daily or any required interval by you. Below are step by instructions to Add/Store SSIS Package in MSDB using Integration Services.
1. Open SQL Server Management Studio and Select Integration Services in Server Type, Enter the Server Name where Integration Service is Installed as shown below.
2. Once connected, Expand Stored Packages Right Click on MSDB and Click New Folder to create a Folder to store your packages, enter a Name(StorePackages in my example) and click ok as shown below.
3. Right Click on MSDB and click refresh to reflect newly created Folder.
4. Right Click on newly created folder StorePackages and Click Import Package.
5. In Import Package Window, select Package location as File System, Select the package path by clicking elipse button. By default the Package Name is taken from the Package you have selected, you can change the name to provide a More meaning ful name as I have given(TestCHKDB).
6. By default, the Protection Level will be "Keep Protection level of the Original package", change this to "Do not save sensitive data", so that no sensitive data is stored. To do this. click on the elipse button next to protection level, select "Do not save sensitive data" in Package protection level drop down and click ok as shown below.
7. When you are on final screen, as shown below, click ok.
8. Now expand the folder you have created and you can see your package here.
You are done. Now your SSIS Package is safe. How to use this package in Job, I will explain this in next article. You can find it here: Schedule SQL Server Agent Job from SSIS Package Stored in MSDB/Integration Services.