Developing and Deploying SQL Sever Apps on Windows Integrity

19 of 39
• SQL Server Import Export Wizard is available on Integrity machine for data transfer
(ETL), but it has very limited capabilities for SSIS packages.
• SSIS packages can be managed from SQL Server Management studio on Integrity
Server. BIDS on windows client is only needed for the package design and debugging.
The example below shows how a sample SSIS package can be developed, debugged, deployed
and tested on Integrity Servers. This sample package copies a table from the Windows client’s
Adventure Works database to Integrity Server with sorting by Employee table. The example
also shows how to add a script task to the package and deploy it to Integrity Server.
Create and Debug the Package on Windows client
1. From Business Intelligence Development Studio select Create Project and
Integration Services Project. A designer window opens for development.
2. On the Control Flow tab, drag a Data Flow task to the design window.
3. On the Data Flow tab, select Source OLE DB server from the Tool box and drag it.
4. Double-click on it and set the source database connection as Adventure Works on
the 32-bit system. From connection manager
, as shown below, choose a New
Connection, if it is not already listed in the connection window.
5. Select Sort from the Tool Box and drag to the design window. Double-click on it and
choose to sort on table Employee. Connect the sort Box to the bottom of the Source
OLE DB.
6. Select the Destination OLE DB Server from the Tool box and drag it. Connect its
input to the sort’s output. Set connection to Adventure Works on the IA64 machine.
This step is similar to Step 4 above. You can create a new table or select an existing
table (e.g., the Employee table) at IA64 for receiving the data.
7. From the Debug menu select Start Debugging. T color of the each block changes as
the process moves through different stages — Gray waiting to run, Yellow currently
processing, Green successfully processed and Red failed. Verify that the program
completes successfully by looking at the Error List window.