question

kennethrbell avatar image
kennethrbell asked

text parse data transformation in ssis

ssis This seems so simple, yet I can't figure it out. I am transferring one table to another in a different database. I am using SQL Server Data Tools 2010. I have everything lined up, column to column. I have one column that needs help. Both the source and destination are nvarchar(3), and represent a code. In the source file, if the code is only two letters it is appended with an x. So ap will read as apx. I guess this was to ensure that every cell contained 3 letters. The destination does not have the x, ap will read ap. I use several joins with the destination table, but when I have joined the source table it only joins when there is not an x. Can I do some kind of case statement, if source ends in x only insert first two letters, else (i.e. no x) insert as is. I am not sure which component to use. I've been working on this for hours. Thanks
ssisdata-transferdatasource
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

west007 avatar image
west007 answered
Hi many options assume there are only this appended x -> join on dest.col =replace(src.col,'x','') or redesign, check if appending that x on destination can be done too, or undone on source,, so the joins would work
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

iainrobertson avatar image
iainrobertson answered
The Derived Column transformation is likely to be a good starting point here. Create a new column and add this as the transformation expression: (Right([YourCodeColumn],1)=="x"?substring([YourCodeColumn],1,2):[YourCodeColumn]) It's basically an if...then...else construct: logical test ? value if true : value if false
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

kennethrbell avatar image
kennethrbell answered
Thanks for the ideas everybody. I do this task approx. once per month. The last time I ended up exporting to Excel, making the mods and then importing back into SQL. I will try these options the next time. Thanks again.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.