SSIS 2014 Package Downgrade

Recently I had a need to be able to take an SSIS package created using SSIS for SQL Server 2014 and add the package to a preexisting SSIS 2012 solution. By default, it is not possible to take the package and add it to the existing 2012 solution as the package format is not backwards compatible.

After a fairly quick Google search, I came across a blog post by Vanie Castro who explained what he manually did to an SSIS 2014 package to ‘downgrade’ it to SSIS 2012. His detailed post can be found here. Following these instructions, I was able to successfully take my 2014 package and downgrade it and add it to my SSIS 2012 solution.

There was still some further adjustment to do to the package once it was in the 2012 solution, but it was certainly loadable. The manual changes were mainly around Script tasks, but this was easily fixed.

The Downgrade Tool

If you have several packages that require downgrading, performing this manually can be a time consuming process. Using the instructions on Vanie’s blog post, I created a simple downgrade tool which enables 1 or more packages to be converted automatically.

SSIS 2014 Downgrade
The downgrade process mainly revolves around replacing Component ID’s and Element Types. The downgrade tool is provided with two XML mapping files that contain the ComponentID and ElementType mappings. The mappings look to be fairly complete, but if any are missing, these files can be edited to add any missing mappings. If you do find any missing mappings, could you let me know and I will add them to the XML files in the package distribution.

The tool has been uploaded to CodePlex where the source code to the latest version can be found. If you would  rather download the an executable version without the source code, this can be found here. Full instructions on how to use the tool can be found on the Codeplex site.

If you find the downgrade tool useful or have any comments or questions about it, feel free to email me or leave a comment on this post and I will reply as soon as I can.

 

Advertisements

Catching and Storing Exceptions in SSIS Script Tasks

I use SSIS (SQL Server Integration Services) very often to write data extraction and migration packages. More recently I have been using SSIS to write an interface to update a third party system through calling web services. The SSIS package runs as a scheduled task and queries a database table (call this the interface table) which holds records that have been updated in the source system, that also need updating in the third party system. By using the supplied web services, I am ensuring that all business logic is maintained when updating the third party system, I am also ensuring that no support agreements are invalidated as I am not performing direct updates to the database.

When the package runs, I need to maintain an audit trail of which records were successfully updated and which were not. There are 3 ways in which a record will not be updated, these are:

  1. The record from the source system did not exist in the third party system (This should be a rare occurrance as the source system is updated overnight from the third party system).
  2. Whilst programmatically the call to the web service was successful, the record could not be updated as the web service returned an unsuccessful response.
  3. An exception was thrown in the SSIS package.

Within the SSIS package, I am using a Script Task to perform all the calls to the web services and process the response. The first two items in the list above are handled fairly easily by examining the response coming back from the web service call and updating the status of the record in the interface table.

But what happens if there is an exception thrown in the Script Task in the SSIS package? Well, I would find out eventually that there was an issue, when the customer tells me there is and I have to go and start digging around to find out why the package failed. If I looked in the interface table and there was a status next to the record, I could inform them why the update did not occur. If exceptions are not handled properly, there will be no status information in the interface table saying that an exception has been thrown and I then have to start looking at the job history in SQL Server to find out when and why this happened.

So, how do we handle this in our Script Task? Catching exceptions is easy enough in code and originally I wrote the following code:

try
{
    .. code that could throw an exception ..
    Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception e)
{
    Dts.Events.FireError(-1, "Task Name", e.message, String.Empty, 0);
    Dts.TaskResult = (int)ScriptResults.Failure;  
}

Within the Error Event Handler in the package, I added a simple Execute SQL Task, that used the contents of the System::ErrorDescription variable to update the status message.  Forcing the Script Task to throw an exception (by changing the URL of the web service end-point), the exception information was written back to the interface table, unfortunately all I ended up with as a status was “error in script task” – not exactly useful!! I know there was an error, but I want to know what it was!!

After some further digging and a lot of research, I found a solution to the problem. To overcome the problem, you need to create a package variable that is going to hold the exception message and you need to set this in your catch block. Normally when we work with variables in a Script Task we have to populate the ReadOnlyVariables and/or the ReadWriteVariables lists in the task configuration. For this solution to work, we do not add our package variable to the ReadWriteVariables list.

The code I have ended up with that works is as follows.

try
{
    .. program code that could throw and exception ..
    Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception e)
{
    Variables exceptionVariable = null;
    Dts.VariableDispenser.LockOneForWrite("User::ScriptException", 
                                          ref exceptionVariable);
    exceptionVariable["User::ScriptException"].Value = e.Message;
    exceptionVariable.Unlock();

    Dts.Events.FireError(-1, "Task Name", e.Message, String.Empty, 0);
    Dts.TaskResult = (int)ScriptResults.Failure;
}

Looking at the code above, we have a package variable called ScriptException (remember do not add this to the ReadWriteVariables list!), I then modified my Execute SQL Task for the Error Event Handler to work with the User::ScriptException package variable instead of the System::ErrorDescription variable. When forcing the code to throw an exception, looking at the interface table, the full exception message is now held.

Whilst I don’t want my code to throw exceptions and I always strive for my code to be exception neutral, should the code throw an exception, I can now at least start by looking at the interface table and gain a good understanding of where things went wrong.