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

 

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

 

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

 

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

 

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.

 

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.

2319-blog-database-specific-security-featured-image
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
kohera-2312-blog-sql-server-level-security-featured-image
SQL Server security on server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
blog-security_1
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...
DSC_1388
Aggregaties
Power BI Desktop is een prachtige tool om snel data-analyses te kunnen uitvoeren. Je connecteert op een databron, importeert en...
dba image
DBA is not that scary
Often when talking to people who are looking for a career path in the data world I feel like there...
blog-2303
How do you link an SCD Type 2 table in Power Query?
This article uses a simple example to demonstrate how to link an SCD Type 2 table in Power Query to...