Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Slowly Changing Dimensions: Change Source via config


Slowly Changing Dimensions: Change Source via config

Author
Message
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)

Group: General Forum Members
Points: 591 Visits: 898
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.
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)

Group: General Forum Members
Points: 591 Visits: 898
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search