April 4, 2012 at 3:59 am
I'm new to working with SSIS and I'm poking around and playing with the transformations in the data flow.
Our current ERP system is programmed to insert a CHAR(2) value into fields if they are left blank (don't ask why, I don't know). If CHAR(2) is allowed to pass through into the dimensional model, our reporting system issues errors as it cannot show CHAR(2), obviously I cannot allow CHAR(2) to remain past the transformation step in any columns/attributes.
Previously (before migrating to MSSQL Server), I've always used a SQL statement with a transformation logic built into the source extract
CASE col_1 WHEN CHAR(2) THEN 'Blank' ELSE CHAR(2) END
But that is not very transparent if other developers take over maintenance of the system or pokes through it for ideas, so I would like to move this transformation into the data flow for transparency.
How is this best achieved? I've tried using a Derived Column transformation, but it cannot take "CASE" statements (at least, I cannot make it).
My next idea was to use a Conditional Split and route the CHAR(2) valued columns into a Derived Column transformation and Union All the output from that Derived Column transformation with the default output of the Conditional Split and inserting the data into the destination table.
That idea seems like a lot of work for something that, on the outside of the box, sounds very simple.
Another annoying detail is the inability to use the syntax <Col_1>==CHAR(2) - instead I need to use the following syntax <Col_1>=="" since the character "" is representing the CHAR(2) value.
Add to all of this a dimension table with many strings that could possibly contain attribute values of CHAR(2) independently of each other...
Any help or guidance is much appreciated.
April 4, 2012 at 4:12 am
SSIS expression language doesn't allow "CASE", but it does have an equivalent - the "?" operator. Search for "SSIS expression conditional" or something similar and you should find the syntax.
Other than that, it's not clear to me what you're trying to do. Are you trying to insert different values conditional on the data type of the column in the table? If you are, you could use the INFORMATION_SCHEMA.COLUMNS view to get the data type.
John
April 4, 2012 at 4:27 am
What I'm doing is replacing a certain value CHAR(2) when it is present in a row under a certain heading, since the value becomes "illegal" in the eyes of our BI reporting tool.
Take a table with columns ACCOUNTNUMBER and ACCOUNTNAME - if any of the account numbers have a "blank" name in our ERP system, what is written into the DB is the value CHAR(2). I need to transform these instances of CHAR(2) into something that is readable in our BI tool (in this case, I transform it to the string "Blank"). When a column with a CHAR(2) value is output from a select statement it takes the form "".
SSIS Derived Column transformation accepts the following code (thanks for the pointer on the ? command):
ACCOUNTNAME == "" ? "Blank" : ACCOUNTNAME
Is there any way to decode the CHAR value other than filling in the ""? E.g. SQL Server accepts the CHAR(2) instead of ''.
Now I just need to find out why my SSIS package skips the data flow with the Derived Column transformation.
April 4, 2012 at 4:47 am
Maybe the CODEPOINT function will work - something like this:
CODEPOINT(ACCOUNTNAME) == 2 ? "Blank" : ACCOUNTNAME
John
April 5, 2012 at 6:03 pm
That works perfectly.
Thanks for pointing me in the right direction on these issues.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply