Export to CSV Using ActiveX in DTS Sql Server 2000 2

[computergiant]

Hello,
I need to export data from two tables into a single CSV file from SQL Server 2000. This needs to happen inside a DTS package for automation purposes.

TABLE1 has 8 fields and will be the first line of the CSV file.

TABLE2 has 7 fields and will be the 2nd line of the CSV file.

I belive this is possible using ActiveX VBScript. I understand ASP but getting this to work in ActiveX I am not sure of. Any sample code would be excellent. Also, how do I make the connection? Will it be via an adobconnectin string or using the graphical connector in DTS?
Thanks,

 

[fredericofonseca]

sql 2000 (and 2005 probably. haven't tried yet.)


In the DTS designer setup a connection to the server, a connection to text file (destination), set up the properties of the destination as being delimited, "comma", and then setup a data transform task, with source in the SQL connection and destination on the text file connection.

You then do your SQL on the source property of the Data transform task.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[MaffewW]

How I'd done this before was to set up 3 x connection in dts

1 x sqlserver, and 1 to each csv file.

on succcess of the 2 x exports merge them

DECLARE @cmd varchar(1000)
SET @cmd = 'copy \\share\file1 + \\share\file2 \\share\merged.csv'
EXEC master..xp_cmdshell @cmd


Matt

Brighton, UK

 

[computergiant]

Thanks for the tips! I went ahead and wrote an ASP page, then parsed through it to convert it to an ActiveX script.
Good idea about exporting to multiple csv files, however I didn't want so many objects cluttering my DTS.
Thanks.

Function Main()

Dim conn, strConn
Dim RS, RS2, RS3, RS4
Dim SQL, SQL2, SQL3, SQL4
Dim AckCode
Dim sData, sOutput, FileName, fs, MyFile

Set conn = CreateObject("ADODB.connection")
conn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=;Initial Catalog=HSNOrders;Data Source=ATLAS"

'Get HSNOrderHeader information
Set RS = CreateObject("ADODB.Recordset")
SQL = "SELECT * FROM HSNOrderHeader" ' INSERT INTO MOMImport(altnum, lastname) VALUES('ABCDE', '12345');"
Set RS = conn.Execute(SQL)

'Get HSNOrderFooter Information
Set RS2 = CreateObject("ADODB.Recordset")
SQL2 = "SELECT * FROM HSNOrderFooter"
Set RS2 = conn.Execute(SQL2)

'Get HSNOrderDetails Information - number of orders we received in the file
Set RS3 = CreateObject("ADODB.Recordset")
SQL3 = "SELECT COUNT(*) AS HSNOrderCount FROM HSNOrderDetails"
Set RS3 = conn.Execute(SQL3)

'Get MOMImport Information - number of orders we processed into Mail Order Merchant (M.O.M.)
Set RS4 = CreateObject("ADODB.Recordset")
SQL4 = "SELECT COUNT(*) AS MOMOrderCount FROM MOMImport"
Set RS4 = conn.Execute(SQL4)

IF CINT(RS2.Fields("OrderCount")) = CINT(RS4.Fields("MOMOrderCount")) THEN AckCode = "A" END IF
IF CINT(RS2.Fields("OrderCount")) > CINT(RS4.Fields("MOMOrderCount")) THEN AckCode = "P" END IF
IF CINT(RS2.Fields("OrderCount")) < CINT(RS4.Fields("MOMOrderCount")) THEN AckCode = "R" END IF


'write to CSV file
'sOutput stores the final output
'sData stores each line output

'==== write the title (name of the column) ===
'sData = Chr(34) & "First Name" & Chr(34) & ","
'sData = sData & Chr(34) & "Last Name" & Chr(34)
sData = Chr(34) & "H" & Chr(34) & "," & Chr(34) & "FA" & Chr(34) & "," & Chr(34) & RS.Fields("InterchangeSenderID") & Chr(34) & "," & Chr(34) & RS.Fields("InterchangeReceiverID") & Chr(34) & "," & Chr(34) & RS.Fields("HSNDate") & Chr(34) & "," & Chr(34) & RS.Fields("HSNTime") & Chr(34) & "," & Chr(34) & RS.Fields("VendorID") & Chr(34) & "," & Chr(34) & RS.Fields("OurGSControlNumber") & Chr(34)

sOutPut = sOutPut & sData & vbCrLf

'===== now output 2 line of data =======
sData = Chr(34) & "AK" & Chr(34) & "," & Chr(34) & RS.Fields("GSControlNumber") & Chr(34) & "," & Chr(34) & RS.Fields("FunctionalID") & Chr(34) & "," & Chr(34) & AckCode & Chr(34) & "," & Chr(34) & RS2.Fields("OrderCount") & Chr(34) & "," & Chr(34) & RS3.Fields("HSNOrderCount") & Chr(34) & "," & Chr(34) & RS4.Fields("MOMOrderCount") & Chr(34)

sOutPut = sOutPut & sData '& vbCrLf

FileName="d:\HSN_To_MOM\997b.csv" 'default file name

'HEREI SAVE THE FILE DIRECTLY TO A DIRECTORY
Set fs = CreateObject("Scripting.FileSystemObject")
Set MyFile = fs.CreateTextFile(FileName, True)

MyFile.writeline sOutPut

conn.Close

Set conn = Nothing
Set SQL = Nothing
Set SQL2 = Nothing
Set SQL3 = Nothing
Set SQL4 = Nothing
Set RS = Nothing
Set RS2 = Nothing
Set RS3 = Nothing
Set RS4 = Nothing
Set sOutput = Nothing
Set sData = Nothing

Main = DTSTaskExecResult_Success

End Function