sql server dts: Hi,

Thanks for your replies. I have a query related to passing parameter
to a stored procedure in a DTS package. The code below is whatI am
using. Basically, the code does not seem to be picking up the
'EndDate' parameter that I am passing to the 'usp_PaymentExt' SP.
Could someone please help me.

Thanks
Brendan

DTS Code

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

Function Main()

Dim obj
Dim ofile
Dim oConn
Dim objComm
Dim ors
Dim i
Dim h
Dim j
Dim sql
dim spOutput
Dim objCmd

dim startDate
dim endDate

set objComm = createobject("ADODB.Command")

set objComm = createobject("ADODB.Command")
set obj = createobject("Scripting.FileSystemObject")
set oConn = createobject("ADODB.Connection")

set ors = createobject("ADODB.Recordset")
set ors2 = createobject("ADODB.Recordset")
set ors3 = createobject("ADODB.Recordset")


set ors4 = createobject("ADODB.Recordset")

dbserver= DTSGlobalVariables("dbserver").Value

oConn.Open "driver={SQL Server};server=" &
DTSGlobalVariables("dbserver").Value &
";uid=user;pwd=pass;database=db;"



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

oConn.CommandTimeout = 60

objComm.ActiveConnection=oConn
objComm.CommandType=4
Dim oParam
Set oParam=objComm.CreateParameter("@EndDate",8)
oParam.value= DTSGlobalVariables("EndDate").Value
objComm.Parameters.Append(oParam)

objComm.CommandText="usp_PaymentExt"
ors4.CursorLocation=3

call ors4.Open (objComm)


Set objComm = Nothing

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

ors2.Open "SELECT distinct HDR,FT2003,[Date Stamp],[Time Stamp],[Batch
Seq],[Import Ind.],Filler1,convert(char(987),Filler2) from
tbl_tmpPayment", oConn

ors.Open "SELECT convert(char(5),REPLICATE(0,(5-len([Rec
Seq])))+replace([Rec Seq],'.','.')) as 'Rec Seq',[Debit
Acct],Amount,[Funds Type],[Cust. Ref],[Rel. Ref]," & _
"[Value Date],[Ident. type1],[Ident. Number1],[P1 Name/Addr -1],[P1
Name/Addr -2]," & _
"[P1 Name/Addr -3],[P1 Name/Addr -4],[Ident. type2],[Ident.
Number2],[P2 Name/Addr -1]," & _
"[P2 Name/Addr -2],[P2 Name/Addr -3],[P2 Name/Addr -4],[Details Of
Payment - Line 1]," & _
"[Details Of Payment - Line 2],[Details Of Payment - Line 3],[Bk to Bk
- 1],[Bk to Bk - 2]," & _
"[Bk to Bk - 3],[Advise by],[Charges to],[OP Name/Addr -1],[OP
Name/Addr -2]," & _
"[OP Name/Addr -3],[OP Name/Addr -4],[Acct Number 3],[TP Name/Addr
-1]," & _
"[TP Name/Addr -2],[TP Name/Addr -3],[TP Name/Addr -4],[Last
Party],[Entry Date]," & _
"[BNY TRN],[Dest. Code],[Source Curr.],[Pay
Currency],Filler5,Filler6,Description," & _
"Filler7 from tbl_tmpPayment", oConn

ors3.Open "SELECT distinct TRL, [Item Count],Total,Filler3,Filler4 from
tbl_tmpPayment", oConn

DTSGlobalVariables("OutputDirectory").Value = "\\machinename\CR\folder"
& replace(replace(replace(cstr(now()),"/",""),":","" )," ","" ) &".txt"
Set ofile =
obj.CreateTextFile(DTSGlobalVariables("OutputDirectory").Value, True)

if not ors.eof then

While Not ors2.EOF
h = 0
Do While h < ors2.Fields.Count
If h = ors2.Fields.Count - 1 Then
ofile.WriteLine ors2(h) & ""
Else
ofile.Write ors2(h)
End If
h = h + 1
Loop
ors2.MoveNext
Wend

'Data Record
While Not ors.EOF

i = 0

Do While i < ors.Fields.Count
If i = ors.Fields.Count - 1 Then
ofile.WriteLine cstr(ors(i))
Else
If ors.Fields(i).Name="Rec Seq" then
ofile.Write cstr(ors(i))
'msgbox(cstr(ors(i)))
Else
ofile.Write cstr(ors(i))
End If
End If

i = i + 1
Loop

ors.MoveNext
Wend

'Trailer Record
While Not ors3.EOF

h = 0

Do While h < ors3.Fields.Count
If h = ors3.Fields.Count - 1 Then
ofile.WriteLine cstr(ors3(h) & "")
Else
ofile.Write cstr(ors3(h))


End If
h = h + 1
Loop

ors3.MoveNext
Wend

ofile.Close



ors.Close

Set objCmd = Nothing




else
' send dummy blank file
'ofile.WriteLine " "
ofile.Close

end if '****if not ors.eof

oConn.execute "delete from tbl_tmpPaymentExt"


Main = DTSTaskExecResult_Success

End Function



SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



SP code

ALTER procedure usp_PaymentExt @EndDate varchar(6)
as
delete from tbl_tmpPaymentExt
INSERT INTO
[CBN_Integration].[dbo].[tbl_PaymentExt_Batch]([Item_Count], [Total])
VALUES(0,0)
insert into tbl_tmpPaymentExt
(HDR,FT2003,[Date Stamp],[Time Stamp],[Batch Seq],[Import
Ind.],Filler1,
Filler2,[Debit Acct],Amount,[Funds Type],[Cust. Ref],[Rel. Ref],
[Value Date],[Ident. type1],[Ident. Number1],[P1 Name/Addr -1],[P1
Name/Addr -2],
[P1 Name/Addr -3],[P1 Name/Addr -4],[Ident. type2],[Ident.
Number2],[P2 Name/Addr -1],
[P2 Name/Addr -2],[P2 Name/Addr -3],[P2 Name/Addr -4],[Details Of
Payment - Line 1],
[Details Of Payment - Line 2],[Details Of Payment - Line 3],[Bk to Bk -
1],[Bk to Bk - 2],
[Bk to Bk - 3],[Advise by],[Charges to],[OP Name/Addr -1],[OP Name/Addr
-2],
[OP Name/Addr -3],[OP Name/Addr -4],[Acct Number 3],[TP Name/Addr -1],

[TP Name/Addr -2],[TP Name/Addr -3],[TP Name/Addr -4],[Last
Party],[Entry Date],
[BNY TRN],[Dest. Code],[Source Curr.],[Pay
Currency],Filler5,Filler6,Description,Filler7,
TRL,Filler3,Filler4,paymentversion,cficashid,cficash_entry)
select * from CBN_Integration..vw_PaymentExt where [Value Date] <=
@EndDate
--update tbl_tmpPaymentExt set Total = (select sum(convert(decimal
(38,2), amount)) from tbl_tmpPaymentExt)
update tbl_tmpPaymentExt set Total = (SELECT
convert(char(20),REPLICATE(0,19-(len((sum(convert(decimal (38,2),
amount)))))) + replace(convert(char(20),(sum(convert(decimal (38,2),
amount))))+'0',' ',''))
from tbl_tmpPaymentExt)
update tbl_tmpPaymentExtset [Item Count] = (
SELECT distinct
convert(char(6),REPLICATE(0,(6-len((select count([Cust. Ref]) from
tbl_tmpPaymentExt))))+replace((select count([Cust. Ref]) from
tbl_tmpPaymentExt),'.','.'))
from tbl_tmpPaymentExt
)
DECLARE @Item_Count int
DECLARE @Total float
DECLARE @batchSeq char(6)
set @batchSeq = (select distinct [Batch Seq] from tbl_tmpPaymentExt)
select @Total =(select DISTINCT [Total] from tbl_tmpPaymentExt)
select @Item_Count =(select DISTINCT [Item Count] from
tbl_tmpPaymentExt)
update tbl_PaymentBatch
set [Item_Count] = (@Item_Count),[Total] = (@Total)
where [Batch_Seq] = @batchSeq
--select @batchSeq
insert into tbl_PaymentExt
(HDR,FT2003,[Date Stamp],[Time Stamp],[Batch Seq],[Import
Ind.],Filler1,
Filler2,[Rec Seq],[Debit Acct],Amount,[Funds Type],[Cust. Ref],[Rel.
Ref],
[Value Date],[Ident. type1],[Ident. Number1],[P1 Name/Addr -1],[P1
Name/Addr -2],
[P1 Name/Addr -3],[P1 Name/Addr -4],[Ident. type2],[Ident.
Number2],[P2 Name/Addr -1],

Hi Allan,

Thanks for you reply. I am using a script like this because I am
executing the package from an ASP page. Could you see anything wrong
with the code below? I think the problem is here:

oConn.CommandTimeout = 60

objComm.ActiveConnection=oConn
objComm.CommandType=4
Dim oParam
Set oParam=objComm.CreateParameter("@EndDate",8)
oParam.value= DTSGlobalVariables("EndDate").Value
objComm.Parameters.Append(oParam)

objComm.CommandText="usp_PaymentExt"
ors4.CursorLocation=3

call ors4.Open (objComm)
Set objComm = Nothing

I think I have tried everything to fix it but it's not working. Any
help is appreciated.

regards
Brendan


[quoted text, click to view]