DTS help - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS help

Author

Topic

scubaculture
Starting Member

2 Posts

Posted - 2007-02-22 : 08:18:41

Hi everyone

I am an asp developer (don't laugh) and have been given a dts taks to modify. The old developer did a step using activex scripts which the powers that be want modified to be a sql task.

A client file is imported (got this working fine), these new records then need to go through the old step:

Check if each new record exists already.
If exists check another table to get a certain flag
If that flag exists check yet another table for 2 values
If those 2 values are not null update the temporary table.

So in activex script its something like

rsID.Open sqlID, myConn

If rsTaxID.Eof Then
While Not rsID.Eof
rsID.movenext
Wend
End if

Can that be done in an execute sql task?

Thanks for any help

I am not sure if this is possible in a sql task?

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-02-22 : 11:04:11

The powers that be are entirely correct in this instance!
This would be far better accomplished in an execute SQL task.
It's probably easiest if you post the current ActiveX script in its entirety, so I can give you a T-SQL equivalent.

Mark

scubaculture
Starting Member

2 Posts

Posted - 2007-02-22 : 11:23:54

Thanks very much for the reply Mark

Script:

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

' Function explanation:
' first get the tax_id where bluecare is y to select all bluecare providers
' find the matching providers in the db1a providers table using taxid, if none found insert into exception table and create text file
' once provider is matched we need to check the priv_status_history table for the dip_level and priv_status_code, again search on taxid order by sequence
' if no dip_level or priv_status_code is found insert into exception table and create text file
' client_facilities_update table is then updated to include the dip_level and priv_status_code
' check db1a for provider and priv_status_history

Function Main()

Dim rsTaxID
Dim sqlTaxID
Dim rsProviderID
Dim sqlProviderID
Dim dip_level
Dim priv_status_code
Dim rsDipPriv
Dim sqlDipPriv
Dim rsInsert
Dim sqlInsert
Dim finaltaxID
dim rsException
dim sqlException
dim rsExceptionInsert
dim sqlExceptionInsert
dim rsException2
dim sqlException2
dim sqlExceptionInsert2
dim rsExceptionInsert2
Dim master
Dim master2

Dim fso 'File system object
Set fso = CreateObject("Scripting.FileSystemObject")

IF (fso.FileExists("ProfExceptionLog.txt")) THEN

Set master = fso.OpenTextFile("ProfExceptionLog.txt",8)
master.WriteLine "File Opened @ " & now
master.WriteLine "Not found in the provider table:"

ELSE
Set master = fso.CreateTextFile("ProfExceptionLog.txt")

master.WriteLine "File Created @ " & now
master.WriteLine "Not found in the provider table:"

END IF

Dim fso2 'File system object
Set fso2 = CreateObject("Scripting.FileSystemObject")

IF (fso2.FileExists("ProfHistoryExceptionLog.txt")) THEN

Set master2 = fso2.OpenTextFile("ProfHistoryExceptionLog.txt",8)
master2.WriteLine "File Opened @ " & now
master2.WriteLine "Not found in the priv_status_history table:"

ELSE
Set master2 = fso.CreateTextFile("ProfHistoryExceptionLog.txt")

master2.WriteLine "File Created @ " & now
master2.WriteLine "Not found in the priv_status_history table:"

END IF

'get the tax_id where bluecare is y
sqlTaxID = "select tin from prof_temp where ct_bluecare = 'Y'"

Set rsTaxID = CreateObject("ADODB.Recordset")
rsTaxID.Open sqlTaxID, myConn

If rsTaxID.Eof Then

'error checking

Else

While Not rsTaxID.Eof

' find the matching providers using taxid

' from [sac-w2s-db1a].bcbsga.dbo.physicians p

sqlProviderID = " select p.provider_tax_id from providers p " & _
" where p.provider_tax_id = '" & rsTaxID("tin") & "' "

Set rsProviderID = CreateObject("ADODB.Recordset")
rsProviderID.Open sqlProviderID, myConn

If rsProviderID.Eof Then

'error checking

sqlException = "select provider_id, tin, last_name, first_name, address from prof_temp where tin = '" & rsTaxID("tin") & "' "
Set rsException = CreateObject("ADODB.Recordset")
rsException.Open sqlException, myConn

master.WriteLine rsException("provider_id") & "," & rsException("tin") & "," & rsException("last_name") & "," & rsException("first_name") & "," & rsException("address")

sqlExceptionInsert = "insert into prof_exception(tin, provider_id, last_name, first_name, dateentered) values ('"& rsException("tin") &"', '"& rsException("provider_id") &"', '"& rsException("last_name") &"', '"& rsException("first_name") &"', getdate())"
Set rsExceptionInsert = CreateObject("ADODB.Recordset")
rsExceptionInsert.Open sqlExceptionInsert, myConn

'update client fac table as nothing can be deleted, these will need to be manually edited by privelliging
strsqlupdate = "update client_facilities_update set dip_level = ' ', priv_status_code = ' ' where provider_tax_id = '"& rsTaxID("tin") &"' "
Set rsUpdate = CreateObject("ADODB.Recordset")
rsUpdate.Open strsqlupdate, myConn

else

While Not rsProviderID.Eof

'get the dip and privcode

sqlDipPriv = "select top 1 dip_level, priv_status_code from priv_status_history where car_id ='44' and provider_tax_id = '"& rsProviderID("provider_tax_id") &"' order by sequence desc"

Set rsDipPriv = CreateObject("ADODB.Recordset")
rsDipPriv.Open sqlDipPriv, myConn

If rsDipPriv.Eof Then

'no provider history found then exception report generated

sqlException2 = "select provider_id, tin, last_name, first_name, address from prof_temp where tin = '" & rsProviderID("provider_tax_id") & "' "
Set rsException2 = CreateObject("ADODB.Recordset")
rsException2.Open sqlException2, myConn

master2.WriteLine rsException2("provider_id") & "," & rsException2("tin") & "," & rsException2("last_name") & "," & rsException2("first_name") & "," & rsException2("address")

'exceptions also stored
sqlExceptionInsert2 = "insert into prof_priv_status_exception(tin, provider_id, last_name, first_name, dateentered) values ('"& rsException2("tin") &"', '"& rsException2("provider_id") &"', '"& rsException2("last_name") &"', '"& rsException2("first_name") &"', getdate())"
Set rsExceptionInsert2 = CreateObject("ADODB.Recordset")
rsExceptionInsert2.Open sqlExceptionInsert2, myConn

'update client fac table as nothing can be deleted, these will need to be manually edited by privelliging

strsqlupdate = "update client_facilities_update set dip_level = ' ', priv_status_code = ' ' where provider_tax_id = '"& rsProviderID("provider_tax_id") &"' "
Set rsUpdate = CreateObject("ADODB.Recordset")
rsUpdate.Open strsqlupdate, myConn

else

While Not rsDipPriv.Eof

' this is where you update the client_facilities_update table to include the dip_level

strsqlupdate = "update client_facilities_update set dip_level = '" & rsDipPriv("dip_level") & "', priv_status_code = '" & rsDipPriv("priv_status_code") & "' where provider_tax_id = '"& rsProviderID("provider_tax_id") &"' "
Set rsUpdate = CreateObject("ADODB.Recordset")
rsUpdate.Open strsqlupdate, myConn

rsDipPriv.movenext
Wend

end if

rsProviderID.movenext
Wend

End if

rsTaxID.movenext
Wend
End if

master2.WriteLine "file completed at: " & now
set fso = nothing
set objfso = nothing
set master = nothing
set fso2 = nothing
set objfso2 = nothing
set master2 = nothing

Main = DTSTaskExecResult_Success

End Function

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources