DTS Package Help - I'm Stuck!

Question

Post reply

DTS Package Help - I'm Stuck!

winntshawn

SSC-Addicted

Points: 438
More actions
October 20, 2003 at 12:22 pm

#107216

Good day all,
I am having some issues with a DTS package that I need to get running. We currently have data stored on 2 separate systems - one an RDBMS on NT (not SQL Server) and the other on an AS400. I have to collect data from both locations, do some minor formatting, and save the finished results in a table on our SQL 2000 server. Everything seems to be going fine until:
We sometimes have a situation where information is duplicated between the NT data and the AS400 data. If there is a duplication, the AS400 data is authortive, otherwise I need to insert the NT data into the finished table. The AS400 data is inserted into the finished table before the NT data. I wrote an ActiveX Script task to make a compaison and insert the data if it was not already in the finished table, but I keep getting a weird SQL error about unterminated quotation marks:
Error Source: Microsoft OLD DB Provider for SQL Server
Error Description: Line 1: Incorrect systax near 'Y'.
Error on Line 57
Unclosed quotation mark before the character string ')'.
Line 1: Incorrect systax near 'Y'.
To figure out if I was doing something wrong with the sript, I took the bulk of the code and tried to perform the same comparison/insert operation from an ASP page ..... and the damn thing worked!?!?!?!
Any help would be appreciated.
PS. I will post the offending code following this message.
Thanks in advance,
Shawn

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

winntshawn SSC-Addicted Points: 438 More actions · Answer 1

Code from ASP page:

<%

On Error Resume Next

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Server.ScriptTimeout = 900

' These values were copied from the ADOVBS.INC file.

'---- CursorTypeEnum Values ----

Const adOpenForwardOnly = 0

Const adOpenKeyset = 1

Const adOpenDynamic = 2

Const adOpenStatic = 3

'---- CommandTypeEnum Values ----

Const adCmdUnknown = &H0008

Const adCmdText = &H0001

Const adCmdTable = &H0002

Const adCmdStoredProc = &H0004

dim countr1, countr2

' Instantiate the ADO objects.

set mySourceConn = CreateObject("ADODB.Connection")

set mySourceRecordset = CreateObject("ADODB.Recordset")

set myDestRecordset = CreateObject("ADODB.Recordset")

'Set the connection properties to point to SerialDB.

'Use the Transition table.

mySourceConn.Open = "Provider=SQLOLEDB.1;Data Source=(local); Initial Catalog=SerialDB;user id = 'Serialtest';password='password'"

mySQLCmdText1 = "SELECT * FROM Transition"

mySQLCmdText2 = "SELECT SerialNumber FROM SerialNumbers"

'Execute the mySQLCmdText1, and put the data into the mySourceRecordset object.

mySourceRecordset.Open mySQLCmdText1, mySourceConn, adOpenKeyset

'Execute the mySQLCmdText2, and put the data into the myDestRecordset object.

myDestRecordset.Open mySQLCmdText2, mySourceConn, adOpenKeyset

If mySourceRecordset.RecordCount < 1 Then

MsgBox " There are no records found. Return a Failure code"

Main = DTSTaskExecResult_Failure

Else

For countr1 = 1 to mySourceRecordset.RecordCount

SourceSerialNumber = mySourceRecordset.Fields.Item("SerialNumber")

For countr2 = 1 To myDestRecordset.RecordCount

DestSerialNumber = myDestRecordset.Fields.Item("SerialNumber")

Select Case SourceSerialNumber

Case DestSerialNumber

myDestRecordset.MoveNext

Case Else

serial = "'" & mySourceRecordset.Fields.Item("SerialNumber") & "'"

item = "'" & mySourceRecordset.Fields.Item("ItemNumber") & "'"

desc = "'" & mySourceRecordset.Fields.Item("ItemDescription") & "'"

po = "'" & mySourceRecordset.Fields.Item("PONumber") & "'"

order = "'" & mySourceRecordset.Fields.Item("OrderNumber") & "'"

ship = "'" & mySourceRecordset.Fields.Item("ShipDate") & "'"

InsertSQL = "INSERT INTO SerialNumbers VALUES ('" & mySourceRecordset.Fields.Item("SerialNumber") & "','" & CStr(mySourceRecordset.Fields.Item("ItemNumber")) & "','" & CStr(mySourceRecordset.Fields.Item("ItemDescription")) & "','" & mySourceRecordset.Fields.Item("PONumber") & "','" & mySourceRecordset.Fields.Item("OrderNumber") & "','" & mySourceRecordset.Fields.Item("ShipDate") & "')"

Response.Write InsertSQL & "<br>"

mySourceConn.Execute InsertSQL

myDestRecordset.MoveFirst

Exit For

End Select

myDestRecordset.MoveFirst

myDestRecordset.close

set myDestRecordset = nothing

mySourceRecordset.close

set mySourceRecordset = nothing

mySourceConn.close

set mySourceConn = nothing

%>

===========================================

Thanks in advance,

Shawn

winntshawn SSC-Addicted Points: 438 More actions · Answer 2

Code from DTS:

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Function Main ()

' These values were copied from the ADOVBS.INC file.

'---- CursorTypeEnum Values ----

Const adOpenForwardOnly = 0

Const adOpenKeyset = 1

