I'm facing an issue with an SSIS package I developed using Visual Studio 2019 Community Edition. On my local environment, everything runs smoothly. However, when I deploy it on our ETL server running Windows Server 2016 using an automated process provided by Microsoft through a pipeline, I encounter the following error: "Cannot convert between Unicode and non-Unicode string data types."
Additional details about the configuration and versions:
Microsoft SQL Server Management Studio: 13.0.16000.28 Microsoft Analysis Services Client Tools: 13.0.1700.441 Microsoft Data Access Components (MDAC): 10.0.14393.6078 Microsoft MSXML: 3.0 6.0 Microsoft Internet Explorer: 9.11.14393.0 Microsoft .NET Framework: 4.0.30319.42000 Operating System: 6.3.14393 Microsoft Windows Server 2016 Standard Version: 10.0.14393 Build 14393 ETL Development: Visual Studio 2019 Community Edition with targetServerVersion set to SQL Server 2016.
From my research, I've found that this error can typically arise when there's a mismatch between data types of sources and destinations in SSIS1. There are suggested solutions, such as using a Data Conversion Transformation or a Derived Column Transformation2.
Despite these proposed solutions, I find it puzzling that I only encounter this error on the server and not on my local machine. Has anyone faced a similar issue? Could it be a specific server setting or a different SSIS version causing this problem? Or could it be related to the automated deployment process via the pipeline? I appreciate any insights or guidance.