Abstract…
You have an SSIS package using server store and you need to update it because somebody changed the database underneath you. Cool, simple beans, easy to fix right? Really it is pretty simple and quick. Oh, there’s lots you can read about best practices, security and such but this article isn’t going there. You may see something like …
“decrypt protected XML node "PassWord" with error 0x8009000B "Key not valid for use in specified state.". “
if all the steps have not be done.
The following order of steps should work for you just fine…<insert boilerplate disclaimer…these steps worked just fine on my systems, you systems may vary and the process is suggested as learning content only…ok, that should do it. Remember, no guarantees>
Step one…say hello to my little friend, the package
A best practices note would be to work from Source Control (TFS, of course) so, open the last version of the project from source control.
Once the package is open, on your control flow tab, you should see a little warning icon on your dataflow task (assuming that the issue comes from a schema change). The Icon looks like…
Love the Icon. You’ll get to see it a lot here…
Double click on the pump with the error and that will take you to the data flow tab. On your data source, you should see the warning icon again… step 2 will address how to fix that.
Step two… sending my little friend away…
For the source, the process is simple if your setup is simple. Hey, if you’ve already done some tricky stuff, your level is probably above this article. Anyway, double click on your source and you’ll get a warning that the underlying meta data has changed. Go ahead and answer “yes” to the question and this will bring up the Source Editor. In this case, the source is an OLE DB Source.
Here I would suggest clicking on Preview to verify you’ve not got any errors with your data pull (especially if the pull is done with a SQL statement). Make any necessary changes and reclick on Preview.
Repeat this process for all the Control Flows that have the warning icon.
Now comes the fun part, adding the package back to the server.
Step three… Import?
You are working from the server and the package runs fine within BIDS (Business Intelligence Development Studio). You go to the server (open up an SSIS instance) and the package still wont run. Huh!
On the one hand you think;
- package on server
- package run good
- package on server still bad
While you may be running bids out of the MSDB store, the compiled package has not been loaded back into the package store. This is where “import” comes into play.
First, back in bids, make sure you “BUILD” your project. Once this is completed, a file is create in the \bin directory under your project. For example: <path>\..\SSIS File\PDA Project\PDA Project\PD\bin
Note:In your project, if you click on the package, you’ll see the “Full Path” in the properties box. This is where the \bin folder will be created when you “Build” the project.
Ok, project built and we’re done right? Not yet.
The next step is to go back to SSMS (SQL Server Management Studio) and open up the SSIS server instance. Here you will see…
MSDB is what you want to work with. Right clicking on MSDB brings up the following sub menu;
Click on Import Package and you get the file explorer
So far pretty straight forward. The next step will change how the package actually will or won’t run on the db server.
Open up the Protection level by clicking on the … right above the HELP button. This opens up the following dialogue
I’m using server storage and roles for access control because this is running in the context of a SQL Job with a predefined account that has appropriate access to all locations needed in the package. For more information go to:Package Protection Levels on MSDN
Go ahead and click through the OKs.
Testing the result…
When you were in BIDS you clicked on the 
to make sure it ran. There is not a nice ICON in SSMS. N SSMS, right click on the package and then click on Run Package
You will then be presented with the Execute Package Utility. To run in your user context, leave “Use Windows Authentication Checked”. If you have a specific service account native to SQL Server, you can use that. Click on Execute to make sure the package runs.
Summary
That’s pretty much it. To recap. This process opens an existing package from the MSDB store. Modifies then rebuilds the package. The last step is re-importing the package back into SQL Server. Tools used:
BIDS – A part of the Visual Studio IDE focused on Business Intelligence
SSMS – SQL Server Management Studio
Integration Services Server – Imports and manages packages
Import and Export facility to Import the new build into the server
Technical Musings, Opinions and Articles 