Running an SSIS Package from an Executable File

Our QA department does rigorous testing on our development (web, database, etc.) before they clear it for production. That’s how it should be.

I work for a life insurance company. As such, our websites have Premium Rate calculators where agents and prospective customers can enter certain information (age, gender, etc.) and have returned a chart of premiums based on different payment schedules and plans. All of this data, as well as other data for our websites, is stored in SQL Server databases. I was responsible for building the databases and designing the SSIS packages that refresh and populate the database tables.

When we offer new insurance products, our Quality Assurance department needs to test the new plans and rates that come out of the calculator. They’ll load the new data into a development partition on our AS/400 (where our policy administration software is housed), and then ask me to transfer it to the SQL Server database so they can test them out on the websites. I had created a small SSIS package to transfer the dozen or so tables from the AS/400 to SQL Server. Problem was, however, that it was up to me to run it every time QA needed the rates refreshed.

I didn’t want to install Management Studio or BIDS on each QA person’s computer so that they could run a job or launch an SSIS package each time they did their testing. And I certainly didn’t want to be the one who had to run it each time.

I came up with the perfect solution – let the QA people just click an executable that launches the SSIS package! One problem – I had no idea where to begin. So, with a little bit of Googling and Binging I pieced together a solution. I apologize that I don’t remember the site that I eventually found, so if this code is yours please let me know and I’ll gladly make that known.

Step 1. Open up Visual Studio 2005 and create a new VB.NET Windows project. I deleted Form1 and added a new module. I started the coding off by importing the System.Data and System.Data.SqlClient Namespaces.

Step 2. I then defined my connection, command, and parameters:

Step 3. I instantiated new a connection to my SQL Server, using the msdb database, because that’s
where the stored procedure sp_start_job I’m calling is located, and I also instantiated a new SQL command:

Step 4. I then set up my return value:

Step 5. Next, I added another parameter that included the name of the SQL Server Agent Job that runs the SSIS package:

Step 6. Finally, I open the connection and run the command. My SSIS package has an Email task that notifies the user once the SSIS package has finished successfully or unsuccessfully. A message box pops up letting the user know if the job is running or if there was a problem.

And that’s it! I compiled the VB.Net project and installed it on the QA Tester’s computer. In order to run this .exe successfully, the user needs to have the proper database permissions. However, the user doesn’t need to have SSMS, SSIS, or anything else related to SQL Server in order to run this program. QA is happy that they don’t have to ask me or anyone else to reload the tables, and I’m happy that they don’t ask me to reload the tables anymore!

Since this is my first “technical” blog, comments, criticism, and tips on how to improve the blog and even the VB.NET program are more than welcome.

Advertisements

About Gill Rowley

I live in Madison suburb, working as a Senior Consultant for Talavant. I like hunting, fishing, working out, my boat, playing with my rescued bull terrier Lola, and gourmet cooking. Oh, yeah, and I play men's fastpitch softball.
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s