Hello,
I'm beginning using SSIS and I've got myself the wrox book (I use 2005 but I could only find the 2008 version of the book) recently so I am going over it for help but I have a question.
I have a table in a source operational DB. I want to load the information from that table into my DW dimension table. That fact table has the same fields and an extra surrogate key. I wanted to have a way to find if the rows were either missing or required an update. I know there is a SCD transform available for this kind of operation, but using only other types of transforms, how would you go about it? So far I've got the insertion of missing rows covered by using the error output of a lookup transform to insert in my dimension table. However I have a problem with that way of working: since I am already in the error part of the flow, I cannot provide extra logging from my OLE DB destination to insert errors into a dedicated table. When I try to do so, it tells me that "the error row disposition on OLE DB Destination input cannot be set to redirect the...". Thing is, I'm not sure that was the right way to go about it from the beginning so I am asking for your help here
.
How do you perform this type of operation without using the SCD transform? I wanted to do it this way:
- Get data from operational source and compute a hash of its attributes
- Look it up in the dimension and compare hashes
- Insert missing rows in the dimension (should provide and error output in case it fails)
- Update only rows that have different hashes from the operational source (should provide an error output in case of failure as well)
I just have a hard time trying to map which component of the data flow I would use for this. The wrox book always works from flat files and not so much with the type of setup I am describing here.
Many thanks,
Greg
I'm beginning using SSIS and I've got myself the wrox book (I use 2005 but I could only find the 2008 version of the book) recently so I am going over it for help but I have a question.
I have a table in a source operational DB. I want to load the information from that table into my DW dimension table. That fact table has the same fields and an extra surrogate key. I wanted to have a way to find if the rows were either missing or required an update. I know there is a SCD transform available for this kind of operation, but using only other types of transforms, how would you go about it? So far I've got the insertion of missing rows covered by using the error output of a lookup transform to insert in my dimension table. However I have a problem with that way of working: since I am already in the error part of the flow, I cannot provide extra logging from my OLE DB destination to insert errors into a dedicated table. When I try to do so, it tells me that "the error row disposition on OLE DB Destination input cannot be set to redirect the...". Thing is, I'm not sure that was the right way to go about it from the beginning so I am asking for your help here
.How do you perform this type of operation without using the SCD transform? I wanted to do it this way:
- Get data from operational source and compute a hash of its attributes
- Look it up in the dimension and compare hashes
- Insert missing rows in the dimension (should provide and error output in case it fails)
- Update only rows that have different hashes from the operational source (should provide an error output in case of failure as well)
I just have a hard time trying to map which component of the data flow I would use for this. The wrox book always works from flat files and not so much with the type of setup I am describing here.
Many thanks,
Greg