March 7, 2013 at 7:11 am
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.
March 7, 2013 at 7:48 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply