26 Apr

SSIS: View Code

I’d completely forgotten you could right-click on a package, click “View Code” and change the package’s XML configuration.  But, you can, and we will.

I’m going to create a very simple package.

Step 1:  Create a new project and/or a new package.

You know how to do this so do it and name them whatever you would like.

Step 2: Create the Data Flow task.

  1. Create a Data Flow task (drag and drop or click the Data Flow tab).
  2. Drag a OLE DB Source to the Data Flow.
  3. Configure the connection manager to point to AdventureWorks2012, set “Data access mode” to “Table or view” and pick a table.  I used [Production].[Location].
  4. Drag a Flat File destination to the Data Flow and connect the OLE DB source to it.
  5. Configure it to point to a file of your choosing.  I used C:\testData\junk.txt.  Make sure this is a new file, and not an existing file.  It may work but I didn’t test if it did.
View Code.

View Code.

Step 3: Use “View Code” to change the package.

In the Solution explorer, right-click the package, select “View Code” and do the following:

  1. Hit CTL-f to bring up the “Find and Replace” dialogue window.
  2. I searched for junk.txt but if you named your file something else search for that.
  3. Rename the file.  In my case, I changed junk.txt to notMyJunk.txt.

If you run the package you will see that the process ran, and it renamed the destination file to notMyJunk.txt.

Anyways, one more way to configure a SSIS package and I sincerely hope it’s the last one I write about.

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.