DTS - Active X

[be1s]

I would like to use the Active Script task, to write the results of a SQL task to a text file. This is not a straight delimited export. I'll need to format the the text file according to some specs, assuming the end user has nothing to read this file but a text editor. Is this possible? If so, can you direct me to a resourse on the web. Thanks.

 

[be1s]

I've figured out how to do this. My code parses fine however, when I execute the ActiveX task i get the following error

"invalid procedure call or argument"


Here's is part of my code where the error is ocurring and it has to do with the FileSystemObject:

Function Main()

dim myConn
dim myRecordset
dim pcpName
dim objFSO
dim ObjTextFile


Set objFSO = CreateObject ("Scripting.FileSystemObject")

Set objTextFile = objFSO.OpenTextFile ("c:\pendingClaimsMD", ForWriting)

' instantiate the ADO objects
set myConn = CreateObject("ADODB.Connection")
set myRecordset = CreateObject("ADODB.Recordset")

' set the connection properties to point to the claimsnet database,

myConn.Open = "Provider=SQLOLEDB.1;Data Source=(local);UID=;PWD=;Trusted_Connection=Yes;Initial Catalog=PCM_Claimsnet;"

mySQLCmdText = "Select * from ccm_pendingclaimsMD"

myRecordset.Open mySQLCmdText, myConn

etc, etc, etc..

end function

 

[michelleqw]

Maybe I do have the solution for you,

I had to import with vb code tab dellimited files into my sqlserver with the help of dts.
If you are stepping through the wizard (tools / dts /imp-exp), one of the last thing they are asking:

save dts package, choose vb file.

Sqlserver will now make a .bas file. This file you can use in the vb code!

Nice regards,

Michelle.

 

[be1s]

Thanks Michelle, but I don't VB installed on my computer.

 

[gradley]

What statement are you getting the "invalid procedure call or argument" error? I believe the ForWriting parameter in the following statement may be giving you the problem:

 "Set objTextFile = objFSO.OpenTextFile ("c:\pendingClaimsMD", ForWriting)"

That is a property in FSO. I believe you need to define it at the top of your procedure as follows:

Const ForWriting = 2

Otherwise you can just use the number 2 in place of ForWriting.

I hope this helps!

 

[michelleqw]

You don't need vb.

Start ms-access (97 or 2000), create a new module, goto extra / references and choose Microsoft DTSpackage Library.

Copy the code from the .bas file into the module.

Set the cursor on the public main() press F5 for running the code or F8 for debugging.

Is this simple or not!

Michelle.

 

[be1s]

Thank you for your responses.

And to gradley, your solution worked!