Search code examples
sqlsql-serverssissql-server-2012ssis-2012

SSIS Execute SQL Error: " "

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).

Solution

  • 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.