Character encoding issue when deploying SSIS package

Question

I’m using VS2015 with SSDT for developing Integration Services projects for Sql Server 2016. An example of the issue I’m facing when deploying the SSIS package to the sever:

I created a table in a test database (Collation: SQL_Latin1_General_CP1_CI_AS) as:

CREATE TABLE [dbo].[EncodingError](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Text] [nvarchar](20) NULL,
[TimeStamp] [datetime2](7) NULL) ON [PRIMARY]

Then I created a new SSIS project (project deployment model) and added into the default package a Connection Manager for the test db and an “Execute SQL Task” with the script:

INSERT INTO [dbo].[EncodingError](Text,TimeStamp) SELECT '¤' AS Text,GETDATE() AS TimeStamp

When running the package from VS or deploying the whole project to the server (SSIS catalog) and running the package there, it insert the rows correctly into the table:

ID  Text    TimeStamp
1   ¤       2017-09-04 12:13:54.3100000
2   ¤       2017-09-04 12:14:26.2266667

When I redeploy - now only the package, not the whole project - to the server and I run it again it inserts the row like this:

ID  Text    TimeStamp
3   ¤      2017-09-04 12:16:16.8866667

Now when I again redeploy the whole project it’s working correctly.

After I deployed only the package, I created a new SSIS project with the wizard to import the project back from the server. In this case the script in the task is shown as:

INSERT INTO [dbo].[EncodingError](Text,TimeStamp)
SELECT '¤' AS Text
      ,GETDATE() AS TimeStamp

Any idea what is the reason for this behavior? What is the difference if I deploy the whole project vs. only the package from the character encoding point of view?

Answer 1

Good afternoon

I went through a similar problem and solved it by applying Cumulative Update 6 for SQL Server 2016 SP1

https://support.microsoft.com/en-us/help/4037354