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

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.

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