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.
- Drag a Data Flow task to the Control Flow, or just click the Data Flow tab and hit the link.
- Drag a OLE DB Source to the Data Flow. Configure it as follows:
- OLE DB Connection: Create one if you need to and point it to AdventureWorks2012.
- Data Access Mode: SQL Command.
- SQL Command Text:
SELECT YEAR(orderdate) as yrDate, SUM(totaldue) as totaldue
WHERE YEAR(orderdate) = ?
GROUP BY YEAR(orderdate)
- Click the Parameter box and configure it as follows:
- Click New Variable.
- Name: varYear.
- Type: Int32.
- Value: 2007.
- Click OK.
- 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:
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.
Now, we need to create a package. Do the following:
- Click SSIS > Package Configurations.
- Check “Enable package configuartions.
- Click Add.
- Click Next.
- Select Configuration Type: XML configuration file.
- 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.
- Click Next.
- 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.
- 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:
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.