Issue with SSIS Execute SQL Task

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Issue with SSIS Execute SQL Task

Author

Topic

nailers67
Starting Member

4 Posts

Posted - 2012-10-18 : 08:02:05

I have a stored procedure without parameters assigned to an Execute SQL Task. This task is connected to an ADO.NET connection and is the parent of a Script Task that writes the results of the SQL Task to an XML file.

I am running into a problem when attempting to execute this SQL Task and receiving the following error:

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec spExportResultsToXML" failed with the following error: "Exception has been thrown by the target of an invocation.". 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.

Here is a sample of the stored procedure (note that the dates are stored as seconds from 1980-01-01 in the database...this is the reason for the convert function):

CREATE PROCEDURE spExportResultsToXML

AS

DECLARE @XmlOutput xml

SET @XmlOutput =

(SELECT
lblInsertDate = convert(varchar, dateadd(second, prod_data.tmInsertDate, '1980-01-01'), 101),
lblProductionDate = convert(varchar, dateadd(second, prod_data.tmProdDate, '1980-01-01'), 101),
lblProductNumber = prod_data.nProductNumber,
lblMachineNumber = prod_data.nMachineNumber,

FROM prod_data

WHERE convert(varchar, dateadd(second, prod_data.tmProdDate, '1980-01-01'), 101) = convert(varchar, getdate(), 101)

FOR XML RAW('ProdSched'),Root('Export'),ELEMENTS)

SELECT @XmlOutput

GO

I have a feeling that this is failing because of the GETDATE function in the WHERE clause and the way the SSIS is handling it, but I cannot be certain.

The stored procedure is executing correctly in SSMS, so I know this is correct.

Could there be a property configured incorrectly?

Execute SQL Task settings:
General - Name = Execute SQL Task
General - Description = Exectute SQL Task
Options - TimeOut = 0
Options - CodePage = 1252
ResultSet = XML
SQL Statement - ConnectionType = ADO.NET
SQL Statement - Connection = SQLDB
SQL Statement - SQLSourceType = Direct Input
SQL Statement - SQLStatement = spExportResultsToXML
SQL Statement - IsQueryStoredProcedure = True
SQL Statement - BypassPrepare = True

There is nothing configured under Parameter Mapping.

Could anyone help? Not sure what else to look at here.

Thanks in Advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-18 : 13:07:02

Cab you show how you're capturing XML value in resultset tab of execute sql task?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nailers67
Starting Member

4 Posts

Posted - 2012-10-18 : 13:26:17

Yes...Here is what is under the Result Set

Result Name = 0
Variable Name = User::XMLVariable

When I look at the variables:

Name = XMLVariable
Scope = Package
Data Type = String

I did a test with the stored procedure.....I changed the stored procedure to return results in the where clause to a specific date of 07/24/2008 instead of the convert(varchar, getdate(), 101) and the package worked. No idea why the where clause would be breaking this unless that GETDATE and conversion is causing some sort of problem.

Thanks for the reply....any other thoughts?

nailers67
Starting Member

4 Posts

Posted - 2012-10-18 : 13:36:54

Another poster on a different site figured it out....it was a result of the SP returning 0 results and all that was needed was to change the SP

IF @XmlOutput IS NULL
SET @XmlOutput = '<Export/>'
SELECT @XmlOutput

So it still writes even if nothing is returned from the query.

Thanks for your help and just wanted to post the solution.

Cheers!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-18 : 14:08:36

quote:
Originally posted by nailers67

Another poster on a different site figured it out....it was a result of the SP returning 0 results and all that was needed was to change the SP

IF @XmlOutput IS NULL
SET @XmlOutput = '<Export/>'
SELECT @XmlOutput

So it still writes even if nothing is returned from the query.

Thanks for your help and just wanted to post the solution.

Cheers!

Oh ok...Makes sense
Thanks for sharing the solution

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources