DTS Activex Script. Need Help!!

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

SQL Server Development (2000)

DTS Activex Script. Need Help!!

Author

Topic

jestupinan
Starting Member

6 Posts

Posted - 2002-10-30 : 08:34:24

Hello guys,

here's some background first:

I need a DTS package that insert the data allocated in some .txt files to a table in my DB. The problem is .txt files names will never be the same (they'll change every day). So, I figured out to develop a small Activex Script to handle this process.
In my package I have the "ActiveX Script Task" that will perform the operation, a "Text File (Source)" that will have a link to the .txt files, a "Transform Data Task" wich has the Transformation rules and a "Connection" to my Sql Server.

My problem:
I have the script almost ready but I haven't found the way to activate the "Transform Data Task" within the DTS(in order to migrate each .txt file to my table in my DB). Here is my script:

Function Main()
DIM cn
DIM FSO, objFolder, objFiles, objFile, TT
CONST SDIR = "C:\Logs Stream"

Set FSO = CreateObject("Scripting.FileSystemObject")

If (fso.FolderExists(SDIR)) Then
Set oPKG = DTSGlobalVariables.Parent
Set cn = oPKG.Connections("Text File (Source)")

Set objFolder = fso.GetFolder(SDIR)
Set objFiles = objFolder.Files

For Each objFile in objFiles
cn.DataSource = SDIR & "\" & objFile.name
'-- Here I need to put the sentence to
'-- execute the Step in order to migrate
'-- each .txt file
Next
end if

Main = DTSTaskExecResult_Success
End Function

I would really appreciate any help here!!

Thanks you.

SamC
White Water Yakist

3467 Posts

Posted - 2002-10-30 : 09:14:00

I've come close to getting the same solution running. Here's some things that might help, and other points you may need to know.

You can schedule the package to run at regular intervals (once it's saved on the SQL server). In Enterprise Manager, open the DTS folder for your server. Highlight your DTS package in the 'Local Packages' folder. RIGHT CLICK. Select Schedule package.

I ran into permission problems with my hosting service when trying to log results to the log file. Additionally, I can't use 'D$' filename references, which leaves me coding to a physical path. Not very portable.

You'll also find that when the DTS package executes autonomously on the remote server on schedule, the filename references resolve on the remote server. On the other hand, if the remote DTS package is executed (immediately) from enterprise manager, filenames may resolve to your client PC.

It's been hard for me to debug DTS on a remote environment when the scheduled environment is different from the execute immediate environment. I haven't resolved how to get visibility into execution problems when DTS runs autonomously on a remote server. My package runs fine when I run it immediatly, and fails when it runs under schedule.

I'd appreciate any feedback you may have on that.

Sam

mr_mist
Grunnio

1870 Posts

Posted - 2002-10-30 : 09:18:30

You should be able to get some small amount of information on the failed package by viewing the job history in the SQL Server Agent section for the job in the management bit of Enterprise Manager. This should at least point to where the problem lies, which is I would guess most likely permissions to the remote drive and/or procedures for the scheduled user.

sherrer

64 Posts

Posted - 2002-10-30 : 17:38:53

See my earlier post... it might help.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21170

You can reference steps and tasks, change their properties and excecute the entire package or each step or task individually. I am sure that you can reference them in the same manor with the code you have written so far although. They are both stored as collections.

Here is another code snip.
DTSPropertyTransfer is the DTS.Package Object

For i = 1 To DTSPropertyTransfer.Steps.Count
Set DTSStep = DTSPropertyTransfer.Steps(i)
DTSStep.Execute
iCounter = iCounter + 1
pb.Value = iCounter
lvStatus.ListItems.Add iCounter, , DTSStep.Description

If DTSStep.ExecutionResult = DTSStepExecResult_Failure Then
lvStatus.ListItems(iCounter).SubItems(1) = "Failed"
lvStatus.ListItems(iCounter).ForeColor = &HFF&
lvStatus.ListItems(iCounter).ListSubItems(1).ForeColor = &HFF&
Else
lvStatus.ListItems(iCounter).SubItems(1) = "Success"
End If
DoEvents
Next

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources