February 5, 2015 at 2:32 pm
I have an SSIS project that works fine in Visual Studio (VS 2008). But when I deploy it to SQL Server (SQL 2014) and run it as a job through SQL Agent, it behaves oddly. My conclusion after some investigation is that the Script Tasks in the package are being ignored.
After much investigation I have come to this conclusion by including two tasks at the beginning of my package. The first is a Script Task as follows:
Public Sub Main()
Dts.Events.FireError(-1, "Crash", "and burn", String.Empty, 0)
Dts.TaskResult = ScriptResults.Failure
End Sub
The second task is an Execute SQL Task which attempts to execute a nonsense piece of text.
In Visual Studio the package fails on the first task, as expected.
When I deploy that package to SQL Server and run it from SQL Agent, the package fails on the second task. If I remove the second task the package will succeed (but not execute any of the Script Tasks in it).
Could this be a rights issue (often the case with differences between VS and SQL Agent), some config on the server (some Script Task executable not installed?) or some version problem between my VS 2008 and SQL 2014?
February 5, 2015 at 2:40 pm
Out of curiosity, when you run a report on the execution from the catalog, what (if anything) does it say about the script task?
February 5, 2015 at 2:48 pm
Do you mean what does the job history say in SQL Agent? It says absolutely nothing about the Script task. It reports only on the second SQL task (see what's pasted below, the SQL task is called FAILSQL and what I'm executing is a variable - it was the quickest way to dump garbage into the Execute SQL Task).
FWIW I have also turned on logging to a text file. The Script Task is not mentioned there either.
Executed as user: HUBERT\polly. Microsoft (R) SQL Server Execute Package Utility Version 12.0.2000.8 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 21:15:11 Error: 2015-02-05 21:15:12.56 Code: 0xC002F210 Source: FAILSQL Execute SQL Task Description: Executing the query "E:\FACE\Archive\" failed with the following error: "Incorrect syntax near '\'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 21:15:11 Finished: 21:15:12 Elapsed: 0.875 seconds. The package execution failed. The step failed.
February 5, 2015 at 2:54 pm
I may have misread your original post, are you using package deployment or project deployment model?
If it is project deployment model, your package should be within an SSIS catalog, and it has its own built in logging that I find way easier to diagnose issues that anything from SQL agent.
February 5, 2015 at 3:00 pm
You're a few steps ahead of me. And I probably have misled you through not being clear on how I've deployed - apologies.
What I've done is to import the package (.dtsx) into the SSIS Package Store on MSDB. Then I've created a job in SQL Agent that runs that package.
I'm not sure if that is what you're describing or not. Nor am I clear on how to get logs outside of the job history logs.
.....
UPDATE: Should have done this first and googled it. Now I know the difference and no, it's not project deployment, it's package deployment. I'm using VS 2008 and I'm thinking that that limits me (or does it?). I'm also using SSMS 2014 FWIW.
February 10, 2015 at 1:02 pm
Hi, We had that exact problem. After upgrading the package in VS2012 and re-deploying it, the VB bits of the package ran fine on a SQL 2014 server. I did have to run patch_KB2781514 on VS2012 to be able to view the VB code but that may depend on your installed .NET version.
Regards
Mark
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply