Ad hoc Geek

Just a quick and simple task here. I got fed up of manually importing dtsx files into our SSIS file store so I looked into a scripting solution. This relies entirely on dtutil which is installed with SSIS.

@echo off
 
set root=My SSIS Packages
set server=SSIS01
 
echo Importing Some Foo Packages
 
set tmpFolder=Foo
dtutil /SourceServer %server%  /FExists SQL;"\%root%\%tmpFolder%"
if errorlevel=1 dtutil /SourceServer %server% /FCreate SQL;"\%root_folder%";"%tmpFolder%"
 
dtutil /File "Foo1.dtsx" /Encrypt SQL;"\%root%\%tmpFolder%\Foo1";5 /DestServer %server% /Q
...

This is pretty simple – it checks whether there’s a “Foo” folder under the root path “My SSIS Packages” on the destination SSIS instance, if not it creates the folder and then uploads the package. The only thing to note here is that I’m using /Encrypt rather than /Copy, this is the same as manually uploading and setting the protection level to “Rely on server storage and roles for access control” (essential if you’re uploading packages which will be scheduled to run under a different account to your development account).