{"id":123,"date":"2008-07-07T16:36:28","date_gmt":"2008-07-07T16:36:28","guid":{"rendered":"https:\/\/www.wapshere.com\/missmiis\/?page_id=123"},"modified":"2008-07-09T14:22:42","modified_gmt":"2008-07-09T14:22:42","slug":"vbscript-function-runssis","status":"publish","type":"page","link":"https:\/\/www.wapshere.com\/missmiis\/vbscript-function-runssis","title":{"rendered":"vbscript: Function RunSSIS"},"content":{"rendered":"

Run a SQL 2005 SSIS package from vbscript. Useful if you need to do some table manipulation prior to an MA import – such as when generating a Delta table<\/a>.<\/p>\n

Note the comment in the script about using objExec.stdOut. I employed a wait loop and objExec.Status before, but I had a problem with certain SSIS packages not returning a status to vbscript. even though they had completed. I have found this method to be more stable.<\/p>\n

\u00c2\u00a0<\/p>\n

Const MIIS_FOLDER = “C:\\Program Files\\Microsoft Identity Integration Server”
\nConst DB_CONNECT_STRING = “Provider=SQLOLEDB;Data Source=(local);Initial Catalog=DB-name<\/em>;Integrated Security=SSPI”
\nConst SSIS_SUCCESS = “The package execution returned DTSER_SUCCESS”
\nConst SSIS_LOG_FILE = “log_file_path<\/em>”
\nConst ForReading = 1
\nConst ForWriting = 2
\nConst ForAppending = 8
\nConst Unicode = -1<\/p>\n

‘—————————————————–
\n‘\u00c2\u00a0 Function RunSSIS
\n‘\u00c2\u00a0 + Call a SQL SSIS package
\n‘\u00c2\u00a0 + Returns 0 for success
\n‘—————————————————-<\/p>\n

Function RunSSIS(packageName)
\n\u00c2\u00a0 Dim strCmd, strOutput
\n\u00c2\u00a0 Dim objShell, objExec
\n\u00c2\u00a0 Dim iCount
\nDim SSISLogFile, objFS, objSSISLogFile<\/p>\n

Set objFS = CreateObject(“Scripting.FileSystemObject”)
\nSet objSSISLogFile = objFS.OpenTextFile(SSIS_LOG_FILE, ForWriting, TRUE)<\/p>\n

\u00c2\u00a0 WriteLog “Running SSIS package ” & packageName
\n\u00c2\u00a0
\n\u00c2\u00a0 strCmd = “dtexec \/DTS “”\\MSDB\\” & packageName & “”” \/SERVER\u00c2\u00a0server-name<\/em> \/MAXCONCURRENT “” -1 “” \/CHECKPOINTING OFF\u00c2\u00a0 \/REPORTING V”
\n\u00c2\u00a0 Set objShell = CreateObject(“WScript.Shell”)<\/p>\n

\u00c2\u00a0 Set objExec = objShell.Exec(strCmd)
\n‘ –need to use objExec.stdOut straight after Exec to force vbscript to wait
\n\u00c2\u00a0 strOutput = objExec.stdOut.ReadAll<\/p>\n

\u00c2\u00a0 If InStr(strOutput, SSIS_SUCCESS) > 0 Then
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 RunSSIS = 0
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 WriteLog “Package completed successfully.”
\n\u00c2\u00a0 Else
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 RunSSIS = 1
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 WriteLog “Package failed. See ” & SSISLogFileName & ” for the full output.”
\n\u00c2\u00a0 End If
\n\u00c2\u00a0
\n\u00c2\u00a0 objSSISLogFile.Write(strOutput)<\/p>\n

End Function<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

Run a SQL 2005 SSIS package from vbscript. Useful if you need to do some table manipulation prior to an MA import – such as when generating a Delta table. Note the comment in the script about using objExec.stdOut. I employed a wait loop and objExec.Status before, but I had a problem with certain SSIS…<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"class_list":["post-123","page","type-page","status-publish","hentry"],"jetpack_shortlink":"https:\/\/wp.me\/Pkp1o-1Z","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/123","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/types\/page"}],"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=123"}],"version-history":[{"count":0,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/123\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/media?parent=123"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}