none
Ole DB Command calling a store procedure with a GUID parameter (Error Converting String to Uniqueidentifier)

    Question

  • Hi,

    I hope someone can help me on this, Ive been struggling for days and read a lot of forums.

    I created a String Variable to use in a for each loop component and Derived Column to transform it first into (DT_STR,36,1252)@[User::ID_Provider] and then a second Derived Column to transform it to (DT_GUID)provider_id  with the value in the format I want I send it to an OLE DB Command to call to a store procedure. 

    The store procedure receives the parameter as a varchar(36) (on the oledb command the @ external columns are declared as DT_STR.  On the Input Column the column is declared as DT_GUID (last derived column).   Inside the store procedure the varchar 36 parameter inserts into a table that has a uniqueidentifier column. 

    When I run my package this is the error I get.  Ive tried a lot of thing but nothing seems to work.  Do you have an idea of what can I do to success on this task?

    "Conversion failed when converting from a character string to uniqueidentifier.".

    Thank you in advance !!!

    Regards

    Adriana

    Tuesday, July 09, 2013 11:07 PM

Answers

  • Hi,

    Thank you for your kind answers.  

    Here is what I found:

    First, I stopped using variables and receiving the value as it came from the table.  I had to make sure that the data type was DT_STR with length 50 , that is very important since a smaller number won´t work. 

    I kept working and had the problem one more time but didn´t work with the first solution.  So I used data viewers to see what was going on (couldn't figure it out in with an ordinary query), so I saw that the guid column came with {} and I removed it and that's how I solved the second issue with GUID column.

    Rajkumar and Tim thank you very much.

    Regards,

    Adriana

    Tuesday, July 16, 2013 2:49 PM

All replies

  • Hi Adriana,

    I think the string value which you trying to covert to GUID in the second derived column is failing, because the string value may not be a proper "unique identifier".


    Rajkumar

    Wednesday, July 10, 2013 6:03 AM
  • Try passing the parameter to the SP with curly braces.

    ie, use '{93F91B51-0549-4900-8D50-A4A7B361607E}' instead of just '93F91B51-0549-4900-8D50-A4A7B361607E'

    good luck

    Tim Mills-Groninger

    Wednesday, July 10, 2013 2:54 PM
  • Hi,

    Thank you for your kind answers.  

    Here is what I found:

    First, I stopped using variables and receiving the value as it came from the table.  I had to make sure that the data type was DT_STR with length 50 , that is very important since a smaller number won´t work. 

    I kept working and had the problem one more time but didn´t work with the first solution.  So I used data viewers to see what was going on (couldn't figure it out in with an ordinary query), so I saw that the guid column came with {} and I removed it and that's how I solved the second issue with GUID column.

    Rajkumar and Tim thank you very much.

    Regards,

    Adriana

    Tuesday, July 16, 2013 2:49 PM