SSIS Execute SQL Error: “ ”

0
votes

I am getting an error when trying to Execute an SQL file with an Execute SQL Task in SSIS (2012).

Execute SQL Task: Executing the query "" failed with the following error: "".

I am trying to automate a process to import a file from TestRail that drops and recreates tables and then fills them with data.

I can open the file in SQL Server Management Studio. I do get an Inconsistent Line Endings warning when I open the file but it works when I click Yes or No to Normalizing them). It actually worked the first time I created the process but the file was much smaller as we didn't have much data at the time.

Any ideas as to why a query might fail in SSIS but run in SSMS? I'm thinking it might be some funky syntax that SSMS figures out but SSIS can't.

Here's the whole error message:

SSIS package "\valinor.k-net.com\Users\bs\documents\Visual Studio 2012\TFS\Data Analysts\SSIS PACKAGES\SSIS PACKAGES\TestRailRefresh.dtsx" starting. Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "" failed with the following error: "". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Execute SQL Task Warning: 0x80019002 at TestRailRefresh: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "\valinor.k-net.com\Users\bs\documents\Visual Studio 2012\TFS\Data Analysts\SSIS PACKAGES\SSIS PACKAGES\TestRailRefresh.dtsx" finished: Failure. The program '[146976] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).

2
sqlsql-serverssissql-server-2012ssis-2012
Can you post the entire error stack? – Mathew A.
@matt-a, I added the entire error message - though the rest of the message seems pretty generic. – Hannover Fist
Have you tried rebuilding and/or cleaning the solution? Sometimes that error is a meta data issue. – Mathew A.
@matt-a - I tried recreating from scratch but no dice. I broke it up into pieces and found the part that it errors on has some Java. SSIS does add some escape characters to it but it runs in SSMS. – Hannover Fist

2 Answers

0
votes

Users have reported that the maximum size of the submitted task is 32K. I suspect the string you are submitting is longer then that. Perhaps your inserts can be broken up into multiple Execute tasks.

0
votes

After wasting many hours on this, it turns out this is expected stupid behavior by SSIS. Apparently, Microsoft thinks that using a $( is only for internal variables and would NEVER be used by someone in an actual query.

This would run is SSMS but fail in SSIS:

SELECT 'Total: $(1200).'

I fixed the issue by using REPLACE to add a space between the characters - $ (.

https://msdn.microsoft.com/en-us/library/ms188714.aspx

Screw you, Javascript.