Deploying SSIS Packages

Today I am going to talk about easy SSIS package deployment.

I generally am working with multiple data marts, and separate my projects by data mart. However, if I have the same package name in 2 projects, and do a default deploy from SSIS, those files overwrite each other.

You can create subfolders under MSDB in SQL Management Studio when logged into the SSIS instance, however, you have to manually deploy to these sub folders.

Because I am lazy, I wrote a quick batch file to move a project from a development machine to the SSIS machine. The following batch file takes 1 argument, Servername, and copies all of the files from a project to that server. The project path is hard coded in the batch file, so if you want to deploy multiple SSIS projects, just copy the batch file and change the path.

---

@Echo Off

Echo.
Echo.
Echo Test ETL SSIS Package Installation Script
Echo Written by BobP
Echo 7/6/07
Echo.

if %1a == a goto Error

Echo.
Echo.
Echo -----------------------------------------------------
Echo --This will delete any Test ETL files
Echo --on the server, and reinstall from the local machine
Echo -----------------------------------------------------
Pause

REM Remove Existing files and directory on Server
for %%f in (C:\Projects\TestETL\TestETL\bin\*.dtsx) do dtutil /Q /SourceS %1 /SQL "\TestETL\\"%%~nf /Del
dtutil /Q /SourceS %1 /FDe "SQL;\;TestETL"

Echo.
Echo Preparing to create folder
Echo.
pause

REM Create the Directory
dtutil /Q /SourceS %1 /FC "SQL;\;TestETL"
if errorlevel 1 goto End
Echo.
Echo Preparing to Copy Files to Server
Echo.
pause


REM copy the all project packages to the server
for %%f in (C:\Projects\TestETL\TestETL\bin\*.dtsx) do dtutil /Q /DestS %1 /Fi %%f /C "SQL;\TestETL\\"%%~nf

Echo.
Echo.
Echo Installation Complete!
Echo.
Echo.
Pause
Goto End

:Error
Echo.
Echo.
Echo Missing Servername!
Echo Syntax: Deploy [targetservername]
Echo.
Echo.

Pause

:End

---

I have several copies of this, one for each of my projects, on my desktop. When I want to deploy, all I have to do is double click.

peace

BobP