Showing posts with label as400. Show all posts
Showing posts with label as400. Show all posts

Monday, March 19, 2012

Differential rows

I am using SSIS to replicate data from an AS400 mainframe to a SQL destination. I am using a lookup column to see if the primary key is duplicated and if not, it will INSERT the row. This is all working fine. What I need to know is can I also use the Lookup transformation to look for differential data and then UPDATE the row? The primary key of the table will never change, however the data might and I need the package to recognize that this is a modified row on the mainframe and that the same row on the SQL destination server needs to be updated.

Thanks for any useful information.

I believe that I found a solution to my own problem. It looks like I need to use the Slowly Changing Dimension transformation. So far in running the test data, this is exactly what I need.

Differential rows

I am using SSIS to replicate data from an AS400 mainframe to a SQL destination. I am using a lookup column to see if the primary key is duplicated and if not, it will INSERT the row. This is all working fine. What I need to know is can I also use the Lookup transformation to look for differential data and then UPDATE the row? The primary key of the table will never change, however the data might and I need the package to recognize that this is a modified row on the mainframe and that the same row on the SQL destination server needs to be updated.

Thanks for any useful information.

I believe that I found a solution to my own problem. It looks like I need to use the Slowly Changing Dimension transformation. So far in running the test data, this is exactly what I need.|||

Thank you so much for the answer to your own question! I've been using lookup and conditional split transformation to check if non-key columns have been modified. Writing an expression to compare all columns in a 40-column table has been making me go crazy. SCD does it all for you! In the literature I had in hand SCD is always used in the data warehouse context. Since I'm using IS for the data migration (only), I've never considered it as a transforamtion that could be of any use to me.

Sunday, February 19, 2012

Different data types

I have successfully made a couple of SSIS packages to read data from a legacy AS400 to SQL Server 2005. These are working fine. I am now working on one that is trying to pull some financial data and I keep getting the following error for the decimal columns:

"The component ciew is unavailable. Make sure the component view has been created. The input column "input column AMT1 (2870) cannot be mapped to "external metadata column AMT1(2852) because they have different data types."

Now I made sure that the data sitting on the AS400 is of type decimal and the SQL server column is the same. What else do I need to look for?

Thanks for the information.

Go to the advanced editor of the source component and compare the datatypes of the input and output column...that may give you a clue. Also remeber that there is a data conversion transform available in data flow.|||

how did you do that, may I ask?

I suggest you change the name of the "output column" in the

datasource this is done by

1. right clicking the datasource

2. show advance editor

3. click input or output properties

4. expand the datareader output if its a datareader

5. click on output columns.

6. look for the columns you want to change and click it,

7. Add an "x" in the column "name" property say "xuser_id" for the orginal column "user_id"

8. click ok

9. check the mappings column mapping by following steps 1 and 2

10 add a derived column transform.

12. transform the column to the desired format

13. in the derived column transform make sure that the "derived Column" property is set to "new column"

and in the derived column name remove the "x" we add in step no. 7 of course you need to set the right format

wheewww. this is long... hahaha