BIDS, SSIS and SQL Server


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 image 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.



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

Published by


Originally, this was a pretty darn boring post. Kinda like... Well I won't go there. Perhaps its still on the robotic side but... I could say I like music. Safe, generic and non-comittal. Or, I could say that I've been having a blast tuning up my old guitars, getting blisters on my fingers and turning the amp up past 2. Amazaing what a little overdrive and a half pressed wahwah pedal can do for a sound. Get that cool "Money for Nothin" vibe happening. I get a real kick out of reading old Sci Fi. Reading Asimov's vision about the future is really entertaining now. When he wrote much of the material, the items that were futuristic were day to day tools I used in the early part of my career. Microfiche and the like. I also remember that upstart Microsoft and MS DOS...and can you say Lotus 123? So maybe this is a little better than "I like to read and play music". My career is focused on Team Leadership and Technology. Both share the attributes of continual growth and education. Currently, I manage a team of 4 programmers as direct reports. I've been in this role since 08/2007. Prior to that, I was the team lead (on site) for integration with the customer. Customers ranged from local government to manufacturing and medical. Teams ranged in size from one to six additional team members. On the other side is technology. I've been keeping current with .net technologies focusing on C# and Sharepoint (2007/2010). Specialties Team Building and Management Technical Staff Recruiting Microsoft Visual Studio 7 through 2010 (VB.NET and C#.NET) Microsoft SQL Server 6.5-2008R2 (DTS, TSQL, SSIS, SSAS, and SSRS ) SharePoint 2010 (Office Integration, InfoPath, Site Management and planning) Windows Server 2008 R2 AD DS PowerShell Techology analysis Puchasng and working with vendors Microsoft licensing management and compliance Business Systems Analysis Traning plans Mentoring Training coordination.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s