tag:blogger.com,1999:blog-63598826837374271712024-03-14T01:33:09.433-07:00SSIS101A collection of SSIS stuff from 101 till 404. I just put the things here I run into....John Minkjanhttp://www.blogger.com/profile/08228393663535024706noreply@blogger.comBlogger16125tag:blogger.com,1999:blog-6359882683737427171.post-26205002755457635032017-09-13T05:09:00.002-07:002017-09-13T05:09:43.396-07:00SSIS: Export all packages from MSDB package storeQuick script to generate DTUTIL statements to export all SSIS package from the MSDB package store to the file system using DTUTIL:<br />
<br /><br />
<blockquote class="tr_bq">
<span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">;</span></span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">WITH</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> FOLDERS </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">AS</span></span></span><br />
<span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">(</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;">-- Capture root node</span></span></span><br />
<span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">SELECT</span></span></span><br />
<span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: magenta; font-family: "consolas"; font-size: x-small;"><span style="color: magenta; font-family: "consolas"; font-size: x-small;"><span style="color: magenta; font-family: "consolas"; font-size: x-small;">cast</span></span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">(</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">PF</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">foldername </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">AS</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">varchar</span></span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">(</span></span></span><span style="color: magenta; font-family: "consolas"; font-size: x-small;"><span style="color: magenta; font-family: "consolas"; font-size: x-small;"><span style="color: magenta; font-family: "consolas"; font-size: x-small;">max</span></span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">))</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">AS</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> FolderPath</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">,</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> PF</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">folderid</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">,</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> PF</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">parentfolderid</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">,</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> PF</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">foldername</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">FROM</span></span></span><br />
<span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> msdb</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">dbo</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">sysssispackagefolders PF</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">WHERE</span></span></span><br />
<span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> PF</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">parentfolderid </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">IS</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">NULL</span></span></span><br />
<span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;">-- build recursive hierarchy</span></span></span><br />
<span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">UNION</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">ALL</span></span></span><br />
<span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">SELECT</span></span></span><br />
<span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: magenta; font-family: "consolas"; font-size: x-small;"><span style="color: magenta; font-family: "consolas"; font-size: x-small;"><span style="color: magenta; font-family: "consolas"; font-size: x-small;">cast</span></span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">(</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">F</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">FolderPath </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">+</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: red; font-family: "consolas"; font-size: x-small;"><span style="color: red; font-family: "consolas"; font-size: x-small;"><span style="color: red; font-family: "consolas"; font-size: x-small;">'\\'</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">+</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> PF</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">foldername </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">AS</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">varchar</span></span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">(</span></span></span><span style="color: magenta; font-family: "consolas"; font-size: x-small;"><span style="color: magenta; font-family: "consolas"; font-size: x-small;"><span style="color: magenta; font-family: "consolas"; font-size: x-small;">max</span></span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">))</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">AS</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> FolderPath</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">,</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> PF</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">folderid</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">,</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> PF</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">parentfolderid</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">,</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> PF</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">foldername</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">FROM</span></span></span><br />
<span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> msdb</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">dbo</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">sysssispackagefolders PF</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">INNER</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">JOIN</span></span></span><br />
<span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> FOLDERS F</span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">ON</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> F</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">folderid </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">=</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> PF</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">parentfolderid</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">)</span></span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">,</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> PACKAGES </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">AS</span></span></span><br />
<span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">(</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;">-- pull information about stored SSIS packages</span></span></span><br />
<span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">SELECT</span></span></span><br />
<span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> P</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">name </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">AS</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> PackageName</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">,</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> P</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">id </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">AS</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> PackageId</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">,</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> P</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">description</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">as</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> PackageDescription</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">,</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> P</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">folderid</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">,</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> P</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">packageFormat</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">,</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> P</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">packageType</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">,</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> P</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">vermajor</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">,</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> P</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">verminor</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">,</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> P</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">verbuild</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">,</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: magenta; font-family: "consolas"; font-size: x-small;"><span style="color: magenta; font-family: "consolas"; font-size: x-small;"><span style="color: magenta; font-family: "consolas"; font-size: x-small;">suser_sname</span></span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">(</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">P</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">ownersid</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">)</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">AS</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> ownername</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">FROM</span></span></span><br />
<span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> msdb</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">dbo</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">sysssispackages P</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">)</span></span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">SELECT</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;">-- assumes default instance and localhost</span></span></span><br />
<span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;">-- use serverproperty('servername') and serverproperty('instancename') </span></span></span><br />
<span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;">-- if you need to really make this generic</span></span></span><br />
<span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: red; font-family: "consolas"; font-size: x-small;"><span style="color: red; font-family: "consolas"; font-size: x-small;"><span style="color: red; font-family: "consolas"; font-size: x-small;">'DTUTIL /sourceserver '</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">+</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: magenta; font-family: "consolas"; font-size: x-small;"><span style="color: magenta; font-family: "consolas"; font-size: x-small;"><span style="color: magenta; font-family: "consolas"; font-size: x-small;">@@SERVERNAME</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">+</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: red; font-family: "consolas"; font-size: x-small;"><span style="color: red; font-family: "consolas"; font-size: x-small;"><span style="color: red; font-family: "consolas"; font-size: x-small;">' /SQL "'</span></span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">+</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> F</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">FolderPath </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">+</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: red; font-family: "consolas"; font-size: x-small;"><span style="color: red; font-family: "consolas"; font-size: x-small;"><span style="color: red; font-family: "consolas"; font-size: x-small;">'\\'</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">+</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> P</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">PackageName </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">+</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: red; font-family: "consolas"; font-size: x-small;"><span style="color: red; font-family: "consolas"; font-size: x-small;"><span style="color: red; font-family: "consolas"; font-size: x-small;">'" /En file;"F:\\Archief\\SSIS\\'</span></span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">+</span></span></span><span style="color: magenta; font-family: "consolas"; font-size: x-small;"><span style="color: magenta; font-family: "consolas"; font-size: x-small;"><span style="color: magenta; font-family: "consolas"; font-size: x-small;">CONVERT</span></span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">(</span></span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">VARCHAR</span></span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">(</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">50</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">),</span></span></span><span style="color: magenta; font-family: "consolas"; font-size: x-small;"><span style="color: magenta; font-family: "consolas"; font-size: x-small;"><span style="color: magenta; font-family: "consolas"; font-size: x-small;">GETDATE</span></span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">(),</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">112</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">)+</span></span></span><span style="color: red; font-family: "consolas"; font-size: x-small;"><span style="color: red; font-family: "consolas"; font-size: x-small;"><span style="color: red; font-family: "consolas"; font-size: x-small;">'\\'</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">+</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> P</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">PackageName </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">+</span></span></span><span style="color: red; font-family: "consolas"; font-size: x-small;"><span style="color: red; font-family: "consolas"; font-size: x-small;"><span style="color: red; font-family: "consolas"; font-size: x-small;">'.dtsx";4 /Q'</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">AS</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> cmd</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">FROM</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> FOLDERS F</span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">INNER</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">JOIN</span></span></span><br />
<span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> PACKAGES P</span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">ON</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> P</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">folderid </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">=</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> F</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">folderid</span></span><br />
<span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;"><span style="color: green; font-family: "consolas"; font-size: x-small;">-- uncomment this if you want to filter out the <br /> -- native Data Collector packages</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;">WHERE</span></span></span><br />
<span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"><span style="color: blue; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> F</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">FolderPath </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><></span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: red; font-family: "consolas"; font-size: x-small;"><span style="color: red; font-family: "consolas"; font-size: x-small;"><span style="color: red; font-family: "consolas"; font-size: x-small;">'\Data Collector'</span></span></span><br />
<span style="color: red; font-family: "consolas"; font-size: x-small;"><span style="color: red; font-family: "consolas"; font-size: x-small;"><span style="color: red; font-family: "consolas"; font-size: x-small;"></span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">and</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> F</span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">.</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;">FolderPath </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">not</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;"><span style="color: grey; font-family: "consolas"; font-size: x-small;">Like</span></span></span><span style="font-family: "consolas"; font-size: x-small;"><span style="font-family: "consolas"; font-size: x-small;"> </span></span><span style="color: red; font-family: "consolas"; font-size: x-small;"><span style="color: red; font-family: "consolas"; font-size: x-small;"><span style="color: red; font-family: "consolas"; font-size: x-small;">'Maintenance%'</span></span></span></blockquote>
<br /><br />
<br /><br />
Till Next Time (Also posted on MSSQL101.BLOGSPOT.COM)John Minkjanhttp://www.blogger.com/profile/08228393663535024706noreply@blogger.comtag:blogger.com,1999:blog-6359882683737427171.post-75870959418451691532013-09-23T08:31:00.003-07:002013-09-23T08:31:50.220-07:00Script with OLE DB connections<br />#region Namespaces<br />using System;<br />using System.Data;<br />using System.Data.SqlClient;<br />using Microsoft.SqlServer.Dts.Runtime;<br />using System.Windows.Forms;<br />using System.Data.Common;<br />using System.Data.OleDb;<br />using Wrap = Microsoft.SqlServer.Dts.Runtime.Wrapper;<br />// Add referenc in menu Project / Add Refference / Microsoft.SqlServer.DtsRuntimeWrap<br />#endregion<br /><br />namespace ST_fb053e9cb2994d049ff4ca5a92cd1b7a<br />{<br /> [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]<br /> public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase<br /> {<br /> <br /> public void Main()<br /> {<br /> <br /> string PackageName ="";<br /> string ExecutionGuid ="";<br /> string CounterName ="";<br /> string CounterValue ="";<br /> <br /><br /> for (int i = 0; i <= (Dts.Variables.Count -1); i++)<br /> {<br /> if (Dts.Variables[i].Name.StartsWith("PackageName") )<br /> {<br /> PackageName = Dts.Variables[i].Value.ToString();<br /> }<br /><br /> if (Dts.Variables[i].Name.StartsWith("ExecutionInstanceGUID") )<br /> {<br /> ExecutionGuid = Dts.Variables[i].Value.ToString();<br /> }<br /> }<br /><br /><br /> for (int i = 0; i <= (Dts.Variables.Count - 1); i++)<br /> {<br /> if (Dts.Variables[i].Name.StartsWith("RC"))<br /> {<br /> <br /> CounterName = System.Text.RegularExpressions.Regex.Replace(Dts.Variables[i].Name.ToString().Substring(2), "[A-Z]", " $0");<br /> CounterValue = Dts.Variables[i].Value.ToString();<br /> //MessageBox.Show(CounterName);<br /> string CommandText = "";<br /> CommandText = "INSERT INTO MGT.[Audit].[ProcessCounts] ([ExecutionGuid],[PackageName],[CounterName],[Value])";<br /> CommandText = CommandText + "VALUES('" + ExecutionGuid + "','" + PackageName + "','" + CounterName + "'," + CounterValue + ")";<br /> ConnectionManager cm = Dts.Connections["Cn_MGT"];<br /> Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject as Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100;<br /><br /> using (OleDbConnection conn = cmParams.GetConnectionForSchema() as OleDbConnection)<br /> {<br /> OleDbCommand command = new OleDbCommand(CommandText, conn);<br /> command.ExecuteNonQuery();<br /> }<br /><br /> <br /> }<br /><br /> <br /> } <br /> Dts.TaskResult = (int)ScriptResults.Success;<br /> }<br /><br /> #region ScriptResults declaration<br /> enum ScriptResults<br /> {<br /> Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,<br /> Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure<br /> };<br /> #endregion<br /><br /> }<br />}<br />
<br />
Till Next TimeJohn Minkjanhttp://www.blogger.com/profile/08228393663535024706noreply@blogger.com0tag:blogger.com,1999:blog-6359882683737427171.post-31763123624674759752011-09-27T05:09:00.000-07:002011-09-27T05:45:10.538-07:00SSIS Script Database objects:' Microsoft SQL Server Integration Services Script Task<br />
' Write scripts using Microsoft Visual Basic 2008.<br />
' The ScriptMain is the entry point class of the script.<br />
<br />
Imports System<br />
Imports System.Data<br />
Imports System.Math<br />
Imports System.IO<br />
' compile with: <br />
' /r:Microsoft.SqlServer.Smo.dll <br />
' /r:Microsoft.SqlServer.ConnectionInfo.dll <br />
' /r:Microsoft.SqlServer.Management.Sdk.Sfc.dll <br />
Imports Microsoft.SqlServer.Dts.Runtime<br />
Imports Microsoft.SqlServer.Management.Smo<br />
Imports Microsoft.SqlServer.Management.Common<br />
Imports System.Collections.Specialized<br />
Imports Microsoft.SqlServer.Management.Sdk.Sfc<br />
<br />
<br />
<system.addin.addin("scriptmain", )="" ,="" description:="" publisher:="" version:="1.0"> _<br />
<system.clscompliantattribute(false)> _<br />
Partial Public Class ScriptMain<br />
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase<br />
<br />
Enum ScriptResults<br />
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success<br />
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure<br />
End Enum<br />
<br />
<br />
' The execution engine calls this method when the task executes.<br />
' To access the object model, use the Dts property. Connections, variables, events,<br />
' and logging features are available as members of the Dts property as shown in the following examples.<br />
'<br />
' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value<br />
' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)<br />
' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)<br />
'<br />
' To use the connections collection use something like the following:<br />
' ConnectionManager cm = Dts.Connections.Add("OLEDB")<br />
' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"<br />
'<br />
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.<br />
' <br />
' To open Help, press F1.<br />
<br />
Public Sub Main()<br />
'<br />
' Add your code here<br />
'<br />
CreateDBScript()<br />
Dts.TaskResult = ScriptResults.Success<br />
End Sub<br />
<br />
Public Sub CreateDBScript()<br />
' Connect to the local, default instance of SQL Server. <br />
Dim srv As New Server(Dts.Variables("DBInstanceName").Value.ToString)<br />
<br />
'Get DB's to script<br />
Dim scDB() As String<br />
scDB = Split(Dts.Variables("DBToScript").Value.ToString, ";")<br />
'MsgBox(Dts.Variables("DBToScript").Value.ToString)<br />
'Open the Script File<br />
Dim scrDB As New StreamWriter(Dts.Variables("ScriptOutputFileName").Value.ToString, False)<br />
<br />
'Get all the databases<br />
Dim scrp As New Scripter(srv)<br />
scrDB.WriteLine("-- Scripting databases for " + Dts.Variables("DBInstanceName").Value.ToString)<br />
<br />
For Each dbn As Database In srv.Databases<br />
If dbn.IsSystemObject = False Then<br />
Dim sc As System.Collections.Specialized.StringCollection = dbn.Script<br />
For Each st As String In sc<br />
scrDB.WriteLine(st)<br />
Next<br />
scrDB.WriteLine("--")<br />
scrDB.WriteLine("")<br />
End If<br />
Next<br />
<br />
<br />
<br />
<br />
'Loop the array<br />
For i As Integer = scDB.GetLowerBound(0) To scDB.GetUpperBound(0)<br />
<br />
' database name<br />
Dim dbName As [String] = scDB(i) ' database name<br />
<br />
' Reference the database. <br />
Dim db As Database = srv.Databases(dbName)<br />
<br />
' Define a Scripter object and set the required scripting options. <br />
<br />
scrp.Options.ScriptDrops = False<br />
scrp.Options.WithDependencies = True<br />
scrp.Options.Indexes = True ' To include indexes<br />
scrp.Options.DriAllConstraints = True ' to include referential constraints in the script<br />
scrp.Options.SchemaQualify = True<br />
scrp.Options.ExtendedProperties = True<br />
<br />
scrDB.WriteLine("-- Scripting schemas for " + dbName)<br />
<br />
For Each sch As Schema In db.Schemas<br />
<br />
If sch.IsSystemObject = False Then<br />
' Generating script for schema sch<br />
Dim sc As System.Collections.Specialized.StringCollection = sch.Script<br />
For Each st As String In sc<br />
scrDB.WriteLine(st)<br />
Next<br />
<br />
scrDB.WriteLine("--")<br />
scrDB.WriteLine("")<br />
End If<br />
Next<br />
<br />
scrp.Options.WithDependencies = True<br />
<br />
' Iterate through the tables in database and script each one. Display the script.<br />
For Each tb As Table In db.Tables<br />
' check if the table is not a system table<br />
If tb.IsSystemObject = False Then<br />
scrDB.WriteLine("-- Scripting for table " + tb.Name)<br />
<br />
' Generating script for table tb<br />
Dim sc As System.Collections.Specialized.StringCollection = scrp.Script(New Urn() {tb.Urn})<br />
For Each st As String In sc<br />
scrDB.WriteLine(st)<br />
Next<br />
scrDB.WriteLine("--")<br />
scrDB.WriteLine("")<br />
<br />
<br />
End If<br />
Next<br />
scrp.Options.WithDependencies = False<br />
' Iterate through the views in database and script each one. Display the script.<br />
For Each vw As View In db.Views<br />
' check if the table is not a system table<br />
If vw.IsSystemObject = False Then<br />
scrDB.WriteLine("-- Scripting for view " + vw.Name)<br />
<br />
' Generating script for table tb<br />
Dim sc As System.Collections.Specialized.StringCollection = scrp.Script(New Urn() {vw.Urn})<br />
For Each st As String In sc<br />
scrDB.WriteLine(st)<br />
Next<br />
scrDB.WriteLine("--")<br />
scrDB.WriteLine("")<br />
End If<br />
Next<br />
<br />
Next i<br />
scrDB.Close()<br />
End Sub<br />
<br />
End Class</system.clscompliantattribute(false)></system.addin.addin("scriptmain",>John Minkjanhttp://www.blogger.com/profile/08228393663535024706noreply@blogger.com0tag:blogger.com,1999:blog-6359882683737427171.post-78730640627158885382010-04-01T11:23:00.000-07:002010-04-01T11:23:08.972-07:00SSIS carriage return in expression builderFound it on MSDN:<br />
Property Expression for the Message of an E-mail Message<br />
<br />
<br />
The following property expression can be used to set the MessageSource property of a Send Mail task. The expression uses a combination of string literals, user-defined variables, and the concatenation (+) operator. The user-defined variables are named nasdaqrawrows, nyserawrows, and amexrawrows. The string "\n" indicates a carriage return. <br />
<br />
"Rows Processed: " + "\n" +" NASDAQ: " + (dt_wstr,9)@[nasdaqrawrows] + "\n" + " NYSE: " + (dt_wstr,9)@[nyserawrows] + "\n" + " Amex: " + (dt_wstr,9)@[amexrawrows]<br />
<br />
<br />
If nasdaqrawrows is 7058, nyserawrows is 3528, and amexrawrows is 1102, the expression evaluates to the following string.<br />
<br />
<br />
Rows Processed: <br />
<br />
<br />
<br />
NASDAQ: 7058<br />
<br />
<br />
<br />
NYSE: 3528<br />
<br />
<br />
<br />
AMEX: 1102John Minkjanhttp://www.blogger.com/profile/08228393663535024706noreply@blogger.com0tag:blogger.com,1999:blog-6359882683737427171.post-81407350453418122932009-08-06T08:34:00.001-07:002009-08-06T08:34:11.916-07:00SSIS Retain Connection<p>When you connect to an Oracle database, you sometimes need to keep the connection to stay in the same session:</p> <p><a href="http://lh6.ggpht.com/_qd3TDEvIh4Y/Snr37rXpQHI/AAAAAAAACEM/rZ_35rjFa5k/s1600-h/image7.png"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/_qd3TDEvIh4Y/Snr38J3G_ZI/AAAAAAAACEQ/zXWIDBCPQiE/image_thumb3.png?imgmax=800" width="383" height="298" /></a> </p> <p>It’s simple, goto the properties of your connection and set RetatinSameConnection to TRUE:</p> <p><a href="http://lh5.ggpht.com/_qd3TDEvIh4Y/Snr38QOVviI/AAAAAAAACEU/59eZDniCs84/s1600-h/image11.png"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/_qd3TDEvIh4Y/Snr38x9bumI/AAAAAAAACEY/vdAmJ4MruvI/image_thumb5.png?imgmax=800" width="402" height="49" /></a> </p> <p>Till Next Time</p> John Minkjanhttp://www.blogger.com/profile/08228393663535024706noreply@blogger.com0tag:blogger.com,1999:blog-6359882683737427171.post-45124823496157859922009-07-17T00:06:00.001-07:002009-07-17T00:07:12.493-07:00SSIS Dynamic File Name"D:\\TEMP\\FilePrefix_" + (DT_WSTR,30)(YEAR(GETDATE()) ) + RIGHT ( "0"+(DT_WSTR,30)(MONTH(GETDATE()) ) ,2) +RIGHT("0"+ (DT_WSTR,30)(DAY(GETDATE()) ),2)+ RIGHT ("0" + (DT_WSTR,30) (DATEPART( "Hh",GETDATE())),2) +RIGHT ("0" + (DT_WSTR,30) (DATEPART( "Mi",GETDATE())),2) +RIGHT ("0" + (DT_WSTR,30) (DATEPART( "ss",GETDATE())),2) +".TXT" <br />Till Next TimeJohn Minkjanhttp://www.blogger.com/profile/08228393663535024706noreply@blogger.com0tag:blogger.com,1999:blog-6359882683737427171.post-62478462745968938622008-12-03T04:50:00.001-08:002008-12-03T04:50:31.314-08:00SSIS Generating new package GUID<p>Remember to generate a new package id when using a package template or a copy:</p> <p><a href="http://lh6.ggpht.com/_qd3TDEvIh4Y/STaAkz44cUI/AAAAAAAABGs/nyoA6cacoIE/s1600-h/image%5B3%5D.png"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="65" alt="image" src="http://lh4.ggpht.com/_qd3TDEvIh4Y/STaAleXUbXI/AAAAAAAABGw/0DXUAjyHusM/image_thumb%5B1%5D.png?imgmax=800" width="397" border="0" /></a> </p> <p>Till Next Time</p> John Minkjanhttp://www.blogger.com/profile/08228393663535024706noreply@blogger.com0tag:blogger.com,1999:blog-6359882683737427171.post-15454143463299716532008-11-20T00:48:00.000-08:002008-11-20T00:50:01.548-08:00SSIS First of the month Expression builderDATEADD( "M", -24, (DT_DATE) ((DT_WSTR, 2) MONTH(getdate()) + "/"+"01" + "/" + (DT_WSTR, 4) YEAR( GETDATE())))<br /><div></div><br /><div>Till Next Time</div>John Minkjanhttp://www.blogger.com/profile/08228393663535024706noreply@blogger.com0tag:blogger.com,1999:blog-6359882683737427171.post-74002587307517380262008-11-19T23:50:00.001-08:002008-11-19T23:50:51.989-08:00SSIS First and Last Day of the month<blockquote><p>declare @date datetimeset </p><p>@date = getdate()</p><p>--get first day of month </p><p>select dateadd(m, datediff(m, 0, @date), 0)</p><p>--get last day of month </p><p>select dateadd(m, datediff(m, 0, dateadd(m, 1, @date)), -1)</p></blockquote>Till Next TimeJohn Minkjanhttp://www.blogger.com/profile/08228393663535024706noreply@blogger.com0tag:blogger.com,1999:blog-6359882683737427171.post-22389540817375915292008-10-30T01:23:00.000-07:002008-10-30T01:35:29.711-07:00SSIS Reading all the user defined variables<div>In an SSIS scripttask you are not able to loop trough all the user defined variables if you don't know the name of the variable. I created a workaround.<br /><br />Step 1:<br />create an ActiveX scripttask with the following script:'GETVARS.SCR<br /><br /><blockquote>'This function gets all the user defined variables from a SSIS package<br />'(except ado Objects) and put them in a flatfile<br />'This flatfile can be<br />used to transfer the variables to a LOG file<br />'in the database<br />'20081030<br />John Minkjan<br /><br /><br />Function Main()<br />Dim oVal<br />Dim isSystem, varType,<br />varName, varValue<br />Dim str, FileName, filesys, filetxt<br /><br />Const<br />ForReading = 1, ForWriting = 2, ForAppending = 8<br /><br />Set filesys =<br />CreateObject("Scripting.FileSystemObject")<br /><br />FileName = "c:\varfile_"&<br />DTSGlobalVariables("System::PackageName").Value &".txt"<br /><br />Set filetxt<br />= filesys.OpenTextFile(FileName, ForWriting, True)<br /><br />'Put in a header<br />filetxt.WriteLine("varName;varType;varValue")<br /><br />For each oVal in<br />DTSGlobalVariables<br />isSystem = oVal.SystemVariable<br />varType =<br />oVal.DataType<br />varName = oVal.QualifiedName<br />varValue = oVal.Value<br /><br /><br />if not isSystem then<br />if varType <> 13 then '(Ado Objects)<br /><br />if varValue = null or varValue = "" then<br />varValue = "NULL"<br />end<br />if<br /><br />filetxt.WriteLine(Cstr(varName) &";" & Cstr(varType) &<br />";" & Cstr(varValue))<br /><br />end if<br />end if<br /><br />Next<br /><br />filetxt.Close<br /><br />Main = DTSTaskExecResult_Success<br /><br />End<br />Function</blockquote><br /><br />Step 2:<br />Use a data flow to pull the flatfile back into the database for logging: <img id="BLOGGER_PHOTO_ID_5262862499903128306" style="DISPLAY: block; MARGIN: 0px auto 10px; WIDTH: 182px; CURSOR: hand; HEIGHT: 214px; TEXT-ALIGN: center" alt="" src="http://2.bp.blogspot.com/_qd3TDEvIh4Y/SQlxj1BzQvI/AAAAAAAAA4E/JIrueaFjuOs/s400/SSIS101_DFT.JPG" border="0" /></div><br />Till Next TimeJohn Minkjanhttp://www.blogger.com/profile/08228393663535024706noreply@blogger.com0tag:blogger.com,1999:blog-6359882683737427171.post-16127603398352971742008-09-05T02:38:00.000-07:002008-09-05T02:44:51.633-07:00SSIS Delete old backup filesWrote a simple scripttask for deleting old backup files:<br /><br /><blockquote><p>Public Sub Main()<br />Dim strBackupdir As String<br />Dim strBackupSubDir As<br />String<br />Dim strProjectName As String<br />Dim intDaysBack As Integer<br />strBackupdir = "..\Backup\"<br />strProjectName = "project name"<br />intDaysBack = 7</p><p><br />For Each strSubDirFound As String In Directory.GetDirectories(CStr(strBackupdir))If Mid$(strSubDirFound, Len(strBackupdir) + 1, Len(strProjectName)) = strProjectName Then<br />For Each FileFound As String In Directory.GetFiles(strSubDirFound, "*.bak")<br />If File.GetCreationTime(FileFound) < Now().AddDays(-intDaysBack) Then<br />File.Delete(FileFound)<br />End If<br />Next<br />End<br />If<br />Next<br />Dts.TaskResult = Dts.Results.Success<br />End Sub</p></blockquote><br /><br />Till Next TimeJohn Minkjanhttp://www.blogger.com/profile/08228393663535024706noreply@blogger.com0tag:blogger.com,1999:blog-6359882683737427171.post-84298666758999832472008-08-29T16:38:00.000-07:002008-08-29T16:38:01.736-07:00SSIS Directory exists<div><div><div>We needed to check if an old backupdirectory exists before we started processing it. I made a simple scripttask with a expression to the next stage:</div><br /><div><img id="BLOGGER_PHOTO_ID_5239834067417339954" style="DISPLAY: block; MARGIN: 0px auto 10px; CURSOR: hand; TEXT-ALIGN: center" alt="" src="http://2.bp.blogspot.com/_qd3TDEvIh4Y/SLehUo5mmDI/AAAAAAAAApo/0puBgkG-nJs/s400/DIR001.JPG" border="0" /></div></div><br /><br /><p>In the scripttask we us the DIR function to check if a directory exists:</p><img id="BLOGGER_PHOTO_ID_5239834894966382546" style="DISPLAY: block; MARGIN: 0px auto 10px; CURSOR: hand; TEXT-ALIGN: center" alt="" src="http://3.bp.blogspot.com/_qd3TDEvIh4Y/SLeiEzwq39I/AAAAAAAAApw/aSST5upCrgA/s400/DIR002.JPG" border="0" /><br /><br /><p></p><br />With an expression we check if can go to the next stage: </div><br /><a href="http://3.bp.blogspot.com/_qd3TDEvIh4Y/SLeilXJwWcI/AAAAAAAAAp4/eah62aKXGXA/s1600-h/DIR003.JPG"><img id="BLOGGER_PHOTO_ID_5239835454222653890" style="DISPLAY: block; MARGIN: 0px auto 10px; CURSOR: hand; TEXT-ALIGN: center" alt="" src="http://3.bp.blogspot.com/_qd3TDEvIh4Y/SLeilXJwWcI/AAAAAAAAAp4/eah62aKXGXA/s400/DIR003.JPG" border="0" /></a><br />Till Next TimeJohn Minkjanhttp://www.blogger.com/profile/08228393663535024706noreply@blogger.com0tag:blogger.com,1999:blog-6359882683737427171.post-24774486015478169652008-08-29T16:30:00.000-07:002008-08-29T16:30:00.334-07:00SSIS Carriage return in annotationOne those things you tend to forget:<br />A carriage return (enter) in SSIS is done with control + enter<br /><br />Till Next TimeJohn Minkjanhttp://www.blogger.com/profile/08228393663535024706noreply@blogger.com0tag:blogger.com,1999:blog-6359882683737427171.post-6232603148900140482008-08-16T00:56:00.000-07:002008-08-16T02:37:38.446-07:00SSIS Variables in procedures<div><div><div><div><div><div>Say you have a procedure like this on your MSSQL db which you want to call from SSIS. It has an input and output parameter which we want to use:<a href="http://1.bp.blogspot.com/_qd3TDEvIh4Y/SKaZa5Ox0iI/AAAAAAAAAkA/XKuBuklxikc/s1600-h/SSISVariables001.JPG"><img id="BLOGGER_PHOTO_ID_5235040304183235106" style="DISPLAY: block; MARGIN: 0px auto 10px; CURSOR: hand; TEXT-ALIGN: center" alt="" src="http://1.bp.blogspot.com/_qd3TDEvIh4Y/SKaZa5Ox0iI/AAAAAAAAAkA/XKuBuklxikc/s400/SSISVariables001.JPG" border="0" /></a> In SSIS we make two variables:<br /><img id="BLOGGER_PHOTO_ID_5235041555724706626" style="DISPLAY: block; MARGIN: 0px auto 10px; CURSOR: hand; TEXT-ALIGN: center" alt="" src="http://4.bp.blogspot.com/_qd3TDEvIh4Y/SKaajvlhI0I/AAAAAAAAAkI/KlxtY3Zk3BM/s400/SSISVariables002.JPG" border="0" /><br />Add an Execute SQL task to your Sequence Container<br /><a href="http://4.bp.blogspot.com/_qd3TDEvIh4Y/SKabHqolMXI/AAAAAAAAAkQ/kNb4osoizRU/s1600-h/SSISVariables003.JPG"><img id="BLOGGER_PHOTO_ID_5235042172870668658" style="DISPLAY: block; MARGIN: 0px auto 10px; CURSOR: hand; TEXT-ALIGN: center" alt="" src="http://4.bp.blogspot.com/_qd3TDEvIh4Y/SKabHqolMXI/AAAAAAAAAkQ/kNb4osoizRU/s400/SSISVariables003.JPG" border="0" /></a> In the properties set the name, description and connection.<img id="BLOGGER_PHOTO_ID_5235043138275966114" style="DISPLAY: block; MARGIN: 0px auto 10px; CURSOR: hand; TEXT-ALIGN: center" alt="" src="http://4.bp.blogspot.com/_qd3TDEvIh4Y/SKab_3DI_KI/AAAAAAAAAkY/dT8osnZMjC0/s400/SSISVariables004.JPG" border="0" /> For the SQLstatement put:<br /><br /><a href="http://1.bp.blogspot.com/_qd3TDEvIh4Y/SKae8oSqNcI/AAAAAAAAAkw/GFgHofclaUc/s1600-h/SSISVariables005.JPG"><img id="BLOGGER_PHOTO_ID_5235046381309801922" style="DISPLAY: block; MARGIN: 0px auto 10px; CURSOR: hand; TEXT-ALIGN: center" alt="" src="http://1.bp.blogspot.com/_qd3TDEvIh4Y/SKae8oSqNcI/AAAAAAAAAkw/GFgHofclaUc/s400/SSISVariables005.JPG" border="0" /></a><br />At the parameter mapping set direction and Parameter Name. The Parameter Name should a sequence starting at 0. The sequence should correpond with the order of the question marks in in your SQL statement.<br /><br /><img id="BLOGGER_PHOTO_ID_5235044913224812898" style="DISPLAY: block; MARGIN: 0px auto 10px; CURSOR: hand; TEXT-ALIGN: center" alt="" src="http://4.bp.blogspot.com/_qd3TDEvIh4Y/SKadnLP7CWI/AAAAAAAAAko/FpiFbqNQfxA/s400/SSISVariables006.JPG" border="0" /> Run your package (F5) to see if it works<br /><br /><img id="BLOGGER_PHOTO_ID_5235046961683918498" style="DISPLAY: block; MARGIN: 0px auto 10px; CURSOR: hand; TEXT-ALIGN: center" alt="" src="http://2.bp.blogspot.com/_qd3TDEvIh4Y/SKafeaWobqI/AAAAAAAAAk4/jV08ZKauG2w/s400/SSISVariables007.JPG" border="0" /><br />Till Next Time<br /><div></div></div></div></div></div></div></div>John Minkjanhttp://www.blogger.com/profile/08228393663535024706noreply@blogger.com1tag:blogger.com,1999:blog-6359882683737427171.post-17787495200541516452008-08-16T00:51:00.000-07:002008-08-16T02:38:45.962-07:00SSIS Back in town.....Wow,<br /><br />Started on a SSIS project today after being an OBIEE junkie for a year. There was a lot of stuff I knew I had done before, but how the F*&% did I do it? Natural conslusion: Let's start up this blog.<br /><br />Till Next TimeJohn Minkjanhttp://www.blogger.com/profile/08228393663535024706noreply@blogger.com0tag:blogger.com,1999:blog-6359882683737427171.post-67986642966573891202008-02-03T07:51:00.000-08:002008-08-16T00:55:30.339-07:00Welcome to SSIS101Hi, My name is John Minkjan and I'm a senior BI-consultant at <a href="http://www.ciber.nl/">CIBER</a> in the Netherlands. I reserverd a couple of ...101 blogspots. At the moment I'm mostly active on <a href="http://obiee101.blogspot.com/">OBIEE101</a>. I will start posting here as soon as my focus in back on Microsoft Sequel Server Integration Services.<br /><br />Till Next Time<br /><br />JohnJohn Minkjanhttp://www.blogger.com/profile/08228393663535024706noreply@blogger.com0