Configuring a package using dtsconfig configuration files

Had someone ask me a question on how to configure a package using DTS Config files, since they had only had experience configuring using sysdtsconfig.

Open the package.

Right click and choose package configurations.

Click on “Add”

Click “Next”

Make sure “XML configuration file” is selected in the configuration type drop down.

Then select “Browse”

Then browse out to the dtsconfig file

You should get something like:

You will say “reuse existing” unless you are adding or CHANGING a configuration in the file.

IF you are just trying to GET a configuration from the file, then you just want to Reuse existing, and not change the file in ANY way.

If you want to ADD a configuration, but not CHANGE an old configuration, you still want to use “Reuse existing”

IF you want to overwrite existing configurations, as well as change/add configurations, then you use “Overwrite”

Now give your configuration a useful name, hopefully a standard within your company.
Now that you have added the configuration:

Select it, and hit Edit, and verify that it configures the elements you care about.

In this example, I am configuring the connection string of the ETLExamples object.

The file looks like:

<?xml version="1.0"?>

<DTSConfiguration>

   <DTSConfigurationHeading>

       <DTSConfigurationFileInfo GeneratedBy="TheMythTheManTheLegend" GeneratedFromPackageName="ExampleUsingDTSConfigurationFiles" GeneratedFromPackageID="{CD0DB029-8B6D-4994-B8C5-D0DE51A611A4}" GeneratedDate="12/13/2013 2:54:52 PM"/>

   </DTSConfigurationHeading>

   <Configuration ConfiguredType="Property" Path="\Package.Connections[ETLExamples].Properties[ConnectionString]" ValueType="String">

       <ConfiguredValue>Data Source=.;Initial Catalog=ETLExamples;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-ExampleSimpleCSVExport-{B3A912D4-3037-43F8-AE78-511D48B8BC26}LocalHost.ETLExamples;Auto Translate=False;</ConfiguredValue>

   </Configuration>

</DTSConfiguration>

As you can see, the “Path” has the exact name of my connection to configure (ETLExamples) sand the property (connectionstring) because it looks like this, when I hit edit, my edit screen will look like:

If it does not show this, it means that the name is not the same in your package.  If you are trying to use a common connection value, then you must name your package items (such as connections, or whatever you want to configure) the same name of the thing being configured in this file.

Calling your connection “MyETLExample” and expecting it to be configured, or expecting to easily match configurations or select it from the file simply would not be valid in this case.

SSIS: Writing to log files, but maintaining a single header.

So I had a small issue where I wanted to create a log file during the on error step of a precedence constraint.  For example, while dynamically pulling from a list of sites, one of the sites does not match the definition of a "valid" site, and the data flow component errors, but I don't want to error the entire package, and I only want to write to the log file IF there is an error with a site, but otherwise do NOT create a log file.

Well, I got this set up, and I had multiple steps writing to the error file, and I had the options set so that each time it writes it does not overwrite the file.

So I get a file that looks like:

Header
Record
Header
Record
Header
Record

But, I only want to write the header one time, instead of multiple times, but I have no way of controlling with the SSIS package is going to throw the errors.  The users may have "fixed" the site I was trying to fix.

So, after a bit of thinking, the solution I came up with seems to work pretty well.

I created an overall variable to track whether or not I have written an error or not, called User::HasWrittenError, and then set it initially to false.  In the step that writes the errors out, I put a script transform task in the data flow, and in the post execute of the data flow script component, I added the HasWrittenError to my script.



In the script task itself, in the post execute, I did the following:

public override void PostExecute()
    {
        base.PostExecute();

        Variables.HasWrittenError = true;
        /*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */
    }

So now that the code sets the HasWrittenError, I added this to each of the logging file steps.

In the file connection manager for the output, I added an expression to set the ColumNamesInFirstDataRow option to @[User::HasWrittenError] != TRUE.



What this allows is that after the first execution, when HasWritten error is "false" it will write the header on the first run, and also create the file, on the second execution through, HasWrittenError will be "true" and thus no header will be written to the file.