Extracting and Loading Data in Microsoft SQL Server 2005 Integration Services

Create an Integration Services package

Start SQL Server Business Intelligence Development Studio. On the File menu, point to New, and then click Project. Make sure that the Project Type is set to Business Intelligence Projects, and then click the Integration Services Project template. Type a name for the project. Specify the location folder for the project and confirm that the Create Directory For Solution check box is selected. Click OK.

Add an OLE DB connection manager

Right-click anywhere in the Connection Managers pane at the bottom of the Control Flow tab and click New OLE DB Connection. Click New to define a new connection. Keep the default: Native OLE DB\SQL Server Native Client. Type localhost for the Server Name. Select Use Windows Authentication and choose the desired database.

Review available connection manager types

Right-click anywhere in the Connection Managers pane at the bottom of the Control Flow tab and explore the list of connections available: Flat File Connection, ADO.NET Connection, Analysis Services Connection, and so on.

Add an Excel connection manager

Right-click anywhere in the Connection Managers pane at the bottom of the Control Flow tab and click New Connection. In the SSIS connection manager, click EXCEL (connection manager for Excel files) and click Add. Type a name for the Excel file and specify an Office Excel file path.

Create a data flow task

Click the Data Flow tab. If you go to the Data Flow page right after creating a package, you will see a message stating that no data flow tasks have been added to the package. Click the message link to add a new task. You can also access it from the Control Flow page.

Add an OLE DB Source data adapter

Drag OLE DB Source from the Toolbox to the grid. The small red circle on this data adapter means that it needs a connection manager.

Add a connection manager to the OLE DB Source data adapter

Double-click the OLE DB Source data adapter to open the Editor and click OLE DB Connection Manager. In OLE DB Connection Manager, select a connection manager. In the Data Access Mode, select Table Or View and choose the desired table.

Map the connection manager to the data adapter

Click columns from the left panel of the OLE DB Source Editor. This action maps columns from the connection manager to output columns of the adapter.

Add an Excel Destination data adapter

Open the Toolbox and expand Data Flow Destinations. Drag Excel Destination from the Toolbox to the grid. The small red circle on this data adapter means that it needs a connection manager.

Add an Excel connection manager to the Excel Destination data adapter

Double-click the Excel Destination data adapter. This is a destination component that needs to be connected to the input source component. Click the OLE DB Source adapter and connect it to the Excel Destination adapter by dragging the green arrow from OLE DB Source to Excel Destination. Double-click the Excel Destination data adapter to open the Editor and verify that the connection manager is selected. In the Name text box of the Excel sheet, click New. Change the name of the sheet and change the size of long columns. The Excel connection manager will not allow creation of long columns. Finally, click Mapping in the left panel of the Editor.

Execute the package

Right-click the desired package and choose Execute Package.

Create a data source

In Solution Explorer, right-click the Data Sources folder, and then click New Data Source. On the Welcome To The Data Source Wizard page, click Next. On the Select How To Define The Connection page, verify that Create A Data Source Based On An Existing Or New Connection is selected, and then click New. Leave the Native OLE DB\SQL Native Client provider selected. Type localhost in the Server Name text box, select Use Windows Authentication, and select a database. Click OK, and then click Finish twice.

Create a data source view

In Solution Explorer, right-click the Data Source Views folder, and then click New Data Source View. Click Next on the Welcome To The Data Source View Wizard page. Select a data source and click Next. Select the objects you want to include in your data source view. Click next, and then click Finish.

Create a new Named Query

In Solution Explorer, expand the Data Source Views folder, and then open the data source view. In the Tables pane, right-click an open area, and then click New Named Query. Type a name for the new named query and specify a SQL statement in the bottom pane to define your named query. Click OK.

Add a connection manager from Data Source

In the Connection Managers pane, right-click an open area, and then click New Connection from Data Source. In Select Data Source, choose a data source that you created.

Set an OLE DB Source from a Named Query

In the designer, drag a Data Flow task from the Control Flow, open the Data Flow, and drag an OLE DB Source from the Data Flow Sources tab. Double-click the OLE DB Source component. Then, select and expand the data source you created from the OLE DB connection manager list. Select the data source view from the tree and click OK. In data access mode, select Named query and click OK.