27 Apr

SSIS: Package Deployment

These are the steps for deploying a package from development to a production server.

Step 1: Create a configuration file.

These are actually created at run time and as such are optional.  It’s here because Microsoft has in here.

Step 2: Create a deployment utility.

A deployment utility is a folder that contains the files that you will need to run the package.

The following steps create the utility:

  1. Make sure that the project has been converted to the package deployment model.
  2. Right-click on the package and select properties.
  3. Select the Deployment tab.
  4. Set AllowConfigurationChanges to True.
  5. Set CreateDeploymentUtility to True.
  6. Click OK to close.
  7. Right-click the project and select Build.


Step 3: Copy packages to the target computer.

Exactly what it sounds like.

Step 4: Run the “Package Installation Wizard” on the target computer.

Perform the following steps:

  1. Open the folder.
  2. Double-click the manifest file (.SSISDeploymentManifest) to start the process.
  3. Follow the steps.

Yeah, this is kind of light on detail.


The main MSDN for package deployment:


09 Apr

SSIS: Package Configuration

I’m hoping this goes quickly.  It’s pretty clean once we get the first package configured.  Essentially, all you do is configure the package, create a configuration file, and roll, baby, roll.

Configuration files come in 5 flavors:

  • XML configuration file: It’s stored in XML.  It can contain multiple configurations.
  • Environment variable: Stored in an environment variable.
  • Registry entry: Stored in a registry entry.
  • Parent package variable: Contained in a variable.  It’s usually used to update child packages.
  • SQL Server table: It can include multiple configurations.

And, for this article, there isn’t a lot more than that going on.  So let’s do one.

Step 1: Create a new package and/or project, if need be.

You should know this drill by now (File > New

Step 2: Create a OLE DB Source.

  1. Drag a Data Flow task to the Control Flow, or just click the Data Flow tab and hit the link.
  2. Drag a OLE DB Source to the Data Flow.  Configure it as follows:
    1. OLE DB Connection: Create one if you need to and point it to AdventureWorks2012.
    2. Data Access Mode: SQL Command.
    3. SQL Command Text:
      SELECT YEAR(orderdate) as yrDate, SUM(totaldue) as totaldue
      FROM sales.salesorderheader
      WHERE YEAR(orderdate) = ?
      GROUP BY YEAR(orderdate)
    4. Click the Parameter box and configure it as follows:
      1. Click New Variable.
      2. Name: varYear.
      3. Type: Int32.
      4. Value: 2007.
      5. Click OK.
  3. Click OK.

Now, we take the easy way out.  Drag a UNION ALL to the Data Flow, connect the OLE DB Source to it, and enable the data viewer (double-click the path, select the Data Viewer tab, and check the “Enable data viewer” box.

If you run the package you should get the following results:

yrDate	totaldue
2007	47171489.546

Step 3: Configure the package.

You may need to convert to package configuration at this point.  To do that go to Project > Convert to package deployment, and follow the wizard.  You won’t need to do anything but Next or OK.

Properties to export.

Properties to export.

Now, we need to create a package.  Do the following:

  1. Click SSIS > Package Configurations.
  2. Check “Enable package configuartions.
  3. Click Add.
  4. Click Next.
  5. Select Configuration Type: XML configuration file.
  6. Configuration file name: Click the ellipse, and put it somewhere you can find again.  I used C:\testData and named the file myconfig.  Click Save.
  7. Click Next.
  8. Navigate to the bottom of the objects list and select the varYear variable.  It should select the entire variables path.  Note:  I tried selecting everything to export and it fried the XML configuration file by converting everything to its HTML equivalent < = <.  I was able to read it in Internet Explorer but unable to edit it.  I do not have a solution for this other than to not select everything.
  9. Click Next.  Click Finish.  Click Close.

If you navigate to the folder you save the config file to you should see your file.  Edit it with a text editor.  I used Notepad++.

The XML file will likely be one long line.  Use the Find option in your editor and search for 2007.  We’re cheating but there will be more than one varYear in the file so this is easier.  Change 2007 to 2005, save the file, and run the package.  You should get the following result:


yrDate    totaldue

2005    12693250.6264


And there you go, a working package configuration.

Obviously you can do a lot more than this with configuration files but I just wanted to run something through the system and make sure I had it nailed down.

This link had more information on package configurations.