A few weeks ago, I was tasked to create a webpage that runs an SSIS package. This is necessary when users want to run the SSIS package at any time without going to the DB admin. 

It’s also required when users run the package often and would prefer the convenience of simply clicking a button on a web page. Suppose you’re not familiar with SQL Server Integration Services (SSIS). 

In that case, it’s a platform from Microsoft used to help you extract, transform, and load data from multiple data sources to your data warehouse or transactional database.

Searching the internet for solutions, I found a few approaches, but none of them worked perfectly for me.

The Initial Steps I Took to Execute SSIS Package Programmatically

I started by using the “Load Page” method of the Application object. This returns an instance of the Package class, but it didn’t work because there was a lack of security permissions. The default ASP.NET user does not have enough privileges to execute the package programmatically or access all the resources required by that SSIS package. 

Even when impersonating a user account with sufficient privileges to run the SSIS package, I discovered that the new thread still uses the ASP.NET account.

Then, I tried using DTEXEC.EXE, a command line utility to configure and execute SSIS packages. But, this also failed with the permission issue, like the first method.

After that, I tried to run it by calling a SQL agent job. This was more complicated than the previous two attempts, but it worked for me.

I followed detailed steps to do this one:

1– The first thing you need to do is create an SQL agent job and configure it to call your package using the correct user account. I will write a more detailed post about this later.

2– Then add references to the following assemblies: – Microsoft.SqlServer.Smo – Microsoft.SqlServer.ConnectionInfo

3– Now, you need to define an instance of the ServerConnection object to connect to the SQL server. The constructor of this object has more than one overload. Here, I will use the one that takes an SQL connection since I already have a connection string saved in the web.config file.

The code should look like this:

[sourcecode language=’c-sharp’]

//get the connection string from the web.config file string connectionString = ConfigurationManager.ConnectionStrings[“MyDatabase”].ConnectionString;

//create a sqlconnection SqlConnection sqlConnection = new SqlConnection(connectionString);

//create server connection that connects to the SQL server where the job was created 

ServerConnection connection = new ServerConnection(sqlConnection);

[/sourcecode]

4– After that, we’ll create an instance of a server object, passing the server connection instance we just created to its constructor. Then, we’ll create a JobServer object, which will be used to access the agent job you created. It should look like this:

[sourcecode language=’c-sharp’]

Server server = new Server(connection);

//create an instance of the job server to access the jobs JobServer jobServer = server.JobServer;

//get the job by its name Job job = jobServer.Jobs[“SQLAgentJobName”];

[/sourcecode]

5– Now you have access to the job, and you can run it by calling the Start method of the job:

[sourcecode language=’c-sharp’]

job.Start();

[/sourcecode]

Sometimes, you might need to send parameters to the SSIS package, like a specific date or flag. Unfortunately, I couldn’t find an easy way to do that. However, there is a workaround.

The job constructs a command line for each step inside it. If your job runs a package, it will only have one step. The job will execute that command line when you start the job. What we will do here is manipulate that command line to include the parameters. The command line will look something like this:

/SQL “JobName” /SERVER “ServerName” /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF /REPORTING E

In the command above, no parameters are assigned to the SSIS package. So you need to append the command line with the name and value of the variable in the following syntax:

/SET “Package.Variables[VariableName].Properties[Value]”;”value”

For example, if you want to set a variable called LoadDate to “1/1/2010” the command will look like the following:

/SQL “FirstJob” /SERVER “ServerName” /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF /REPORTING E /SET “Package.Variables[LoadDate].Properties[Value]”;”1/1/2010″

Please note that most of the time, the SQL job will be configured previously to send the variable, and the command line will look like the one above, so you only need to replace the value in the command line.

To reflect your changes on the SQL job, you need to set the command property for that job step and call the Alter() method. But note that your changes will be saved permanently to the job on the server:

[sourcecode language=’c-sharp’]

job.JobSteps[0].Command = newCommand; job.JobSteps[0].Alter();

[/sourcecode]

Conclusion

Running an SSIS package using an SQL agent job isn’t the easiest way, but it provides more flexibility regarding the user account running the process. So consider using it when the user account that runs the application doesn’t have enough privileges to run the package.

If you need expert guidance on how to execute an SSIS package programmatically, you can contact us for a free consultation.