April 23, 2002 at 11:13 pm
How do i assign the Binding table for the Data Driven Query Task? MSDN states that this is done through the Bindings Tab (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_elemtsk1_97lj.asp) but that would be present only in SQL2000. How do i work around this using VB ActiveX Script perhaps? Am using SQL7 SP3 only on WinNT 4 SP6a. Thanks!
April 24, 2002 at 2:51 am
Could you give a little more detail on just what you're trying to do with DTS. I use both 2000 and 7.
I have found it is easy to please a great many people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
April 24, 2002 at 5:11 am
i would like to copy records from one table in a remote site (tblProduct) to a central database in another site, primary key being that of fldBarCode. If, for example, the barcode, does not exists in the Central Database, DTS should copy that row/record. If found existing, it should update only certain fields for that BarCode like fldItemNo and fldPrice. i found that Data Driven Query Task is right for the job via InsertQuery and UpdateQuery properties. i used a LookUp connecting to the Central Database via a 3rd Connection to return Product Description as reference for the existing BarCode. To illustrate, in my Data Driven Query Task Properties Dialog Box...
Source Connection: Connection1
Source Table: [DBLocal].[dbo].[tblProduct]
Destination Connection: Connection2
Destination Table: [DBRemote].[dbo].[tblProduct]
Transformations: (i used ActiveX Script)
*********************************
Function Main()
Dim BarCode, ProductName
BarCode = DTSSource("fldBarCode").Value
ProductName = DTSLookups("Existence").Execute(BarCode)
If Len(ProductName) = 0 Then
Main = DTSTransformstat_InsertQuery
Else
Main = DTSTransformstat_UpdateQuery
End If
End Function
******************************
Query Type (Insert):
INSERT INTO tblProduct
(fldBarCode, fldChartAcctNo, fldItemNo, fldBrand,
fldDesc, fldPrice, fldDeleted)
VALUES ( ?, ?, ?, ?, ?, ?, ?)
Query Type (Update):
UPDATE tblProduct
SET fldChartAcctNo = ?,
fldBrand = ?,
fldDesc = ?,
fldPrice = ?,
fldDeleted = ?
WHERE fldBarCode = ?
Lookup: (Name = Existence, via Connection3 pointing to the Central Database)
SELECT LEFT(fldDesc, 1)
FROM tblProduct
WHERE fldBarCode = ?
What am i missing? DTS keeps returning a blank error message when i run this package. Where do i pass the parameters for the InsertQuery and UpdateQuery? How do i assign the binding table? Though frustrated, i was relieved by your prompt reply. Hope to get through this. Thanks!
April 24, 2002 at 9:04 am
OK you're moving into a fairly foggy area from my point of view. The strict answer to your question about defining the insert/update queries is that they are defined in the SetRowsetAndQueries property on the destination. Wow I bet that helped !!!
When I've done this before I've added a data-drive query task from the toolbox and then set the queries up in the dialog box.
In the hope of offering some relief, we usually find that we get better performance for this type of thing by using dts just to grab the data into a staging table, then running a couple of set-based queries to do the insert/update work. I guess this is because dts is executing a query per transformation whereas the set-based query after import is doing it in one hit.
To summarise build a stored procedure which calls a DTS package that grabs the source data into a staging-table. Then run an update query where you've got matches, followed by an insert query where you haven't.
No doubt someone cleverer than me may have a pure DTS solution, but we use the DTS + Query route as we find it easier to troubleshoot when data looks funny
I have found it is easy to please a great many people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
April 24, 2002 at 9:04 am
OK you're moving into a fairly foggy area from my point of view. The strict answer to your question about defining the insert/update queries is that they are defined in the SetRowsetAndQueries property on the destination. Wow I bet that helped !!!
When I've done this before I've added a data-drive query task from the toolbox and then set the queries up in the dialog box.
In the hope of offering some relief, we usually find that we get better performance for this type of thing by using dts just to grab the data into a staging table, then running a couple of set-based queries to do the insert/update work. I guess this is because dts is executing a query per transformation whereas the set-based query after import is doing it in one hit.
To summarise build a stored procedure which calls a DTS package that grabs the source data into a staging-table. Then run an update query where you've got matches, followed by an insert query where you haven't.
No doubt someone cleverer than me may have a pure DTS solution, but we use the DTS + Query route as we find it easier to troubleshoot when data looks funny
I have found it is easy to please a great many people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
April 25, 2002 at 4:55 am
Have thought of the 2nd option before but still i would like to know how to do things via Data Driven Query Task. Pls. bear with me on this. i've searched MSDN but it only has one mention of SetRowsetAndQueries which is not very helpful. Could you please give me some step by step directions on how to assign the parameters for the InsertQuery and UpdateQuery using SQL7 only. Thanks!
April 26, 2002 at 6:41 am
I'm with Crosspatch. Do the two step on the data. I use the DataPump extensively and it is very squirrely. If you look at it wrong, it'll mess up your bindings and they'll have to be manually reset one at a time from the tiny dialog box. I have one that has over 80 bindings. Major pain. If you find a solution that enables one to bind without using the dialog please post it!
June 7, 2002 at 10:38 pm
ok, i yield! 🙂 It took me a time, eventually i used a staging table containing only records falling for a specific period. The problem now is, my DTS Package fails on the Datapump Task eventhough i set Connection Timeout to 0. Is there a way i could tell the Datapump Task to use a CommandTimeout of zero? Am using a dial-up connection which is very slow and frequently experience Timeout expired on the Datapump Task. Thanks!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply