Running an SSIS Package Programmatically From .NET Code

Posted on : 19 Feb, 05:00 PM


A few weeks ago I was given the task 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. If you’re not familiar with SQL Server Integration Services (SSIS), 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.

I started by using the LoadPackage method of the Application object. This returns an instance of 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 or access all the resources required by that package. Even when I impersonated a user account with sufficient privileges to run the package, I found out that the new thread still uses the ASP.NET account.

Then I tried using DTEXEC.EXE which is 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 right 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&rdquo ] .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 some 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.

Be aware that the job constructs a command line for each step inside it. If your job just runs a package then 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 there are no parameters assigned to the package. So you need to append the command line with the name and value of the variable in the following syntax:

/SET “Package.Variable s[ 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.

Now to reflect your changes on the SQL job, you just 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 using an SQL agent job isn’t the easiest way, but it provides more flexibility in terms of 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.

Related Posts

No related posts found
;

Running an SSIS Package Programmatically From .NET Code

Posted on : 19 Feb, 05:00 PM


A few weeks ago I was given the task 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. If you’re not familiar with SQL Server Integration Services (SSIS), 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.

I started by using the LoadPackage method of the Application object. This returns an instance of 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 or access all the resources required by that package. Even when I impersonated a user account with sufficient privileges to run the package, I found out that the new thread still uses the ASP.NET account.

Then I tried using DTEXEC.EXE which is 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 right 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&rdquo ] .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 some 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.

Be aware that the job constructs a command line for each step inside it. If your job just runs a package then 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 there are no parameters assigned to the package. So you need to append the command line with the name and value of the variable in the following syntax:

/SET “Package.Variable s[ 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.

Now to reflect your changes on the SQL job, you just 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 using an SQL agent job isn’t the easiest way, but it provides more flexibility in terms of 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.

Related Posts

No related posts found
;

footer-img

Integrant’s Vision is to transform the software development lifecycle through predictable results.

Subscribe

To get our newsletter & stay updated

© 2023 Integrant, Inc. All Rights Reserved | Privacy