MikeDavisSQL

The XML Task in SSIS allows you to parse through an XML file and read the nodes in the XML. In a previous blog I showed how to use a web service task to get the city and state when entering a zip code.

The results we got back from the web service were in XML format and we saved them into a variable named Results. This image shows the value in the variable

We need to get the city and the state out of this XML and save them each into a variable. You will need to create an XML task in the Control Flow of a package. Set the operation type to XPATH. The source is going to be the results variable. The destination will be the city variable. The second operand will be the node you want to be read, “//CITY” in this case. Last we set the XPATH Operation to values because you want the value of the city node.

The XML Task that retrieves the State value will be identical except for the Second Operand and the Destination variable.

After all of this is complete the package with the web service task will come before the XML Tasks. I placed a break point on the last XML Task and I am showing the results of the variables in the watch window. You can see that the results contain the XML, the City, and State variables contain the values from their respective nodes.

The Web Service task in SSIS can be used to call a web service command to perform a needed operation in your package. The results of most web services will be in XML Format. You can save these results into a variable or into a file. Either way, you can then parse the XML to find the node values you need. This can be done with the XML task in SSIS. I have a blog on the XML task also.

The Web Service I am using is a free demo service. It allows you to enter a zip code and returns the city and state associated with that zip code. This first thing you need on the package is an HTTP connection. The Server URL for this connection is http://www.webservicex.net/uszip.asmx?op=GetInfoByZIP. The rest of the settings for the connection are default.

Then drag a Web Service Task into the control flow. Set the connection to the HTTP connection you just created. You will need the WSDL file for the web service. This can be downloaded from the website where the web service is hosted. This file will need to be saved locally. Set the WSDL File to the location where you saved the WSDL file in the Web Service task in SSIS.

Click on the input node on the left window pane and set the input properties as shown below. These are drop down menus that are populated automatically by the WSDL file. Create a package variable with a data type of string to hold the zip code. Map that in the fields below as shown.

Under the output node set the Output type to Variable and create a results variable with a datatype of string.

If you enter the zip code of 32065 you will receive back this xml list.

This shows us the proper city and state associated with the zip code we entered. Now we can parse through the XML with an XML task and use this data. I show you how to do this in the next blog here.

When creating an SSIS package it is always a best practice to use variables, and parameters in 2012,  to avoid hard coding values into any part of your package. But there are some best practices involved with creating those variables/parameters also. The rest of this article will refer to variables. But each one could be a parameter except for the variables with expressions. This is because an expression will over write any value passed in.

Let’s look at a common situation in an SSIS package. A common need is to save a file with a date appended to it. This can be done with an expression and a variable. The first example is going to be using one variable without the best practices applied.

Of course we are going to need a file system task to save the file for us. One of the nice features of the file system task is the fact that the rename function can rename and move a file. You simply set the source and destination to a different location. You should never need two file system tasks back to back, with one doing the move and the other doing the rename.

The first thing we are going to do is create a file connection in the connection manager of a package. The file location will be c:\test\test.txt. We are going to back this file up in the folder c:\test\backup and rename the file to test_YYYYMMDD.txt. This will give us today’s date at the end of each file we back up.

This could be a flat file connection also.

The source in the File system task will be set to this connection. We could use a variable to pass this name in if we needed. In this example the destination is the important part. The destination will use a variable so we set the property IsDestinationPathVariable to True.

We will need a variable on the package also. I will create a variable called strFileName. It is always a best practice to create variables with the first few letters of the name describing the data type and camel case the rest of the variable name. Here is a list of some data types and the extensions to use.

Now we set the properties of the variable. Set the property EvaluateAsExpression to true. Then click on the expression ellipse and set the expression to:

“c:\\test\\backup\\test”+ (DT_WSTR, 10) (DT_DBDATE) GETDATE() +”.txt”

Notice the double slashes. These are necessary due to escape characters. For example “\n” is new line. Also notie the double quotes around the literals. Then we simple use three functions. Getdate gives us the date, DT_DBDate trims the time from the date, and DT_WSTR converts the date to a string with 10 characters.

This is an easy way to save a file with the date. But this is not using best practices in SSIS. What if the folder you want to back the file up into changes? What if the file extension changes? Using configuration files or tables you can update these values from outside the package. But if the variable contains an expression then configuration file value would be over written when the expression is evaluated. Therefore it is necessary and a best practice to use multiple variables.

Now I will show the same example using variable best practices in SSIS. We are going to keep the same connection in the connection manager and the source in the file system task will still be set to this connection. But instead of creating one variable for the backup file location we will create several variables. Here they are:

strBackupFolder – The backup location

strFileName – The name of the file without the folder or extension

strFileExtension – The extension of the file

strFullFileName – All the other variables combine in this one

strDate – The date represented as a string

strBackupFolder will be set to “c:\test\backup\”

strFileName will be set to “test”

strFileExtension will be set to “.txt”

strFullFileName will be set to an expression

strDate will be set to an expression

