Now let us see how to create a simple SSIS package. The following are the steps:
- From the File menu, click New-> Project.
- Under Projects, choose Business Intelligence Projects and under Templates, choose Integration Service Project, and then click OK.
- By default a package named Package.dtsx is created under the folder SSIS Packages. Click View-> Solution Explorer to see this.
- Right click on the Connections pane, and then click New OLE-DB Connection.
- Create two new connections - one to your Yukon source server and another to your Yukon destination server. Name these connections as MySourceConnection and MyDestinationConnection.
- From the View menu, click Toolbox.
- Now drag and drop a Data Flow Task component on the Control Flow pane.
- Double-click the Data Flow Task component to take you to the Data Flow pane.
- From the toolbox, drag and drop a OLE DB Source component and OLE DB Destination component. Name them as MySource and MyDestination respectively.
- Double-click MySource.
- Set the connection of MySource to MySourceConnection.
- Set the Data-access mode to Table or View.
- Set the name of the Source table from the listed tables of the database.
- By clicking on the Columns tab on the left, you will be able to see the list of columns for the table. You can uncheck the columns that are not needed and then click OK.
- Set the properties of MyDestination OLEDB component in the same way as in 14.
- Click on MySource component. Drag the Green arrow you notice and make it point to MyDestination component. You have now connected the Source and the Destination components.
- From the Debug menu click Start to run your first SSIS project.
Once your package has executed successfully, you will see components (Dataflow task, MySource and MyDestination) coloured in Green. And if there is any failure during execution, the components are coloured in Red. The success or failure of the package can be monitored by clicking on the Progress tab in the project during execution.
Note: The Source and Destination components must contain the same number of columns and same column names (case sensitive) to avoid Validation errors.
You can also have Event Handlers at the Package level or for each component in the Event Handler tab.
So this is how we go about creating a simple package. My next article will focus on how you can programmatically create these SSIS packages using Whidbey.