SSIS Script Task Error: “Found SQL Server Integration Services 2012 Script Task that requires migration”

2
votes

As per the Microsoft documentation on DTEXEC for executing SSIS packages:

When you use the version of the dtexec utility that comes with SQL Server 2012 Integration Services (SSIS) to run a SQL Server 2005 Integration Services (SSIS) or a SQL Server 2008 Integration Services (SSIS) package, Integration Services temporarily upgrades the package to SQL Server 2012 Integration Services (SSIS).

Is there a feature to disable this aspect of DTEXEC utility when running SSIS packages?

My reason for this question is that I have a script task to rename some files in an SSIS package. This works just fine on my local machine and a coworkers local machine, but after deploying this SSIS package to our windows 2012 server with SQL Server 2012 installed I get an error message. I'm really confused, because I wrote this package in SQL Server 2012 Data Tools so this task shouldn't need to be upgraded/migrated at all, which is what the error is complaining about...

Warning: 2016-04-06 11:29:58.14
Code: 0x00000000
Source: DataMergeScriptTask
Description: Found SQL Server Integration Services 2012 Script Task "my_Script_task" that requires migration!

End Warning

Error: 2016-04-06 11:30:03.02
Code: 0x00000001
Source: DataMergeScriptTask
Description: Exception has been thrown by the target of an invocation.

End Error

2
ssisssis-2012
AFAIK this behaviour is built-in and can't be changed. Which is why people go through the pain of upgrading their 2008 packages to 2012. I'm guessing your error is caused by differing versions of the "cut-down C# or VB.net" that is used in the Script task. Have you tried to upgrade the package? The problem with the task could be as simple as a slightly difference namespace reference being needed. – SebTHU
Funny thing is, this package has been successfully converted using SQL Server 2012 Data Tools... I even re-wrote the whole thing in a new package using sql server 2012 data tools and had the same result. Seems like sql server data tools 2012 script tasks are broken. – Dinglemeyer NeverGonnaGiveUUp
Seems like we'll be able to take our C# script and convert it into a standalone console app/exe and put it on the server that way. I hope I can identify a solution for this issue in the contexts of SSIS though... Very disappointing. – Dinglemeyer NeverGonnaGiveUUp
It may be the only solution for you right now, but generally I'd recommend against SSIS calling external EXEs - from painful experience of maintaining an environment in which someone did this extensively. It makes deployment much more complicated. If you add configuration in, it gets even worse. And the feedback from the EXE to SSIS is difficult and limited: for example, if the EXE hangs, so will the package. Have you tried setting a breakpoint and debugging, since it's a task (debuggable) rather than a component (not debuggable)? – SebTHU
Oh, I won't be calling the external EXE via SSIS; just had to do this with a single .exe to get it working on the server. Our method of execution is via batch job called by a windows task scheduler. Seems like this is going to allow us to get it running! – Dinglemeyer NeverGonnaGiveUUp

2 Answers

2
votes

Unfortunately we haven't found a solution for the Script Task not converting into 2012, as these packages are all written in Data Tools 2012 and opening/resaving the task doesn't seem to actually upgrade the script task in a way that solves it on the server.

My solution has been to take the C# script and turn it into a standalone .exe which has some command line arguments to pass our different parameters with defaults in place to match our current environment. Then I set this up to be executed by a Batch script called by a windows task scheduler job.

I'm hoping to find a real fix for this so that script tasks in larger packages will still be peachy, but that may take more time or a reinstall of data tools... Having multiple versions of SQL Server data tools/BIDS on my workstation could be a cause of this grief. More testing to come!

-1
votes

Just update the TargetServerVersion in your package to match the server version that you plan to deploy to.

Make sure you have a copy of your project in case it doesn't go smoothly. Then right click on the project name, choose properties, then drill into Configuration Properties -> General and update the TargetServerVersion. enter image description here Then rebuild and deploy. When the project builds, it should now be in the format that your server expects so it won't try to rebuild the script code on the server where references might not be the same.