Now for the two expressions, the strDate will be just like the expression from the previous example. We use the same three functions to get a date string: (DT_WSTR, 10) (DT_DBDATE) GETDATE(). If you want the date to be in a different format then you will need to use the function Year, Day, and Month to control the format of the date.

The strFullFileName will simply combine all the other variables together to form a complete file name.

Now each of the variables that do not contain expressions can be updated from outside the package with configuration file or configuration tables and they still play nicely with the expressions in the other variables. In any packages you create try to ask the questions like “What if something changes?” This will save you tons of headaches in the future from maintenance. Always try to combine multiple variables with expressions, do not code everything into one variable.

Now in SQL 2012 we have parameters that make it easy, but configuration files are still an option and I still see a lot of my clients using them even on 2012 due to several reasons, but mostly because of the work to convert over.

SSIS packages are great ETL tools and can do just about anything you need in terms of ETL. Most organizations start out creating SSIS package one by one until they have dozens, hundreds, or even thousands of packages. I have worked with one client that ran over 4,000 packages. This can be a nightmare to maintain. You can save yourself a lot of work by deciding upfront how to configure your packages using configuration files or tables. We are going to discuss configuration files in this article.

We are going to look at a simple example of passing information to a package with a configuration file. Then we will go over using configuration files on multiple packages. Imagine running dozens of packages that point to a server and the server name changes. If you have a configuration file that is feeding this server name to every package you can make a single change to the configuration file and all the packages are updated. This can reduce your maintenance time significantly.

Here is a simple package example:

1. Drag in a script task into a blank SSIS package.

2. Create a string variable on the package named strData

3. Set the value of the variable to “Package”

4. Double click on the script task.

5. Add the strData variable to the read only variables.

6. Click Edit Script

7. Under the main function add the code MsgBox(Dts.Variables(“strData”).Value)

8. Click save and then close the window

9. Close the script editor by clicking ok

10. Run the package

When you run the package a popup box appears show the work Package. This is the value of the variable saved in the package. Now we will set up a configuration file on the package to give us the ability to change the value of the variable from outside the package.

1. Close the popup box and stop the package.

2. Right click in the control flow and select Package Configurations.

3. Place a check in Enable Package Configurations.

4. Click Add.

5. Click Next in the in the welcome screen if it appears.

6. Click on browse and select a location you have rights to write to.

7. Name the file FirstConfig

8. Click save and then click next

9. Click the plus next to variables >strData > Properties

10. Place a check next to value, notice the value on the right

11. Click Next > Finish > Close

We now have a configuration file on the package but the value is still the value from the package. Now we will open the configuration file and change the value. The configuration file is an XML file and I like to use XML notepad (Free from Microsoft) to open it. You will look for the configured value in this file. This configured value is the value passed to the package.

1. Open the folder containing the configuration file

2. Open the configuration file by right clicking and select open with

3. Select a program you can use to edit the file (Example: Notepad, Wordpad, XML notepad)

4. Check the configured value from Package to Config

5. Save and Close the File

6. Return to the package and run it

You should see a popup showing Config. This is the value from the configuration file. The value saved in the package is overwritten at run time.

This is just one small example of using configuration files. A popular way to use configuration files is on connections. When you have a connection on a package the properties of this connection show in the configuration manager. You can place a check next to the connection string property or you can place a check next to the individual elements that make up the connection string, initial catalog, server name, user name, and password. The user name and password are not needed when using windows authentication.

The password is not stored in the configuration file automatically even if you select it in the configuration manager. This is done by design for security. Microsoft did not want you saving your configuration file in plain text without knowing it. So you will have to open the configuration file and add the password. If you selected the connection string the password will go right after the user name. You must type in “Password =####;”, (#### Represents your password). Don’t forget the semicolon after the password. Now this configuration file can be used in any other package using this connection.

There is an issue when using a configuration file in multiple packages. The package that is using the configuration files will try to load every connection in the configuration file. It the package does not contain that connection it will fail validation and the package will not run. This causes an issue when trying to share a configuration file with many packages. There are three methods for handling these issues. You can create a configuration file for each package or create a configuration file for each connection or a combination of both.

The first method of a configuration file for each package works well if you do not have a lot of packages. If you have a thousand connections and fifty packages, a per-package solution is the obvious choice. If every package has a different set of connections this is almost necessary.

The second method of a configuration file for each connection works well if you have a lot of packages and fewer connections. If you have fifty connection and a thousand packages it will be much easier to maintain a per connection solution. In this situation a package with ten connections would have ten configuration files, each with one connection.

The third option is to combine the first two options in some form. For example, if you have one connection that is used by every package use this configuration file in every package. The other connections can have a package level configuration file. This is harder to maintain and you need document which packages are using which configuration files.

With all the options of configuration files it is important to plan out how you will use them in your environment before you create thousands of packages and create a maintenance nightmare. Planning your SSIS package configuration architecture is important and should not be over looked. It is easy to put it off when you only have a couple of packages. Most environments have their packages grow in number faster than anticipated. Planning your configuration files will save you a huge retro fit project in the future.