SSIS: Deploying A Package To A Remote SQL Server (And Testing It)

The blog post covers deploying a package onto a server (and then running it). I'm going to use the test package I created in the blog post "Exporting Query Results To A Flat File" as it's as good as any and doesn't rely on anything tricky. I also know that the domain user who is running the Server has permissions to see the G drive I'm trying to write to so I can eliminate that as a potential error!

Open the project in BIDS and right-click the project in "Solution Explorer" (on the right);

SSIS: Solution Properties

Select "Properties" at the bottom;

SSIS: Solution Property Pages

Select "Deploy (BIDS Helper)" in the panel on the right and then change the "DeploymentType" to "SqlServerDestination";

SSIS: Changed Deployment Settings

While it defaults to "localhost" I always tend to pick a different machine to deploy to if possible or specify the name of the machine in full - I just worry that "localhost" could change at any time (you could open and run it on a different machine) whereas if I specify a machine it will remain constant.

When you've entered the server you with to use click "OK".

Right-click the project in the "Solution Explorer" view again;

SSIS: Deploying A Project

And this time select "Deploy";

SSIS: Output Showing A Successful Deploy

Now the package has been successfully deployed to the SSIS Server.

To check it's successfully worked open up SQL Server Management Studio;

SQL Server Management Studio: Connect to Server

Make sure you select "Integration Services" in the "Server type" drop down and click "Connect".

Go to the "Object Explorer" (on the left) and expand the nodes under "Stored Packages" until you find the package you've just deployed;

Management Studio: Deployed Package

Now right-click the deployed package;

Management Studio: Package Options

Select "Run Package";

Management Studio: Execute Package Utility

Click "Execute";

Management Studio: Package Execution Progress

As you'll see I've expanded the window so you can see the complete set of messages for executing this package. This will appear over time as the package executes so could take anything from a few seconds to a few hours to shows you everything (depending on the complexity and volume of the SQL you're running) but it will complete (either with an error or success).

Click "Close".