ActiveX Help - SQL Server Forums

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

Import/Export (DTS) and Replication (2000)

ActiveX Help

Author

Topic

allan_oli
Starting Member

6 Posts

Posted - 2005-12-07 : 06:29:52

I have a DTS package that loads a series of text files into temporary tables, before running SQL Scripts (Execute SQL Task icon) to put this data into the main tables of my database. The package is set up so that it runs the following steps in order:

1. Create temp tables
2. Load text files
3. Run sql scripts

My problem is, I have an ActiveX script on each data load so that if a file is 0kb in size, it doesnt run it. This is fine, but how to I get it to then go on and run the sql scripts - i cannot use on completion or on success as the package just stops at 2.

Any help would be greatly appreciated as i am new to activex

Oli

allan_oli
Starting Member

6 Posts

Posted - 2005-12-08 : 11:05:35

OK heres some more info!

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

Function Main()
Dim oFSO
Dim oFile
Dim sSourceFile

Set oFSO = CreateObject("Scripting.FileSystemObject")

sSourceFile = "B:\scripts\oliver_scripts\dumfries\cmtst\cold\data\prophist.txt"

Set oFile = oFSO.GetFile(sSourceFile)

If oFile.Size > 0 Then
Main = DTSStepScriptResult_ExecuteTask
Else
Main = DTSStepScriptResult_DontExecuteTask
End If

' Clean Up
Set oFile = Nothing
Set oFSO = Nothing
End Function

This is my VBScript. If the file is empty then the task shows a status of "NOT RUN". However, after this i have an on completion precedence for a Execute SQL Task. However, as the status is not run, all of my SQL tasks do not run as they rely on "on complete" staus'

Any help would be greatly appreciated, been looking at this for a while and can't figure it out!

Oli

allan_oli
Starting Member

6 Posts

Posted - 2005-12-21 : 04:41:38

Thanks to anyone who spent any time thinking about my problem. I found the following solution

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

Function Main()
Dim oFSO
Dim oFile
Dim sSourceFile
Dim objStep
Dim objPackage

Set objPackage = DTSGlobalVariables.Parent

Set oFSO = CreateObject("Scripting.FileSystemObject")

sSourceFile = "B:\scripts\oliver_scripts\Dumfries\cmtst\cold\data\prophist.txt"

Set oFile = oFSO.GetFile(sSourceFile)

If oFile.Size > 0 Then
Main = DTSStepScriptResult_ExecuteTask
else
Main = DTSStepScriptResult_DontExecuteTask
Set objStep = objPackage.Steps("DTSStep_DTSExecuteSQLTask_4")
objStep.precedenceConstraints.Remove "DTSStep_DTSDataPumpTask_10"
End If

' Clean Up
Set oFile = Nothing
Set oFSO = Nothing

End Function

However, Adding the precedence back in at the end of the package is not so straight foward, keep getting a "Invalid Script Step Result" error, although the precedence is actually added back in, it reports an error!

Any help with this further problem would be greatly appreciated!

Oli

allan_oli
Starting Member

6 Posts

Posted - 2005-12-21 : 05:08:48

Invalid Step Script Result caused by not adding a "Main = DTSStepScriptResult_ExecuteTask" to the script for the task

All sorted now and working wonderfully!!

Oli

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-21 : 06:27:53

without DTS you can try this also
http://www.nigelrivett.net/SQLTsql/ImportTextFiles.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources