Issue with vbscript loop for sending data to excel using DTS

[scabral79]

Hi,

i created a DTS package that runs a stored proc and passes the results to an excel spreadsheet. The stored proc takes 1 parameter (BankNum).

Because there are numerous BankNum values, I was trying to create an ActiveX script to loop through the dts package and run the stored proc once for each BankNum and create a seperate spreadsheet for each BankNum with the results.

Here is the ActiveX script i am using:

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

Function Main()
Dim p
Dim i
Dim arBankNum
Dim appExcel
Dim newBook
Dim oSheet

arBankNum = Array("001", "060")
Set p = dtsGlobalVariables.Parent

For i = 0 To Ubound(arBankNum)

Set appExcel = CreateObject("Excel.Application")
Set newBook = appExcel.Workbooks.Add
Set oSheet = newBook.Worksheets(1)

'Specify the column name in the Excel worksheet

oSheet.Range("A1").Value = "Req_No"
oSheet.Range("B1").Value = "Cust_Name"
oSheet.Range("C1").Value = "Cust_No"
oSheet.Range("D1").Value = "BBO_Name"
oSheet.Range("E1").Value = "TL"
oSheet.Range("F1").Value = "Priority"
oSheet.Range("G1").Value = "Risk_Rating"
oSheet.Range("H1").Value = "Loan_Line"
oSheet.Range("I1").Value = "Outstanding_Amt"
oSheet.Range("J1").Value = "Exception_Item"
oSheet.Range("K1").Value = "Exception_SubItem"
oSheet.Range("L1").Value = "Exception_Date"
oSheet.Range("M1").Value = "Age"
oSheet.Range("N1").Value = "Original_Amt"

p.GlobalVariables("BankNum") = arBankNum(i)

DTSGlobalVariables("fileName").Value = "\\wfsvmab00035004\RMT5Shrd\BBGInitiatives\" & arBankNum(i) & "." & Month(Now) & "-" & Day(Now) & "-" & Year(Now) & ".xls"

With newBook
.SaveAs DTSGlobalVariables("fileName").Value
.save
End With

'dynamically specify the destination Excel file

appExcel.quit

p.Steps("DTSStep_DTSDataPumpTask_1").Execute
Next

Set p = Nothing
Main = DTSTaskExecResult_Success
End Function


************************************************************

The issue that i am having is that the data pump task is running for both BankNum variables above and generating a seperate excel file for each, but then it runs one more time and creates a file called test.xls, which is what is defaulted in the file name field on the excel connection step and all the results for both banks are being populated in the spreadsheet.

What i want to do is have the data pump task run once for each bank num, generate a seperate excel spreadsheet for each bank num and populate each spreadsheet with the correct data for each bank num.

thanks,
scott

 

[AlexCuse]

I can't say I'm a big fan of the way you are looping. You might want to have a look at this:

http://www.sqldts.com/246.aspx

You'd need to build on that example some of course, but I think that their example is much easier to maintain.

Anyway, what you need to do is change the destination file for your data pump task using vbScript. I will see if I can track down an example.

Also, when you post code, post it in [ignore]

the code...

[/ignore] tags. This will give the result

the code...

Which will be much easier to read.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom