Anushka's Tech Notes

Executing SQL Agent Job with Proxy Account

2012-05-23T23:03:00.001-07:00

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.

Pass Multi Value Parameter to SSRS Report Using URL

2012-05-23T22:55:00.001-07:00

       I have published a post that explaining about Passing Parameters to SSRS Report using URL few months ago. That post solves one problem but still have some other problems. When we going to pass a multiple value parameter then that is stuck. The reason is we cannot pass 1-dimensional array through an URL.

      When we want to pass dynamically changing multiple values to SSRS report through URL try the following way.

       We have our original report and target report. The error says exactly cannot pass a string array. So I have to pass values as one string. What I’m going to do is pass all the values as string to temporary parameter in the target report and then split those values.

In Original Report

         I want to pass Status (which is a multi-valued parameter) parameter’s values to my target report. Using Join I can pass the parameter values separated by commas as one string. That’s the only thing we should do in our original report. Don’t bother about param1. For the time being use it as following.

=”javascript:void(window.open(‘<ReportServerURL>?<ReportURL>&rs:Command=Render&param1=” & Replace(Join(Parameters!Status.Value,”,”),”&”,”%2526″) & “,’_blank’))”

(Above code is clearly described in Passing Parameters to SSRS Report using URL)

In Target Report

         Then we comes to target report. I have to set those values to parameter called MonthlyStatus in my target report. Before that we need to add new parameter for target report. I named it as ‘param1‘ ( I think now you can get the idea about param1 which I used in above expression. Instead of directly passing to MonthlyStatus I use param1). param1 is a new parameter, which haven’t any available values or any default values. Set parma1 as a hidden parameter.(Because no one want to see that )

       Then we come to the simple part. Now we have all the values separated by commas in param1 and need to split each value and set to related parameter. We can do this by editing the default value expression of the related parameter (in this case MonthlyStatus)

Make sure param1 is placed above than MonthlyStatus of the parameter order. Because MonthlyStatus using param1 value as default value.

Note: You cannot see the report preview in BIDS. But after deploying the report you can see that multi-value parameter’s values are passing to target report.

There may be some better solutions for this ! If then please leave as comments.

Web Applications Development with Microsoft .NET Framework 4

2012-05-21T03:17:00.002-07:00

Hello All,

I would happy to inform that i have successfully completed the Web Applications Development with Microsoft .NET Framework 4. (exam no 70-515). For more details about this exam go to following link.

Web Applications Development with Microsoft .NET Framework 4

Set Default Value of PerformancePoint Filter

2012-05-21T03:16:00.004-07:00

Basically default value of a PerformancePoint filter is the default value of the relevant dimension. Following shows how to set default value of SSAS dimension. In my example I use dimTime dimension. I want to create a filter using dimTime dimension.

I’m going to use YearMonth member of dimTime dimension for the filter. So I set default value of YearMonth as shown in above figure.

Set Default Value Dynamically

Moreover we can use MDX expression to set the default value dynamically. Using that we can change the default value according to purpose.

Above MDX expression use to set the current month as default value in my scenario.
Now when I create PerformancePoint filter, this value becomes the default value.!!

Unpivot In SSIS

2012-05-21T03:13:00.002-07:00

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

2012-05-21T03:01:00.000-07:00

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 .

Grid View Data Bind with an Image field

2012-05-21T02:57:00.002-07:00

