Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Slowly Changing Dimensions: Change Source via config Expand / Collapse
Author
Message
Posted Thursday, March 07, 2013 7:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:49 PM
Points: 366, Visits: 577
Hi Guys

I have a dataflow task that connects to a Progress database and extracts a delta of records created or amended in the last N days (N is defined in a package variable that is set in the dtsConfig file: the SLQ command to be generated is built dynamically using this value to insert into some fixed text)

The target for the datareader source is an SQL table with the same strucutre. I have elected to use SCD as there should never be any deleted records, only new and changed.


The problem I have is that the schema of the table is different in DEV and TEST/PRODUCTION, although the table structure wil be the same

I can control the Schema of the Insert destination using a variable and the Update task SQL using expressions.

I don't seem to be able to control the source table. I can see the SCD source as an SQL string in the properties, but I can't see anywhere to build the string as an expression and it is not available in the Parameters Config because it is part of a dataflow task.

I can see the text to be changed if I look at the dtsx file in an XML viewer. It has PropertyID = "590" and is a property of componentID = "588". Is there any way to manually add that to the dtsConfig file?
Any Ideas.
Post #1427960
Posted Thursday, March 07, 2013 7:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:49 PM
Points: 366, Visits: 577
UPDATE:
Found half a solution so I am posting it here in case it is useful for anyone else.

I have created a synonymn for the table

CREATE SYNONYM [dbo].[MyTable] FOR [Dev].[MyTable]

You can't see in the synonym name in the SCD wizard, but if you edit the SQL command by hand and replace [Dev] with [dbo] then the component seems to run just fine.

It's not great as it relies on being able to create the synonym with the correct schema and anyone who edits the package in the future needs to know that the SQL has to be edited by hand if the component needs to be re-created, but it gets me out of a hole for now.

Obiron
Post #1427985
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse