issue in ddq, when connection is changed
-
For DTS deployment on production(using .dts file) we will need to change the connections once dts is installed. The dts package uses data driven queries(ddq). The problem happens when the destination connection is changed, we lose the parameter sequence for the Update query which is required to be set in the way the query was constructed.
e.g
UPDATE LOB_Mst
SET Code = ?, Name = ? WHERE (Int_Id = ?)
The parameter sequence was initially Code,Name, Int_Id however once the destination connection is changed this sequence gets reset.
Any Ideas ??
Thanks in advance.Ashutosh
-
I was not aware that the DTS would do this (I have not made much use of DDQs), but I`m not particularly surprised
Are you changing the connection Manually (IE via the DTS designer) or dynamically (In the DTS itself)?
If manually then you might as well just put the parameters back in manually and live with it.
If dynamically then you probably cannot stop it doing this, but you might be able to fix it dynamically by re-setting the parameters using a dynamic properties task.
Hope this helps...
"Don`t try to engage my enthusiasm, I don`t have one."(Marvin)
-
Couple of ways to address this.
a. define sp's for each update passing dynamic parameters as input fields and exec sp's with global parameters set to dynamic fields in exec sql tasks. This task can have connections set to global variables for the DTS.
b. Define global variables for each dynamic variable/field and use these in Active X script to define connections/updating the DB
Hope this helps!
-
A big Thank You, it works using global variables.
Just one issue, it is ok to set global variables for server and user however for pwd the DBA and the server guys will be mighty unhappy to find the pwd stored in readable format i.e not masked.
Just to recap, I am trying to do this so that there is smooth promotion from UAT to PROD server
Many Regards
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply