2

I have a package variable SomeId of type string(as there isn't an option in SSIS package variable pane for setting it to a GUID).

In the 'Derived Column' step I have set the variable's Expression as (DT_GUID)@[User::SomeId] and DataType auto sets to unique identifier [DT_GUID]

Next step is the 'OLE Db Destination'

I have 'enabled Data Viewer' between the above two steps and I can see the SomeId column with a new guid populated. However as soon as it tries to import it to a database table it fails with below error.

[NameOfOleDbDest1[181]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Invalid character value for cast specification".

I'm thinking it has something to do with how I'm casting the variable to DT_GUID, however the other ways* mentioned by people on stackoverlfow raises error on the expression with red highlight.

*Here are other ways I've tried to cast string to guid but error is raised:

(DT_GUID)[User::SomeId]
(DT_GUID)("{" + [User::SomeId] + "}")

only when I try (DT_GUID)@[User::SomeId] does it succeed. But obviously the above error is raised when I execute the package.

User123
  • 549
  • 1
  • 11
  • 24

1 Answers1

0

This is the same solution as the referenced answer, but hopefully adding it here clarifies it a bit.

I resolved this issue by wrapping the variable in curly braces using the following expression.

"{" + @[User::string_id] + "}"

The variable type in the derived column remained DT_WSTR but the value was converted correctly when inserted into the destination table.

This also helps to resolve the issue of using a string value GUID as a parameter on an OLEDB SOURCE. For that, I create a 2nd variable and use the expression builder to reference the 1st variable and wrap it in curly braces using the same expression. I then use the 2nd variable as my input parameter in the source query.

FisherKing
  • 1