Const adOpenDynamic = 2

Const adOpenStatic = 3

'---- CommandTypeEnum Values ----

Const adCmdUnknown = &H0008

Const adCmdText = &H0001

Const adCmdTable = &H0002

Const adCmdStoredProc = &H0004

dim countr1, countr2

' Instantiate the ADO objects.

set mySourceConn = CreateObject("ADODB.Connection")

set mySourceRecordset = CreateObject("ADODB.Recordset")

set myDestRecordset = CreateObject("ADODB.Recordset")

'Set the connection properties to point to SerialDB.

'Use the Transition table.

mySourceConn.Open = "Provider=SQLOLEDB.1;Data Source=(local); Initial Catalog=SerialDB;user id = 'Serialtest';password='password'"

mySQLCmdText1 = "SELECT * FROM Transition"

mySQLCmdText2 = "SELECT SerialNumber FROM SerialNumbers"

'Execute the mySQLCmdText1, and put the data into the mySourceRecordset object.

mySourceRecordset.Open mySQLCmdText1, mySourceConn, adOpenKeyset

'Execute the mySQLCmdText2, and put the data into the myDestRecordset object.

myDestRecordset.Open mySQLCmdText2, mySourceConn, adOpenKeyset

If mySourceRecordset.RecordCount < 1 Then

MsgBox " There are no records found. Return a Failure code"

Main = DTSTaskExecResult_Failure

Else

For countr1 = 1 to mySourceRecordset.RecordCount

SourceSerialNumber = mySourceRecordset.Fields.Item("SerialNumber")

For countr2 = 1 To myDestRecordset.RecordCount

DestSerialNumber = myDestRecordset.Fields.Item("SerialNumber")

Select Case SourceSerialNumber

Case DestSerialNumber

myDestRecordset.MoveNext

Case Else

serial = "'" & mySourceRecordset.Fields.Item("SerialNumber") & "'"

item = "'" & mySourceRecordset.Fields.Item("ItemNumber") & "'"

desc = "'" & mySourceRecordset.Fields.Item("ItemDescription") & "'"

po = "'" & mySourceRecordset.Fields.Item("PONumber") & "'"

order = "'" & mySourceRecordset.Fields.Item("OrderNumber") & "'"

ship = "'" & mySourceRecordset.Fields.Item("ShipDate") & "'"

InsertSQL = "INSERT INTO SerialNumbers VALUES ('" & mySourceRecordset.Fields.Item("SerialNumber") & "','" & CStr(mySourceRecordset.Fields.Item("ItemNumber")) & "','" & CStr(mySourceRecordset.Fields.Item("ItemDescription")) & "','" & mySourceRecordset.Fields.Item("PONumber") & "','" & mySourceRecordset.Fields.Item("OrderNumber") & "','" & mySourceRecordset.Fields.Item("ShipDate") & "')"

mySourceConn.Execute InsertSQL

myDestRecordset.MoveFirst

Exit For

End Select

myDestRecordset.MoveFirst

myDestRecordset.close

set myDestRecordset = nothing

mySourceRecordset.close

set mySourceRecordset = nothing

mySourceConn.close

set mySourceConn = nothing

Main=DTSTaskExecResult_Success

End Function

==========================================

Thanks in advance,

Shawn

philcart SSC-Forever Points: 47796 More actions · Answer 3

First thing I'd do is drop all that scripting.

Create two staging tables, one for your AS400 data, one for your NT data.

1) Import your data using a Datapump task into the staging tables.

2) Delete records from the NT staging table where they exist in the AS400 staging table.

3) Insert records from your AS400 staging table into the SQL Server destination table.

4) Insert records from your NT staging table into the SQL Server destination table.

Steps 2-4 can be done in stored procedures therefore taking advantage of SQL's set based approach.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

tadasql SSC Enthusiast Points: 149 More actions · Answer 4

hi, have not really looked at your scripts, but could it be possible there's an single quote in the data extracted...

it might be useful if you could response.write or somehow capture the query statement...

cneuhold Hall of Fame Points: 3375 More actions · Answer 5

hi!

sorry, hadn't time to think it all over, but two things struck me:

1. don't use "for to recordcount" to iterate through recordsets! use "while not eof", thats more secure!

2. if you have to use "recordcount" make sure you're using client side cursors, otherwise you'll be dependent of your OLEDB provider, if it support recordcounts on server side cursors.

3. warning: ADO behaviour has changed from 2.7 on. if not defined differently, it now creates server side cursors. use "connection.cursorlocation = aduseserver".

best regards,

chris.

nicholsmarkd SSC Enthusiast Points: 101 More actions · Answer 6

I agree that doing this type of operation in staging tables is a better choice than using scripting. I typically do this in staging tables and then use straight SQL to do the dup check/elimination stuff as well as formatting, cleansing, and any other type of data modification operations.

winntshawn SSC-Addicted Points: 438 More actions · Answer 7

Thanks for the suggestions guys! I am going to try it using staging tables today, I'll let you know hwo it goes!

Cheers,

Shawn

winntshawn SSC-Addicted Points: 438 More actions · Answer 8

Man, that was soooo much easier using the staging tables and SQL commands to work with the data instead of scripts. I guess I was just looking at the wrong way.

Thanks again!

Cheers,

Shawn