Post reply

Issue with SSIS Execute SQL Task

  • October 18, 2012 at 9:18 am

    #268766

    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.

  • October 18, 2012 at 10:55 am

    #1550613

    when you go in to the execute sql task editor, On the left on the Result set are you assigning the xml to a variable?

    ResultName should = 0

    and Variable name can be any variable with a Data type of Object.

  • October 18, 2012 at 11:03 am

    #1550615

    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?

  • October 18, 2012 at 11:11 am

    #1550618

    Other than trying to explicity setting the lenght of the varchar.

    convert(varchar(10), getdate(), 101) I'm guessing the proc is returning no data, when you leave this in there.

    in the proc check if a date exists first, if so run the query, if not then return an xml string with nothing in it.

    <Export/>

  • October 18, 2012 at 11:14 am

    #1550619

    This makes sense...since there is no data present for today's date......

    Would this be done in the stored procedure or in the control flow? What is the best way to do this when no data is present?

    Thanks again

  • October 18, 2012 at 11:31 am

    #1550626

    I'm not totally sure that this is the issue

    However at the end of your proc I would modify it to this.

    IF @XmlOutput IS NULL

    SET @XmlOutput = '<Export/>'

    SELECT @XmlOutput

    I'm assuming your exporting this xml to a file.

    I would just let this empty xml go to the file that way you can validate the process ran, and an empty file was generated.

    and you won't be wondering why nothing generated.

  • October 18, 2012 at 11:35 am

    #1550627

    THAT DID IT!!!

    Thank you so much for the help. It has been over a day of looking around online for why this was erroring out and I had a funny feeling it had something to do with the where clause, but not sure what!

    Thanks again for your help. It is much appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply