Combining DTS with MIIS Imports

Continuing on from this post, let’s say you’ve got a DTS package that is successfully creating Delta tables. You will now want to combine this with your MIIS Delta Import job.

Simple Scheduling

If your system is not particularly time critical you may be able to get away with very basic scheduling. SQL allows you to schedule DTS packages to run at certain times. You could perhaps run the DTS on the hour, and then run your delta imports at a quarter past.

You’d quickly run into problems with this approach as you tried to push import times closer together. There is no way to test the DTS has actually completed before starting the Import in MIIS. And how would you decide whether it was okay to remove lines from the Delta table?

With dtsrun and MASequencer

The next level of complexity is to call the DTS from outside SQL, and to co-ordinate that with the running of the Delta Import. In your SQL installation you should find a command line tool called dtsrun.exe. (On my server it is in Program FilesMicrosoft SQL Server80ToolsBinn.)

Using dtsrun you can make yourself a neat little batch file that will run the DTS. Then, using MASequencer from the MIIS Resource Toolkit, set the DTS batch file as a pre-step to running the Delta Import task in MIIS.

A VBScript Approach

I run my DTS packages as part of the scheduling script I wrote. I should really include a reference for where I got this function from – I certainly didn’t write it myself, but I’m afraid I can’t remember. So with apologies to the actual author:

Function RunDTS(packageName)
‘Run a DTS and return a status of 0 (success) or -1 (failed)
Const DTSSQLStgFlag_UseTrustedConnection = 256
Const DTSStepExecResult_Failure = 1
Const DTSStepExecResult_Success = 0
Dim objPkg, strError, iCount ‘Execute the package
WriteLog “Executing DTS “ & packageName
Set objPkg = CreateObject(“DTS.Package”)
objPkg.LoadFromSQLServer MIIS_MachineName,,,DTSSQLStgFlag_UseTrustedConnection,,,,packageName
objPkg.Execute

‘˜Check for errors
For iCount = 1 To objPkg.Steps.Count
If objPkg.Steps(iCount).ExecutionResult = DTSStepExecResult_Failure Then
strError = strError + objPkg.Steps(iCount).Name + “ failed. “ + chr(13)
End If
Next

If strError = “” Then
WriteLog “DTS package “ & packagename & “ completed successfully”
RunDTS = 0
Else
WriteLog “DTS package “ & packagename & “ failed with error ” & strError
RunDTS = -1
End If
Set objPkg = Nothing
End Function