6

Alright, so here's what I have going on. SSIS Package that queries data from our production system and drops them into CSVs. Have a VB script task that uploads them to a third party. Needed a multi-post HTTP post with authentication and a couple specific fields sent in the header for the 3rd party so had to do a custom VB script instead of using the web destinations.

I have a ForEach loop that goes through, uploads each file via the script and then deletes it. Works great in BIDS. But when I upload it to the SSIS Package store the upload portion doesn't work. But doesn't throw an error anything since the files are still deleted.

Control flow

The Query portion works, spits out the CSVs. The Script Task does nothing but the files are deleted.

Threw on all sorts of SSIS logging into the package and the script. Threw a "Hello World" log into the first line of the script and it's never fired.

What I've done:

Created Proxies with full access, linked it to new Creds and SQL logins. Nothing. Made the SQL login have rights to everything, nothing. Run the package as the new Proxy execution account and as the service account. Even changed the account the SQL AGENT service runs on (New SQL server, allowed to mess it up right now). Ran the package as 32-bit nothing. Thought it was tied to the ScriptTask reference being set as "Copy Local = True," but changed that and nothing.

Works A-OK in BIDS AND I've used this same script at another client's environment and it worked fine there. I'm not sure what else to try. Any ideas? I can upload code portions if need be.

Credentials

SQL Help history

  • One thing you could take a look at is disabling the existing Script Task and then adding a new one that just logs a warning event to show it fired (assumes your SQL Agent invocation has /rep ew attached to it) Code approximately Dts.Event.FireWarning(0, "Test", "Yes, this worked", string.Empty, 0, ref fireAgain); If that works then it becomes a matter of slowing adding the upload code back into it until you find that which breaks it or you've gotten all code in there. – billinkc Dec 9 '15 at 3:25
  • Thanks for the edits! Was going to grab those pictures and add them later. Hoping to get access to the client today so I can try a new script. Thanks for the help + help on twitter! – bvankampen Dec 9 '15 at 17:21
2

That occurred because VS 2015 was built to support only SQL server 2016 and you probably deployed the package under SQL Server 2014 or lower(unsupported versions).

With the latest version of Data tools in VS 2015, you can chose the targeted SQL server version in the package to allow SSDT to chose the correct .NET libraries used in the script component.

See this link for details

0

My SSIS package was originally created in VS2013 and I migrated it to VS2015 for this project. It ran fine in Visual Studio but was failing to call the upload script when running as a SQL AGENT JOB and since I was in a new environment I thought something else was up.

Installed VS2013 + the Data Tools BI edition for 2013, opened the original package + uploaded it to the SSIS package store and works fine now, even as a SQL AGENT JOB. Still have zero ideas why it was skipping the script task when it migrated to 2015 but it works now so I'm OK never finding out why.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.