Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Hyukevain
Yak Posting Veteran
66 Posts |
 Posted - 2004-12-22 : 11:41:05
|
| Hi All,I'm developing a syncronization app, the purpose of my app is to extract data from HO DB and upload it into Branch DB, and that's including update existing branch data with HO data and insert new data from HO.I've use BCP to extract data into text file, send it to Branch and upload it by using DTS into temporary table, run merge script and merge it to real branch data and that's it.My problem is (I'm using SQL Server 7) I use some activeX script to change connection (just call it X) in my DTS dynamically. For instance, I create the DTS and test the flow by using DB "A", when I deploy my DTS to client, dynamically by script I change the DB to "B", but everytime I do that thing, my ExecuteTask(to run merge script) component didn't change it's connection to DB "B", it still refering to DB "A" although my ExecuteTask connection source is refering to the X that I've change the property to "B" at the very beginning of my DTS run (I use workflow).To solve the problem, I open DTS manually by using enterprise manager, change the connection to the proper database, save it without resetting the transformation that use the connection, save the DTS package, and walla............ my DTS run properly.This is very annoying when I want to implement it to another DB, such as "C". Is there any solution to this problem ??The error syntax is some thing like this "some error happen in OLE object bla bla bla, db A not found bla bla bla....."Any advice would be very helpful for me, I need them so badly.Regards,Mike |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-22 : 12:04:43
|
| Why not use bcp for the upload too?Then you can just run a stored proc or script rather than having to load a dts package (and won't have these problems either).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-12-22 : 16:26:12
|
| Another option would be to use linked servers (you get them in SQL 7 right?).If that is a viable option then you could maybe skip the bcp part as well.Otherwise what Brett(nr) says.rockmoose |
|
|
|
Hyukevain
Yak Posting Veteran
66 Posts |
Posted - 2004-12-23 : 00:29:02
|
| Thank's for quick answer from all of you,Let me define the flow more specified.Transformation from text files to branch DB is not as simple as that, it required some modified, such as code need to change to company id + old code and more, and to easiest way to do that is by using DTS transformation.All process should be done in branch server. If I do that with SP, the script must be as dynamically as it can plus if there is some problem, I have to make only little update to client (if I use SP, imagine how many SP I need to update if problem happen) and that can be solve by send 1 file dts to client without change anything in client db (less risky).To rockmoose, no, we can't use Linked server coz the purpose of this app is how can HO sync its data to branch without any connection between both DB server, if they have link, I would use replication instead of sync apps.To nr, either BCP or DTS transformation, both work well here, but before I move data from text to branch db, i move them to temporary table, the problem is, script to create temp table cannot run well because of the problem above.We tried to make any update as little as we can coz HO don't always have online connection to branch and we tried to make as minimal as we can risk that happen when we done modified in our apps.Is there any other solution to solve this problem ?Regards,Mike |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-23 : 01:59:31
|
| Well I don't understand the problem - I think you have decided to use dts and are finding a justification.As for your connection problem - add an activex script at the beginning of the package which loops through all the connections and changes them. Hold the connection info in global parameters and change them there so you can set them easily on load.There's an example of doing this here http://www.nigelrivett.net/SetDTSRunTimeValues.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Hyukevain
Yak Posting Veteran
66 Posts |
Posted - 2004-12-23 : 11:23:11
|
| Nr,Sorry for make u confuse, but the decision to use this method to minimize risk already made.After I looked to the code you gave me, I still can't solve the "problem". At the beginning of my DTS, I've made an activeX script to configure connection, transformation and ExecuteTask property and use workflow "On Success" to link it to the rest of the process. But the problem still come up again and again. ExecuteTask will be run after the activeX return "Success" code, this mean if I failed to configure the ExecuteTask property then the step wouldn't be run. But it seem the ExecuteTask ignore the changes I've made to the connection, and still use the old values.Is there any suggestion on how I manage to solve the problem ? Is there any connection between "ExecuteInMainThread" property with this problem ? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-23 : 12:01:28
|
| I've used that code in a lot of v7 sites and never had an issue.Other people have due to accessing a connection before running the code (it won't have any effect once the connection is made) or by having a task that isn't dependent on the rest of the workflow.If you put it as the first task and everything else is dependent it should be ok.Try removing everything else except the activex script and something to test the connection.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Bugs in DTS ??