SSIS individual package was one of the most asked for functionalities in SSIS and many developers, data professionals were very glad to see it introduced in SQL Server 2016. However ever since individual package deployment was introduced I must admit that I have had certain reservations toward using it. Many have felt it as a unnecessary restriction that you needed to deploy an entire project – even though there were only changes to 1 or 2 packages in the project. But I have actually seen that as a strength because somehow it underlined that you should always aim for having your ssis project test or production ready after each change. If you need to run concurrent development processes, you should do the following:
- use a project structure that minimizes the need for concurrent development processes in a single project. Usually that results in many more and simpler projects
- Use Git and branching
Aside from that, I have actually recently used individual package deployment at least in dev/test scenarios and also been mostly happy for the functionality. But that all came to an abrupt end today, because individual package deployment is broken, dangerous and useless until a critical error is fixed by MS. Actually I first saw this error about a month ago, where I had a problem with Execute Package Task, if the package name contained certain characters. But more on that below.
What is wrong?
The short explanation is that national characters are not handled correctly. Look at the following example. I have a package like below, with a simple Execute SQL Task in it:
The SQL is:
INSERT INTO dbo.Staging1(tekst) values(N'æøå')
If I deploy the entire project to SSIS Catalog and execute the package from the catalog, I get the following contents in the table:
BUT, if I deploy the single package and execute I get the following contents of the table:
You might ask – why is this significant? For one reason: I can now not trust the SQL which is executed if it contains national characters. It corrupts my data, changes execution logic and so on… But it doesn’t stop here. It turns out that everything is affected by this.
- If I use national characters in task names – they get corrupted on deployment
- If I use national characters in table names (source and destinations) – they get corrupted on deployment
- If I use national characters in execute package tasks – they get corrupted on deployment
- And on and on
So it seems that all content in the xml-structure in *.dtsx is affected by this error in single package deployment. If I were to speculate, then it seems that the deployment wizard forgets to perform a proper encoding of the individual dtsx-file on deploy, while the entire project deployment perhaps just pushes the entire ispac-file where the zip -format retains the necessary encoding of the national characters.
P.S. There is a connect issue on this error. Please vote for it:
