Search code examples
sql-serverssismigrationdynamics-crmssms

Compare column1 (Optionset) to set value in column2 (Lookup) in SSIS

I have a problem, I want to set a Lookup in account from the Value that is in the Optionset:

(DT_GUID)(industrycode == 100000009 ? (DT_STR,50,1252)"280f1c20-ad3b-eb11-c345-000d3a23cdb4" 
: (industrycode == 100000003 ? (DT_STR,50,1252)"23cc76d9-d345-eb11-a813-000d3a23cdb4" 
: (DT_STR,50,1252)"00000000-0000-0000-0000-000000000000"))

[Error Message]1

[Derived Column Transformation Editor]2

As you can see, I want to write in the Column "cluster", but I'm comparing if "IndustryCode" has the optionset Value. And now it shows me this error message. I also tried to Cast it in (DT_WSTR), (DT_STR) and (DT_STR,50,1252)

Solution

  • I learned two new SSIS things today. I created a reproduction and was getting the same error

    Error: 0xC0049064 at Data Flow Task, Derived Column [2]: An error occurred while attempting to perform a type cast.

    Error: 0xC0209029 at Data Flow Task, Derived Column [2]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[GuidCastError]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

    Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (2) failed with error code 0xC0209029 while processing input "Derived Column Input" (3). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

    Of lesser importance, I cannot create a Variable of type DT_GUID even though it's a valid data type.

    But I know they use GUIDs for things like package id and execution id so I opened the Variables window and checked "show variables of system scope" as I know they have GUIDs in there. What I observed was that ExecutionInstanceGUID was of type String and the value they have listed is {0908C691-9E2A-4D42-8111-A7D98DE17BD9}. Those braces ended up being the missing an unexpected key to solving the issue.

    A rewrite, and simplification, of your expression

    (DT_GUID)(industrycode == 100000009 ? "{280f1c20-ad3b-eb11-c345-000d3a23cdb4}" : (industrycode == 100000003) ? "{23cc76d9-d345-eb11-a813-000d3a23cdb4}" : "{00000000-0000-0000-0000-000000000000}")

    I used a query as my source to test things

    select newid() AS cluster
, D.*
FROM
(

SELECT 100000009 AS industrycode, 'case1' AS accountid
UNION ALL
SELECT 100000003 AS industrycode, 'case2' AS accountid
UNION ALL
SELECT 100000000 AS industrycode, 'case3' AS accountid
) D

    You indicated accountid is a guid but for my testing, I wanted text so I could eyeball it.

    If you have any more cases than this, I would advocate skipping the derived column transformation and use a Lookup Task. Write your query like

    SELECT *
FROM
(
VALUES
(100000009, cast('280f1c20-ad3b-eb11-c345-000d3a23cdb4' AS uniqueidentifier))
,(100000003, cast('23cc76d9-d345-eb11-a813-000d3a23cdb44' AS uniqueidentifier))
)D(industrycode, cluster)

    I'd configure it to Ignore lookup failures. If you run it using my source query, you'll see the "else" case of an all zero guid is not handled.

    Solve that problem by adding a Derived Column afterwards that uses an expression like

    IsNull(cluster_lkp)? (DT_GUID) "{00000000-0000-0000-0000-000000000000}" : cluster_lkp