Executing SQL Agent Job with Proxy Account

       When we going to execute a SQL Agent job, most of the times we do not bother about execution user. By default SQL Agent job is executing under the SQL Agent service account. Most probably this account  can be a high privileged user. (this is the account which is Log on account for SQL Agent service)
http://technet.microsoft.com/en-us/library/ms345578.aspx

Which means SQL Agent job is executing with administrative privilages. That is not a safe way because some average user can execute dangerous operating system commands and etc. So we need a safe way to execute SQL Agent job.

The solution is Proxy account!

Basically this is act as a proxy for a perticular user. In order to use proxy account we need to store set of credentials. If we store some average user’s credentials for this, then SQL Agent job is executing with those privilages. That’s not harm at all.

This is the way to use proxy account for executing a SQL Agent job.

1.Create the credential – Use an average user for credentials. (I used an average user named TestUser)

2.Create a proxy that references the credential

3. Assign the Principle for the proxy. ( TestUser is in SQLAgentUser role , so we need to add this role as a principle)

You can add user to the SQLAgentUser role as follows. (SQLAgentUser role is the lowest permission group to execute a SQL Agent job).

4.Assign the run as value in SQL Agent job step to the proxy.

Now execute the SQL Agent job and see the history. You can clearly see job is executed by that average user we mentioned in credentials.

Unpivot In SSIS

         Using Unpivot component in SSIS is a very easy task.  Following is the source table which I extracting from a excel source. It shows Application Software categorization and I want to get those categories (Business,Communication and Graphics) in to rows.

Business	Communication	Graphics
School Administration Software	Microsoft Email Software	3D Graphics Software
Time Tracking Software	BitTorrent Clients	Image Viewers
Cost Analysis Software	WordPress

Following figure shows the Data Flow task of the ssis package.

Following is how we have to configure the Unpivot component. In here Destination Column name should be same for every input column.  Other destination column name is the pivot key value column name.  You can see these column headers in out put.

This is how we have the out put after the unpivot operation.

Category	Application Software
Business	School Administration Software
Communication	Microsoft Email Software
Graphics	3D Graphics Software
Business	Time Tracking Software
Communication	BitTorrent Clients
Graphics	Image Viewers
Business	Cost Analysis Software
Communication	WordPress

Make any comments about this post.

Avoid Null Values From Excel File with SSIS

     Working with Excel is bit mess in Integration Services.When we extracting data from Excel File sometimes struggle with Null values.

Assume that you want to get the data from selected area of an Excel file excluding Null values. Ex:-  I need to get the data only from A8:C20 range in following excel file. I don’t want to get the first 7 rows of the excel file because it’s just a topic.

This can be done directly through the Excel Source component. There is a property named OpenRowSet in Excel Source component. After configuring the Excel Source component you can only see the sheet name.
I just change the OpenRowset property to Sheet1$A8:C20 . Now it’s only extracting  data from selected area.


 

 If you want this value can be given as a variable. It can be done easily by changing the AccessMode property .

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION ???

When this error occurred to me, first what I did was check the Begin Transaction. But unfortunately there were no problems in those components. But every time package execution was failed.

In this case we want to do some transaction using a connection and commit or rollback that transaction using same connection but in different task. So it’s essential to keep the same connection to do that operation.
We can set this using Retain Same Connection property of the connection. Set that property to True in the properties window of the connection.

 This will solve the above problem.

Dynamic SQL Command in ADO NET Source

In ADO NET Source there is no place to insert dynamic SQL command. (OLEDB Source there is SQL command from variable to insert dynamic SQL command).
As shows in following figure there are only two options in ADO NET Source.

 
 What you have to do is just go back to control flow and check the properties of Data Flow task that consist ADO NET Source.
 
Once you get the Expression Editor window you can set the [your ADO NET Source Name][SQL Command] as you want.

 

You can set a dynamic SQL command to a variable in earlier step. Then use that variable in Expression builder as shown below.

 

 That’s it!  It works fine .

Get SSIS package Execution Status

When SSIS Package is executing by SQL Agent Job…
.
.
.
.
DTSExecResult result;
package = app.LoadPackage(packagePath, null);
try
{
package.Execute();
result = package.ExecutionResult;
}
.
.
.
.
Yes. It works fine. But in this case we are going to execute our SSIS package using SQL Server Agent job. Therefore above code is useless.

What I did in this case is, use sysjobhistory table. Each and every time start the SQL Agent job , one entry is stored in sysjobhistory table.  Just need to get the status from that entry.

You can find all the job history in sysjobhistory table while the job details stored in sysjobs table.  You need to connect SQL server with sa account to view this.

Following figure shows how to reach those tables.

Now you can easily get the execution status (run_status)  from sysjobhistory table by stored procedure.  Run_Status containing numbers 0 to 4.  Following stored procedure get the status of particular job.

That’s it.
But make sure that SSIS package is completed its execution, before you get the status.

Stuck with 64 bit ?

Execute SSIS packages with 32 bit configuration in a 64 bit environment
If you,

• Working on 64 bit environment
• Executing SSIS packages using a separate application
• Using two separate server machines for SharePoint and SQL servers

Then definitely you have struggled with this issue.

If you have Excel Connection Manger in your SSIS package or  extracting data from db2 source then your SSIS package might failed without any reason. It’s because 64 bit version is not compatible with these components.
What I did as solution is, execute the SSIS packages using SQL Server Agent jobs .

How to do it ?

• Connect to SQL server with sa privileges.
• Create a new job

 

• Then in steps you can browse your SSIS package in the file system

•  The important point is to tick the Use 32 bit runtime option in Execution options tab.

That’s all about creating a SQL Server Agent job. It will execute your SSIS package in 32 bit mode.
But now how to start this job ?
Simply write a stored procedure to start this job. Following figure shows how to do it.

That’s all !
Now just you need to do is execute this stored procedure using your code with job name as a parameter.
I will talk about how to get the execution status of this SSIS package in next post .