SSISUnit - Testing Framework

SSISUnit is a testing framework for SSIS. it is based on xUnit Family of Unit Testing framework.

it provides better support to the SSIS environment. The main advantage of this tool is that you do  not need to write any program to define the test scenarios.It accepts the declaration approach, the defined test information are storing in XML files. This tool is user friendly, it will be useful to the user whoever not from coding backgrounds.

Benefits of SSISUnit:-

1. SSISUnit support task testing in SSIS.
2. You can a create any number of tests
3. it helps to test complex packages much easier
4. you can define new test or you can import it existing package to declare/define the testcase.

When the unit test is run, this is the sequence of execution for each test:

1. All commands in the setup node are executed.
2. The task referenced by the test is executed. If the task reference is the package guid, the entire

3. package is executed.
4. The command contained in the test node is executed.
5. The value returned from the command is compared to the expected value for the test.
6. All commands in the teardown node are executed.

SSISUnit UI Screenshot:-

 Functionality Available with this UI:

Download ssisUnitSetup2008.msi http://ssisunit.codeplex.com/releases/12606/download/42633

Download SSISUnit Source Codehttp://ssisunit.codeplex.com/SourceControl/list/changesets

Product Sample Package and Test

http://ssisunit.codeplex.com/wikipage?title=Product%20Sample%20Package%20and

%20Test&referringTitle=Home

For Further Information, refer

http://ssisunit.codeplex.com/

How to Configure Windows Firewall for SSIS

To enable remote access for MS SQL Server Integration Services, you have to configure the Windows Firewall by following listed steps here:

1. Open Windows Firewall from Control Panel
2. Click on Exceptions tab.
3. Click the "Add Program" button.
4. Browse to the folder where the SQL Server instance installation folders.
C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.exe is the default installation folder.

5. Click Open then on the next screen click OK button to add the SSIS executable to the application exceptions list.

6. Then the port # 135 should be added to the exceptions list as a TCP port.
7. You can define any descriptive name on the Name textbox, but you should specify the port number 135  and define it as a TCP port.

After these configuration, we can access the MS SQL Server 2008 Integration Services (SSIS) through on remotly

Connecting to a Remote Server's Integration Services.

By Default, we can connect the Database Engine, Analysis services of remote server using SQL Server management studio using Windows Authentication. But in the same way we can’t do for Integration services. If you want access the Integration services on remote server, then you need to do enable Remote access and provide access to the user by following the steps given below:-

To configure rights for remote users:-

1.       If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.

2.       Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.

3.       Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.

4.       Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.

5.       Right-click on MsDtsServer and select Properties.

6.       In the MsDtsServer Properties dialog box, select the Security tab.

7.       Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.

8.       In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.

9.       Click OK to close the dialog box.

10.   Under Access Permissions, repeat steps 7 and 8 to assign the appropriate permissions to the appropriate users and groups.

11.   Close the MMC snap-in.

12.   Restart the Integration Services service.

To connect to Integration Services on a Remote Server

1.       Open SQL Server Management Studio.

2.       Select File, Connect Object Explorer to display the Connect to Server dialog box.

3.       Select Integration Services in the Server type list.

4.       Type the name of a SQL Server Integration Services server in the Server name text box.

5.       Click Connect.

If it connects then you can execute the ETL Packages which are all deployed at the server by passing valid credential.

Still if you can’t access, then you have to configure the server’s firewall by adding  the SSIS program in exception list.

Hope it helps!

Installing SQL Server 2008 R2 SSAS Sample Database