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.

Different Edition of SSIS

We have an application that needs SSIS on the application server to upload data to our SQL database server which runs enterprise edition. Do I need to install the same editon of SSIS on the application server? Can SQL 2005 Standard editon SSIS communicate with the Enterprise Edition? Thanks.

Yes, you can use any SQL Server edition that includes an SSIS license on your application server.
|||

isd83 wrote:

We have an application that needs SSIS on the application server to upload data to our SQL database server which runs enterprise edition. Do I need to install the same editon of SSIS on the application server? Can SQL 2005 Standard editon SSIS communicate with the Enterprise Edition? Thanks.

Yep, the standard or enterprise versions of SQL Server are required. Either will work in this case.

|||Thank you Phil and JayH for you help.

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

differences in SSIS file locations in SQL Server Agent step

When adding an SSIS step to a SQL Server Agent job, when selecting the location of a config file, the dialog lets you select from the database server you're working with. If selecting the location of the package itself (when the source is File System), the dialog lets you select from the machine where Management Studio is sitting instead of from the database server. Is that intentional? And if so, why? Should I just use a fully qualified file name for the package location rather than one using a drive letter?
Hmmm... Yeah, I don't like that. Though I always deploy by storing the packages in SQL Server versus the file system.

Let's let some of the other guys chime in.... I do know that some of the folks around here never use the SSIS step type in Agent and instead use the Command Line type. That allows them to build the DTEXEC statement the way they need it to be.|||

Phil Brammer wrote:

I do know that some of the folks around here never use the SSIS step type in Agent and instead use the Command Line type. That allows them to build the DTEXEC statement the way they need it to be.

That's might be the way I go. In production we'll be using Control-M anyway, so I might as well just have the command-line all prepped and ready to go for that.
|||

Phil Brammer wrote:

Let's let some of the other guys chime in.... I do know that some of the folks around here never use the SSIS step type in Agent and instead use the Command Line type. That allows them to build the DTEXEC statement the way they need it to be.

And it lets us get the full output of DTEXEC, which is handy for troubleshooting.