Deploying SSIS Packages with a Batch File

This is a handy little batch file that I have been using for years to deploy multiple SSIS packages to a SQL Server. It is pretty basic; it copies all of the *.dtsx files in a specified folder to the SQL Server.
It first removes the packages from the SQL Server, deletes the folder, and then copies the new files to the server. One limitation is that it only deletes the packages that are in the source folder. So if you have extra packages deployed, you will need to remove them manually.

This batch files uses DTUTIL so it must be run on a machine that has SSIS installed.

The syntax for running the file is: DeploySSISPackages.cmd (SQL Server name) (Folder containing packages to deploy) (Folder in MSDB to deploy to)

If any of the 3 parameters contain spaces, enclose them in quotes.

Examples:

DeploySSISPackages.cmd SQLServer001 “C:\My Projects\SSIS\ETL” “ETL SSIS”

DeploySSISPackages.cmd SQLServer001 C:\Projects\SSIS\ETL ETL

Enjoy!

@Echo Off

Echo.
Echo.
Echo SSIS Package Installation Script
Echo Written by Bob Pearson
Echo 7/6/07
Echo.

if %1a == a goto Error
if %2a == a goto Error
if %3a == a goto Error

Echo.
Echo.
Echo Deployment Server: %1
Echo -----------------------------------------------------
Echo --This will delete any %3 data mart files
Echo --on the server, and reinstall from the local machine
Echo -----------------------------------------------------
Pause
REM Goto Out


REM Remove Existing files and directory on Server
for %%f in (%2"\*.dtsx") do (
Echo Now Removing: %%~nf
dtutil /Q /SourceS %1 /SQL "\%3\\%%~nf" /Del
)

dtutil /Q /SourceS %1 /FDe "SQL;\;%3"

:Create

Echo.
Echo Preparing to create folder
Echo.
pause

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

:Out
REM copy the SSIS Packages to the server
for %%f in (%2"\*.dtsx") do (
Echo Now Copying: %%~nf
dtutil /Q /DestS %1 /Fi "%%f" /C "SQL;\%3\\%%~nf"
)


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

:Error
Echo.
Echo.
Echo Missing Servername!
Echo Syntax: Deploy SSIS Packages [servername] [Source File Path] [MSDB Deploy Folder]
Echo.
Echo.

Pause

:End

ReportingService2005 Web Service

Well, I know it’s been a while, but I am finally back after the holidays!

The next phase of our reporting project is about to take off, and I had a little down time while waiting on requirements.

So, using the ReportingService2005 web service, I created a report deployment application in C#.

It allows me to select the directory of my RDL/RDS files, and then select my report server and any folder on that server.

Then it deploys the selected reports to the report server with the one button click.

I can then change the report server, and deploy the same reports to that server. This is helpful if you have multiple systems that should stay in sync. Using dynamic web service connections allows me to change the target server within code.


Working with the web service was rather straightforward, and allowed me to do everything I needed to do as far as deploying reports and data sources.
However, what I wanted to write about was a catch that had me stumped for a few minutes.

When you use the CreateReport method of the web service, the data sources are not linked to the server data sources. So what I did was use the GetItemDataSources method to get the report data sources, built a new data source in code, representing the server data source, and then used the SetItemDataSources method to update the report data sources.

DataSource[] dataSources = rs.GetItemDataSources(TargetFolder + "/" + ReportName);

foreach (DataSource ds in dataSources)
{
if (ds.Item.GetType() == typeof(InvalidDataSourceReference))
{
string dsName = ds.Name.ToString();

DataSource serverDS = new DataSource();
DataSourceReference serverDSRef = new DataSourceReference();
serverDSRef.Reference = "/Data Sources/" + dsName;
serverDS.Item = serverDSRef;
serverDS.Name = dsName;
DataSource[] serverDataSources = new DataSource[] { serverDS };
rs.SetItemDataSources(TargetFolder + "/" + ReportName, serverDataSources);
}
}

This nicely updated the report data sources to use the server shared data sources.

All in all, the 1½ days I spent writing this app were well worth it. I learned about the deployment methods of the web service, and kept my coding skills up, plus I have a nice deployment utility for reports!

peace

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

XML/RDL Editor

I am about 60% done with a project to help me edit RDL files. For those of you unfamiliar, these are the XML files in SSRS that control everything about the report.

My goal in this project is to be able to drag and drop nodes between 2 list views, delete nodes, copy/paste nodes, etc.

I am doing this because I have a need to copy standard parts of reports between almost 100 reports now. And that number is only going to grow in the future.

My question: is there already a tool out there that will do this? I searched before developing, but did not find a decent one.

And, if I did finish this, and thought about distributing it, would anyone else out there use it?

On another subject, last Saturday, my project manager’s wife was hit and killed by a drunk driver. She left behind Jim and their 3 sons. (http://voluntaryredneck.mu.nu/archives/244552.php) Please keep them in your thoughts and prayers.

Peace

BobP