Today I’m going to discuss how to bind data to Grid View component that has image field. ( using C#)
Let’s do it step by step.

Step 1 – Create the Grid View component.

I’m using only three columns(File Name, Status , ImageStatus) Following shows the Source view. This can be done by Design view easily.

<asp:GridView ID=”GridView1″ runat=”server” Width=”500px” PageSize=”12″ >
<Columns>
<asp:BoundField HeaderText=”File Name” DataField=”FileName”
HeaderStyle-Width=”300px” ></asp:BoundField>
<asp:BoundField HeaderText=”Status DataField=”Status” > </asp:BoundField>
<asp:ImageField HeaderText=”ImageStatus” DataImageUrlField=”ImageUrl”> </asp:ImageField>
</Columns>
</asp:GridView>

Step 2 — Get required data to data table
I’m not going to explain this step. I assume that GetTable() method do data retrieving part and store them to fileInfo DataTable. This DataTable contains FileName and Staus columns.

DataTable fileInfo = GetTable();

Step 3 – Create DataSet and Add new ImageUrl column.
This ImageUrl contains appropriate image for Status column.

gridSet = new DataSet();
gridSet.Tables.Add(fileInfo);
gridSet.Tables[0].Columns.Add(“ImageUrl”, typeof(String));

Step 4 – Add the Image URL to ImageUrl column
Assume that Status column have two values(R-Readable and U-Unreadable) . Now we are going to map the image suitable for status of the file.

for (int i = 0; i < gridSet.Tables[0].Rows.Count; i++)
{

switch (gridSet.Tables[0].Rows[i].Field<Char>(“Status”))

{

case ‘R’:

gridSet.Tables[0].Rows[i]["ImageUrl"] = “\\Server1\Files\Images\ readable.png”;

break;

case ‘U’:

gridSet.Tables[0].Rows[i]["ImageUrl"] = “\\Server1\Files\Images\unreadable.png”;

break;

}
}

Step 5 – Bind the Data to Grid View

this.GridView1.DataSource = gridSet.Tables[0];
this.GridView1.DataBind();

That’s all. See the following grid view sample. Post your comments !

Date Validation Using Range Validator

2012-05-21T02:39:00.001-07:00

There may be instances that you want to restrict some range of dates in calendar extender. In this instance I’m restricting the future dates. (Current date is the maximum date !) I have Calendar Extender and TextBox(DateBox) to select the date. When the user is selecting some date that exceeding current date, I want to give a restrict message.

Insert the Range Validator component to the page.

Set properties as shown in following. Most important properties are

ControlToValidate – What is the control we want to compare with Range Validator (DateBox)
Type – Since we are going to validate Date, this should be Date type

// set the default date in date box

DateBox.Text = DateTime.Today.ToShortDateString();
RangeValidator1.MaximumValue = DateTime.Today.ToShortDateString();

RangeValidator1.Validate();

if (RangeValidator1.IsValid)
// your code when date is valid.
else
// your code when date is exceeding today.
Set the Minimum Value of the Range Validator in properties window or in page load event. Done!!

When the user is selecting a invalid date then RangeValidator1 is displaying and your custom code which is in commented area is working. Post your comments!

Passing Parameters to Threads and Get Return Value

2012-05-21T02:12:00.001-07:00

When you using threads, in most cases you need to pass parameters or you need to get return value or you need to do both.

Following example demonstrates this scenario clearly in c#.

There’s a method called ExecutePackage that we want to start using our thread and should provide a parameter(String).

private String ExecutePackage(String pName)

{

……

return status;

}

You can pass parameter and get the return value using following thread.

using System.Threading;

Thread thr;

thr = new Thread(delegate()

{

result = this.ExecutePackage(packageName);

});

thr.Start();

It’s done !!!

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

2012-05-21T02:11:00.000-07:00

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.

Update Progress is not working properly

2012-05-21T02:08:00.000-07:00

Update Progress is used to display animated text or some message to users, while target Update Panel is working. But some cases this isn’t happening as we want.

We can use Ajax toolkit component as alternative.
First you have to download AjaxToolkit latest release and add it as reference into your project.

Add a assembly to your config file.
Then instead of <asp:UpdateProgress> tag you can use <ajaxToolkit:UpdatePanelAnimationExtender> tag and target control id should be your update panel id.

Inside <OnUpdating> tag you can assign a task that will working while processing and inside <OnUpdated> tag you can assign task that should work after processed.

In following example code I have used a label to display a message to user.

Dynamic SQL Command in ADO NET Source

2012-05-21T02:04:00.002-07:00

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 .

Passing Parameters to SSRS Report using URL

2012-05-19T23:35:00.000-07:00

Parameters are very useful when you come to report customization. Its going to more useful, if those parameters can set before loading the report. you can do it easily using URL. With SharePoint Integrated mode, this is little bit confused.

There are two ways to do this task.

1] using Report Viewer (this is the default way when you configured reporting service to SharePoint Integrated mode)

2] using HTML viewer (This is the most interesting way in SharePoint Integrated mode. I’m going to stick to this way mostly)

1.

When you passing parameters to report viewer you have to follow this way.
ReportViewer URL&rp:parameter1=Value1&rp:parameter2=value2
nothing can be wrong with this except parameters displaying in right hand side pane, which i do not like.

2.

You can use the HTML viewer to view the reports .
here is the way to view the report in HTML viewer.

Your Report Server Virtual Directory ? Full Path of Report (where report is deployed in SharePoint site)

Example — > http://Server1/ReportServer?http://server1/sites/Test/Reports/YearlyTrend.rdl

Using above example you can view the report in HTML viewer. When it comes to parameter passing just append the parameter names and values to end of the URL with rs:command=render.

http://Server1/ReportServer?http://server1/sites/Test/Reports/YearlyTrend.rdl&rs:command=render&parameter1=Value1&parameter2=value2

You can pass multiple parameters using above format. Target report’s parameters affected by values that you passed in URL

Further Modifications …

This can be used in SSRS Report drill down. When you want to drill down report there is an option to set URL.

Simply can set the URL to target report using expression with available parameters. Following expression can use to pop up the target report in separate window.

=”javascript:void(window.open(‘http://Server1/ReportServer?http://server1/sites/Test/Reports/YearlyTrend.rdl&rs:command=render&parameter1=Value1&parameter2=value2‘,’_blank’))”

After all most common seen problem is ‘&’ (ampersand) symbol cannot pass in URL. because most of the time parameter values contain ‘&’ symbol. See the following example.

=”javascript:void(window.open(‘http://Server1/ReportServer?http://server1/sites/Test/Reports/YearlyTrend.rdl&ReportParameter1=” & Parameters!ReportParameter1.value & ” ‘,’_blank’))”

Parameters!ReportParameter1.value containing a ‘&’ symbol but this cannot be passed. you can Replace the ‘&’ symbol with ‘%2526′ ( which is the url encoding of ‘&’)

=”javascript:void(window.open(‘http://Server1/ReportServer?http://server1/sites/Test/Reports/YearlyTrend.rdl&ReportParameter1=” & Replace(Parameters!ReportParameter1.value,”&”,”%2526″) & ” ‘,’_blank ‘))”

Post any problems that you have regarding this. Any thoughts will be welcomed !

Get SSIS package Execution Status

2012-05-19T23:01:00.000-07:00

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 ?

2012-05-19T21:51:00.003-07:00

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 .