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

 ‘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

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

About: Carol

I've been doing IT for 30 years, and IdM for 15. I live in Australia and build IdM solutions based on Microsoft Identity Manager. I also play the violin, but that doesn't help much with the IdM solutions.