{"id":29,"date":"2007-06-23T06:54:52","date_gmt":"2007-06-23T06:54:52","guid":{"rendered":"https:\/\/www.wapshere.com\/missmiis\/?p=29"},"modified":"2023-01-16T06:25:15","modified_gmt":"2023-01-16T06:25:15","slug":"combining-dts-with-miis-imports","status":"publish","type":"post","link":"https:\/\/www.wapshere.com\/missmiis\/combining-dts-with-miis-imports","title":{"rendered":"Combining DTS with MIIS Imports"},"content":{"rendered":"

Continuing on from this post<\/a>, 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.<\/span><\/span><\/p>\n

Simple Scheduling<\/span><\/span><\/h3>\n

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.<\/span><\/span><\/p>\n

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?<\/span><\/span><\/p>\n

With dtsrun and MASequencer<\/span><\/span><\/h3>\n

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<\/strong>. (On my server it is in Program FilesMicrosoft SQL Server80ToolsBinn.) <\/span><\/span><\/p>\n

\n

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

A VBScript Approach<\/span><\/span><\/h3>\n

I run my DTS packages as part of the scheduling script<\/a> 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:<\/span><\/span><\/p>\n

Function RunDTS(packageName)
\n‘Run a DTS and return a status of 0 (success) or -1 (failed)
\nConst DTSSQLStgFlag_UseTrustedConnection = 256
\nConst DTSStepExecResult_Failure = 1
\nConst DTSStepExecResult_Success = 0
\nDim objPkg, strError, iCount<\/span><\/span>\u00c2\u00a0\u00e2\u20ac\u02dcExecute the package
\nWriteLog \u00e2\u20ac\u0153Executing DTS \u00e2\u20ac\u0153 & packageName
\nSet objPkg = CreateObject(\u00e2\u20ac\u0153DTS.Package\u00e2\u20ac\u009d)
\nobjPkg.LoadFromSQLServer MIIS_MachineName,,,DTSSQLStgFlag_UseTrustedConnection,,,,packageName
\nobjPkg.Execute<\/span><\/span><\/p>\n

‘\u02dcCheck for errors
\nFor iCount = 1 To objPkg.Steps.Count
\nIf objPkg.Steps(iCount).ExecutionResult = DTSStepExecResult_Failure Then
\nstrError = strError + objPkg.Steps(iCount).Name + \u00e2\u20ac\u0153 failed. \u00e2\u20ac\u0153 + chr(13)
\nEnd If
\nNext<\/p>\n

If strError = \u00e2\u20ac\u0153\u00e2\u20ac\u009d Then
\nWriteLog \u00e2\u20ac\u0153DTS package \u00e2\u20ac\u0153 & packagename & \u00e2\u20ac\u0153 completed successfully\u00e2\u20ac\u009d
\nRunDTS = 0
\nElse
\nWriteLog \u00e2\u20ac\u0153DTS package \u00e2\u20ac\u0153 & packagename & \u00e2\u20ac\u0153 failed with error \u00e2\u20ac\u009d & strError
\nRunDTS = -1
\nEnd If
\nSet objPkg = Nothing
\nEnd Function<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":[]},"categories":[34,28,5,10],"tags":[],"class_list":["post-29","post","type-post","status-publish","format-standard","hentry","category-ilm2007","category-miis2003","category-sql","category-vbscript"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pkp1o-t","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/29","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/comments?post=29"}],"version-history":[{"count":3,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/29\/revisions"}],"predecessor-version":[{"id":3342,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/29\/revisions\/3342"}],"wp:attachment":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/media?parent=29"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/categories?post=29"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/tags?post=29"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}