question

bluebuckeye avatar image
bluebuckeye asked

SSIS data being imported into destination different than returned by Lookup transformation

I currently have two tables. The first, User, has a column "Inactive", that has a boolean value of 0 if they are Active, and 1 if they are Inactive. I am importing into a table that has a column "User Status" that only accepts the DT_STR values "A" for Active and "I" for inactive. I am using a lookup table called "User Status" that maps these together. Inactive - User Status 0 - A 1 - I I added a Data Viewer after the lookup transformation in my data flow to verify it was doing the lookup correctly, and it is. Data in the Data Viewer shows about a hundred inactive uses. But the data being imported into my destination table consists of only "A". I have recreated the destination OLE DB connection, and I have gone so far as to totally recreate my project and it's still exhibiting the same behavior. I am open to trying anything at this point.
ssislookups
1 comment
10 |1200

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

And you've confirmed that the columns are mapped correct in the destination? Does the destination have any default constraints on it?
0 Likes 0 ·

1 Answer

·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
Probably you have configured something wrong in the Lookup Transformation. But why to use that overkill Lookup Transformation for this? What about simple Derived Column Transform with simple expression. **`(DT_STR, 1, 1252)([Inactive] ? "I" : "A")`** in case the Inactive is Boolean (bit) or **`(DT_STR, 1, 1252)([Inactive] = 1 ? "I" : "A")`** in case in is an integer value.
10 |1200

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.