Tuesday, 18 October 2011

SSIS : Continue Looping after a child task Failure

It is a very common scenario that we want the loop to continue even in case of a child task failing. We can implement this in the following manner.

1.       Create your loop and configure it

2.       Add a Sequence Container to that Loop

3.       Set the Sequence Container’s MaximumErrorCount Property To 0 (Zero)

4.       Create an OnError Event Handler for the Sequence Container

(You can create a custom error logging mechanism here)

5.       Set the system variable Propagate to False when in the Event Handler Page

You can see the system variables by clicking the gray button on the Variables Window

6.       Add your Child Task in the Sequence Container

In My case I have added a script task that is throwing a dummy Exception using
Throw New ApplicationException("Test Exception")

7.       Execute the package and done. The Script task will fail  but the Sequence container and the Loop Container will execute successfully

Monday, 17 October 2011

SSRS : Passing Parameters to a Report and control Its Display using URL

                We can use SSRS Reports to be displayed as images directly in our own web page and we can also perform an export without even displaying the report or embed the report in our own Custom UI. This is possible by altering the Url for the Report. We can use the ‘rc:’ and  ‘rs:’ parameters of SSRS.
                These parameters help us to customize the way the reports are rendered.

This parameter prefix means Report Client Command. It is used to alter the way the report is rendered. The most useful parameters included in this are:
1)      Parameters
·    It shows or hides the parameters area of the toolbar. It take the values as True, False or Collapsed.
·   True Means that the Parameters section is displayed. It is the default Value.
·   False Means that the Parameters section is displayed and the user cannot toggle it to be displayed.
·   Collapsed Means that the parameters are hidden but can be toggled by the user.

2)      Toolbar
·   It shows or hides the toolbar. It takes the values as either True or False.
·   True means that the toolbar is displayed. It is the default Value.
·   False means that the toolbar is displayed.

3)       Section
·   It specifies the page number of the report to be displayed.
·   The default value is 1.
·   If its value is less than 1 then page no 1 is displayed.
·   If its value is greater than total pages in report then the last page is displayed.
4)      Stylesheet
·   It specifies the CSS that has to be applied to the viewer
5)      Zoom
·   It specified the zooming factor for the report

This parameter prefix means Report Server Command. It is used to alter the way the report is executed. The most useful parameters included in this are:
1)      Format
·         It specifies the format in which the report has to be exported.
·         The report is not rendered instead it is given as a downloadable file in the requested format.
·         The valid formats are :
o   HTML3.2
o   HTML4.0
o   WORD
o   CSV
o   PDF
o   XML
2)      Command
·         It specifies the action to apply to the path, such as render a report or list all of the items in a folder. Defaults to Render for reports.
·         It takes the following values
o   Render (Default)
o   ListChildren
o   GetResourceContents
o   GetDataSourceContents
3)      Snapshot
·         It specifies the snapshot of the report to be rendered

If the report has parameters, these parameters can be passed by specifying the parameters in the URL as

Note *
The Multivalued parameters are passed as comma separated values.

The Date type parameters must be passed as MM/dd/yyyy

If the rc: parameters is set to false then the parameters values must be passed in the URL or the default values must be specified else it will throw an exception

SSIS : Using Proxy Account to execute a Package

Sometimes we need the package to execute with the credentials and rights of a certain user and we don’t want the Integration Services to be executed through that user. So in such a case we can use a proxy account to execute the package. The use of proxy account is not limited to just the SSIS packages we can use it for other tasks as well. Let’s see how can we create a proxy account.
1.       Open the SQL Server Management Studio

2.       Login to the server on which you want to create a proxy account

3.       Select New Credentials by right clicking on Credentials under Security

4.       Give some name to the credential being created

5.       Select the user or type its name in the Identity Box

6.       Enter the password for the selected user and Hit OK

7.       The credential is now created

8.       Now right click SSIS Package Execution under Proxies in SQL Server Agent and select New Proxy

9.       Give a name to your Proxy

10.   Select the credential ‘Admin’ created earlier and then select the role for the proxy and Hit OK

So now the proxy has been created, lets configure a SSIS package in a job using this proxy.

1.       Create a new job by selecting New Job after right clicking Jobs under SQL Server Agent

2.       Give a name to the job

3.       Go to steps Page and click on  New

4.       Now select ‘Admin’ in Run As and schedule the package

5.       And it’s done the job will be executed using the credentials of the selected user.

Sunday, 16 October 2011

Transact SQL : Running SQL Server Agent Job Status

As we know the SQL Server Agent Job Activity monitor is a great tool but it doesn't shows us at what time the job has started execution and how much time has passed by. So we can use the below script to get the list of running jobs along with the time period for which they are running

create table #enum_job  
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varchar(100),
Current_Retry_Attempt int,
State int
insert into #enum_job
master.dbo.xp_sqlagent_enum_jobs 1,garbage
select R.[name]
getdate()as now
#enum_job a
inner join (

SELECT j.job_id,j.name,
ja.run_requested_date AS
AS RunningFor,

when 1 then
end +
':' +
when 1 then
end +
+ Case
when 1 then
end AS
FROM msdb.dbo.sysjobactivity AS
msdb.dbo.sysjobhistory AS jh
ON ja.job_history_id
= jh.instance_id
msdb.dbo.sysjobs_view AS j ON ja.job_id
= j.job_id
) R
on R.job_id= a.Job_Id and a.Running = 1

drop table #enum_job

SSIS : Redirecting Error Rows

  1. Set the Connection manager for the Data Source
  2. Set the Option of redirection in the Error Output Page of the Source Editor 
  3.  Create a table for the Error Rows (Not necessarily SQL) 
  4.  Configure a Data Destination for the error rows   
  5. Done Execute the package and the data with error goes to the Error Destination