Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
help needed for each loop

Answered help needed for each loop

  • Monday, January 07, 2013 1:09 AM
     
     
    Sign In to Vote
    0

    I have two tables, one a master and a second a child.

    They are linked by a uniqueidentifier. field name LedgerKey

    I am using an executeSQL task to select all the unidentifiers from the master table into a variable object called Transets

    The foreach loop I have set the Enumerator as FOREACH FROM Variable Enumerator and the variable TRANSET

    The variable mapping is set to user::Ledgerkey Index 0  the variable is set as an object.

    Inside the foereach loop I have an execute SQL Task that

    INSERT INTO Client_Arc.dbo.Transaction SELECT * FROM MT1.dbo.Transaction WHERE LedgerKey =  cAST(@LedgerKey as uniqueidentifier)

    Under TSQL this query works but in SSIS it fails

    [Execute SQL Task] Error: Executing the query "INSERT INTO Client_Arc.dbo.Transaction SELE..." failed with the following error: "Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    any clues ?

    Dont ask me .. i dont know

     

All Replies

  • Monday, January 07, 2013 2:37 AM
     
     Answered
    Sign In to Vote
    0

    Hi ,

            check the connection properties !!.

            refer this link for similar example - http://www.bidn.com/blogs/mikedavis/ssis/150/ssis-for-each-ado-enumerator-loop

    Thanks & Regards, sathya

     
  • Monday, January 07, 2013 5:21 AM
     
     
    Sign In to Vote
    0

    Hello,

    Please replace SQL Query in your Execute SQL Task

    DECLARE @uuid VARCHAR(50)
    SET @uuid = @LedgerKey

    INSERT INTO Client_Arc.dbo.Transaction
    SELECT * FROM MT1.dbo.Transaction WHERE LedgerKey =  CAST(
            SUBSTRING(@uuid, 1, 8) + '-' + SUBSTRING(@uuid, 9, 4) + '-' + SUBSTRING(@uuid, 13, 4) + '-' +
            SUBSTRING(@uuid, 17, 4) + '-' + SUBSTRING(@uuid, 21, 12)
            AS UNIQUEIDENTIFIER)

     

    Please let me know if it works.

    Thanks,

    Naveen T

    Naveen Kumar

     
  • Monday, January 07, 2013 5:40 AM
     
     
    Sign In to Vote
    0

    please check if there is any differnce in data types

     In exeute sql task ,have sql source type from variable, create your query in variable expression

    it would be "INSERT INTO Client_Arc.dbo.Transaction SELECT * FROM MT1.dbo.Transaction WHERE LedgerKey = " + @[user::LedgerKey]

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

     
  • Monday, January 07, 2013 7:13 AM
     
     
    Sign In to Vote
    0

    Could you verify the format of GUID being passes to the Execute SQL task, does it have any additional brackets {} appended to it?

    Please check the below link -

    http://www.sqllion.com/2011/10/handling-or-importing-guids-in-ssis/