December 11, 2018 at 3:53 pm
Dear Forum,
Regarding Expressions in Conditional Split component, I am wondering how to refer to columns of other components ? If this is possible?
Chapter 11 of the book "SQL Server Integration Services Design Patterns" mentions a condition expression (for a conditional split) along these lines and syntax (am wondering if a step is missed regarding how to make columns of prev. components available to later component .. or if the book refers to syntax type of example of an older version of SSIS?) .. :
(REPLACENULL([OLE DB Source].NAME_VAL1,"")==REPLACENULL([Lookup].NAME_VAL1,""))
i.e. suggesting it is possible to refer to columns of other components ..
This, however gives me an error message in SSIS as per this error text :
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [Conditional Split [188]]: Locating the input column named "[Lookup].[NAME_VAL1]" failed with error code 0xC0010009. The input column was not found in the input column collection.
Error at Data Flow Task [Conditional Split [188]]: Attempt to parse the expression "(REPLACENULL([OLE DB Source].NAME_VAL1,"")==REPLACENULL([Lookup].NAME_VAL1,""))" failed and returned error code 0xC00470DC. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.
Error at Data Flow Task [Conditional Split [188]]: Cannot parse the expression "(REPLACENULL([OLE DB Source].NAME_VAL1,"")==REPLACENULL([Lookup].NAME_VAL1,""))". The expression was not valid, or there is an out-of-memory error.
Error at Data Flow Task [Conditional Split [188]]: The expression "(REPLACENULL([OLE DB Source].NAME_VAL1,"")==REPLACENULL([Lookup].NAME_VAL1,""))" on "Conditional Split.Outputs[Case 1]" is not valid.
Error at Data Flow Task [Conditional Split [188]]: Failed to set property "Expression" on "Conditional Split.Outputs[Case 1]".
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
December 11, 2018 at 4:23 pm
I worked out a solution for myself for this question ..
I had to open the Lookup component, and use the checkboxes to mark more columns in the "available lookup columns" list here ..
The syntax that worked for me (in the Conditional Split component) was then as per this:
(REPLACENULL([OLE DB Source].[NAME_VAL1],"")==REPLACENULL([Lookup].[NAME_VAL1],"")) && (REPLACENULL([OLE DB Source].[DATE_VAL1],"")==REPLACENULL([Lookup].[DATE_VAL1],"")) && (REPLACENULL([OLE DB Source].[NUMBER_VAL1],"")==REPLACENULL([Lookup].[NUMBER_VAL1],""))
December 11, 2018 at 5:40 pm
actually ...
I am getting an error from this expression regarding null values ..
0xC0049067 -1073442713 DTS_E_EXPREVALSTATIC_FUNCTIONCOMPUTEFAILED An error occurred while evaluating the function.
even though I have tried to cater for nulls ..
hmmmm .. I will need to adjust the expression ...
I tried an expression that is rather obscure as per this:
(REPLACENULL([OLE DB Source].NAME_VAL1,"") == REPLACENULL(Lookup.NAME_VAL1,"")) && (REPLACENULL([OLE DB Source].DATE_VAL1,"1901-01-01") == REPLACENULL(Lookup.DATE_VAL1,"1901-01-01")) && (REPLACENULL([OLE DB Source].NUMBER_VAL1,-919.9) == REPLACENULL(Lookup.NUMBER_VAL1,-919.9))
Any advice on correctly catering for null with date and number values would be much appreciated !
I spotted this suggestion ...
https://stackoverflow.com/questions/16674384/conditional-split-fails-if-value-is-null-in-ssis
cheers, Allan
December 12, 2018 at 12:35 pm
I would import the data into SQL then I would do the transformations.
I try to keep the data flows clean, just going from source to destination.
Every now and then I would do a count or a few other tasks, but not often.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply