2

I'm trying to execute an SSIS package I created and deployed to SQL Server 2016, but I get the following errors when I attempt to execute it (I don't get this locally when running it via VS2015). I initially created this package using VS2017. However judging by the error message, I needed to use an older version? So I used VS2015 to open the solution and recreate the Script Component that's the source of the problem. I did this successfully, tested, rebuilt, but I'm STILL getting the same set of error messages on our PROD server after removing the old package from there, and deploying the "new" one from VS2015. I've run out of ideas. Could someone please tell me what I need to do to get the failing Script Component to execute without these errors?

Additional Info: I initially was testing these packages (via running them in Visual Studio) against our DEV database which is running SQL Server 2008, and am attempting to deploy against SQL Server 2016.

enter image description here

KSwift87
  • 1,843
  • 5
  • 23
  • 40
  • In 2016, you can set the `Target Server Version`. Did you try that ? https://www.mssqltips.com/tipimages2/4253_TargetVersion.jpg – Prabhat G Oct 30 '17 at 07:31
  • What kind of component is Fetch Truck Role Records? It says something about custom code... is it a C# script component? And did you try to connect VS to your prod server and try to perform a build with this connection? – Tyron78 Oct 30 '17 at 13:39
  • @Prabhat G -- Yes I did. That unfortunately did not help. – KSwift87 Oct 30 '17 at 15:14
  • @Tyron78 -- It's a C# Script Component located within a data flow. As for connecting to the PROD Server via VS and building against it -- I'm not sure how to do that. SSIS is not my forte by any means. – KSwift87 Oct 30 '17 at 15:15
  • Oh, then you might want to check the target framework configured for the c# component - I don't know about VS 2017, but I experienced problems in the past when the component targeted the wrong framework version. – Tyron78 Oct 30 '17 at 15:53
  • @Tyron78 -- I heard of that as a possible problem, and made sure to build the component utilizing .NET Framework 4.6 since that's what gets installed during SQL Server 2016 installation. Unfortunately that has not resolved the issue. – KSwift87 Oct 30 '17 at 16:00
  • Hm... And which Version of data tools is installed? – Tyron78 Oct 30 '17 at 16:32
  • @Tyron78 -- SQL Server Data Tools 14.0.61709.290. – KSwift87 Oct 30 '17 at 16:53
  • Hm... Should be The correct version for vs 2015... But Sounds as if the server side requires another version – Tyron78 Oct 30 '17 at 18:50
  • @Tyron78 -- I think I'm making progress because I'm getting new error messages (admittedly I don't know the cause of this; says one of my tables doesn't exist but that's clearly BS). Anywho I got the idea from the checked answer in this link: https://stackoverflow.com/questions/44191970/ssis-script-task-fail-version-15-0-script?rq=1 – KSwift87 Oct 30 '17 at 19:03
  • Great you are making progress. Concerning the table which - according to the error message - does not exist: Is it possible that the user you use for the job execution has no privileges on this table? Or maybe you used full qualified table description (like `DB_T.dbo.mytable`) and the prod server does not know the DB name? – Tyron78 Nov 02 '17 at 07:24
  • @Tyron78 -- Yup. The "table not existing" was due to SSDT not generating the "Database" property of the connection string when I parameterized my connection managers. >_ – KSwift87 Nov 03 '17 at 23:14

2 Answers2

2

Turns out it's best to deploy SSIS packages via Visual Studio 20XX as that does the proper conversions that are needed for the targeted SSIS SQL Server. The first answer at the link below is the fix I used which worked like a charm.

SSIS script task fail version 15.0 script?

KSwift87
  • 1,843
  • 5
  • 23
  • 40
1

Change Target server version of your SSIS project to the same version of your SQL server.

Check here

Josf
  • 776
  • 1
  • 8
  • 21