How to Extract, Transform & Load (ETL)
- 1). Click "Start," "All Programs" and "Microsoft SQL Server." Select "SQL Server Business Intelligence Development Studio." Click "File," "New" and "Project" to create a new Integration Services project.
- 2). In the "New Project" dialog box, select "Integration Services Project" from the "Templates" pane. Enter a name for the project, and uncheck the "Create directory for solution" check box. Select a location for your project by clicking "Browse." Click "OK." An empty package named Package.dtsx is created and added to your project.
- 3). Right-click the "Connection Managers" area and then "New Flat File Connection" to create a new source file connection. Enter a name for the connection. Click "Browse" to locate the source data file.
- 4). Right-click "Connection Manager" and then "New OLE DB connection" to connect to the destination. In the "Configure OLE DB Connection Manager" dialog box, click "New." Enter "localhost" as the server name. Select "Use Windows Authentication" in the "Log on to the server" group. Enter "AdventureWorksDW" in "Select or enter a database name" under "Connect to a database." Click "Test Connection" to test the connection. Click "OK."
- 5). Create a data flow task in the "Control Flow" tab. Click the tab. Expand "Control Flow Items" in the "Toolbox." Drag a "Data Flow Task" into the design interface of the "Control Flow" tab. Right-click the task, and click "Rename" to change the name.
Source...