dtutil : Importing SSIS packages via batch scripting

Jan 28
2011

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).

Windows command shell fun

Aug 17
2009

(only for slightly bizarre/masochistic senses of fun)

I’ve been out on a client site for a month or two and haven’t had time to update a lot. Now that I’m back in the office I decided to ease myself into things slowly by going through my usual backup routine and incidentally tidying up my music collection.

I had discovered over the last few months that bits of my collection disappeared when I imported into iTunes, because they were originally ripped into ogg vorbis format – iTunes doesn’t understand ogg (at least, not by default), and because I’ve got around 23GB of music, I hadn’t noticed a few hundred missing songs until now.

Converting them in place was fairly simple with CDex, but once I’d done this I needed to move, check, then delete the (now) extraneous ogg files. So, what command could I issue to search through a list of directories, find files of a particular extension and move them to a holding pen? This isn’t especially straight forward on the windows command line, but this is the batch file that eventually worked for me :

@echo off
if exist oggfile.list del oggfile.list
dir /s /b *.ogg >> oggfile.list
for /f "delims=" %%a in (oggfile.list) do (move "%%a" temp)

You can issue these as separate commands if you want, all that changes is the “%%a” becomes “%a” instead (I did it that way myself to begin with until I realised I wanted to find other, errant non-mp3 files as well).

If you want something more generic, being able to pass arguments to batch scripts means you can do this with files of any particular extension :

@echo off
if "%1"=="" goto :EOF
echo Finding and moving .%1 files...
if exist %1file.list del %1file.list
dir /s /b *.%1 >> %1file.list
for /f "delims=" %%a in (%1file.list) do (move "%%a" temp)

Assuming you save this as finder.bat (there already is a find command), then to move all ogg files in a particular tree you would simply call :

C:\finder ogg

I could probably go the whole hog and make the “temp” directory user-definable as well, but I think I’ve used the script a grand total of 3 times now and probably won’t touch it again.

It was fun to go old-school for a while. Dos batch commands are a lot more powerful than people realise, but they are somewhat arcane.

I recommend Rob van der Woude’s scripting pages for anyone wanting to delve into this more deeply, I’ve used his reference pages quite a lot in the past and usually been able to solve the problem at hand. He also covers a lot of other scripting languages besides windows batch script.

Visit Our Friends!

A few highly recommended friends...

Pages List

General info about this blog...