kohera-logo-regular.svg

The dangers of copying script components in SSIS

In this blog post I want to explain something weird that happened in a previous SQL Server Integration Services (SSIS) project. A lot of different SSIS packages had to be developed. Often very similar code was used within the same package. So we just copied the Data Flows and changed the things that needed to be changed. After checking if everything worked we looked at the output of the data we found out that there were problems. Let me illustrate with an example.

Example:

We have the following simple dataflow called ‘DF | A’ with

1. Take some data (10 row)
2. Add a derived column ‘Result’
3. This column will be filled with the letter ‘A’ within the script component ‘SC | Fill Result Column with A’
4. And finally we will output it to a Derived Column
5. A Data Viewer shows the results

141209b

141209c

When we run this, we get the following result as expected

141209d

Next we add another Data flow to the package named ‘DF | B’. This on does the same thing as the dataflow describe above. The only difference is that the ‘Result’ column will be filled with the letter ‘B’. So we just copy the Data Flow and change the code of the script component

141209e

141209f

Now if we run the query and look at the Data Viewers of both packages we get the following results.

141209g

This is strange, we see that for the result of Data Flow ‘DF | B’ we get the value ‘A’. But we set it explicitly to the value ‘B’. Let’s find out how this is possible.

The Problem

I searched the Internet but I couldn’t find any causes for this problem. Disappointed and fed up with it, I decided to create the second script component from scratch which solved the issue. But we still didn’t know what the real problem was. After searching and trying every option in the SSDT toolkit, I finally turned to the XML of the package. I investigated the XML section where the Script Component of Data Flow ‘DF | B’ is defined. There I found the following piece of code.

<property dataType=“System.String” description=“Specifies the name of the Microsoft Visual Studio Tools for Applications project. Project names must be unique within a package.” name=“VSTAProjectName” typeConverter=“NOTBROWSABLE”>
SC_0b5003f709304e36874a827f76309177
</property>

In this section the name of the assembly is defined. The text in attribute description I found very interesting ‘Project names must be unique within a package’. This made me think what if the Script Component in Data Flow ‘DF | A’ had the same name ‘SC_0b5003f709304e36874a827f76309177’Yep, I found out that it did. SSIS copied everything even the name of the script component solution.
At runtime SSIS will build these 2 assemblies. When the assembly of the Script Component in Data Flow ‘DF | B’ is built, it is overwritten with the assembly of the script component in Data Flow ‘DF | A’. That is why we see in both result set the value ‘A’ in the ‘Result’ Column.

Solutions

Now how can we fix this problem? We could change the assembly name but then we must change it everywhere in the solution of that script component. Secondly we could rewrite the script component from scratch, but who wants to do that if you code is several pages long. After some searching I found a real easy solution to the problem:

1. Open a second package.
2. Copy/Paste one of the 2 Script Components to this new package
3. Copy/Paste it back to the original location in you first package.
4. Replace the old Script Component with the new on.
5. Open the Script Component Editor
6. Click on ‘Edit Script’
7. Close it
8. click on ‘OK’

This way SSIS will have changed the assembly names for you. This time when you run the package again you get the results you want.

141209h

Thanks for reading my post. I hope it will be the solution for some of you experiencing this problem. I have a few other pitfalls in SSIS that I want to discus in future post.

Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature

If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before. Up until now, you had...

Creating maps with R and Power BI

The possibilities are infinite when it comes to creating custom visuals in Power BI. As long as you have creativity and knowledge about the right programming language, you can let...

Sending monitoring alerts through Telegram

What if you could get the ease of phone notifications for whatever monitoring alerts you need? Then we have a solution for you with the app Telegram. Some of you...

Send mails with Azure Elastic Database Jobs

The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want a similar functionality in Azure SQL Database? There are options,...

Sorting matrices in Power BI

Recently I worked on a Power BI project for a client. They had a SharePoint site where they regularly published articles and wanted to pour view data into a report...

The world of data is evolving

The data landscape has changed dramatically over recent years. In the past, we mainly heard that we needed to do as much as possible “cloud-only”—but this trend has become more...