Monday, February 11, 2013

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".
Post a Comment