Here I'm going to use MaxMind's GeoIP flat file To create a new Integration Services package:
1. Choose Start > All Programs > Microsoft SQL Server 2005 > SQL Server Business Intelligence Development Studio.
2. On the File menu, choose New > Project to create a new Integration Services project.
3. In the New Project dialog box, select Integration Services Project.
4. In the Name box, change the default name to Importing Flat Text File.
5. Click OK.
An empty package called Package.dtsx will be created and added to your project, you can rename this package as you want. To add connections to the flat file and the database
1. Right-click anywhere in the Connection Managers area (bottom most window) and then click New Flat File Connection (for other type of connections like Excel, FTP etc., choose New Connection), it will open Flat File Conneciton Manager Editor.
2. On the Flat File Connection Manager Editor screen, enter connection manager name.
3. Click Browse.
4. In the Open dialog box, browse to the flat text file you intend to extract data from.
5. Set the fields according to your flat file. To preview the columns in the text file, click Columns tab. To rename the columns, click Advanced, and in the property pane change the "Name" property for each column.
6. To add connection to the database, right-click in the Connection Managers area and then click New OLE DB Connection.
7. On the Configure OLE DB Connection Manager screen, click New.
8. Choose your server (servers in your network will be listed) or enter IP.
9. Choose authentication type and the database.
10. Test the connection.
Your connection managers window will look something like this:To add a Data Flow task:
1. Click the Control Flow tab.
2. Add a Data Flow Task from the toolbox (under Control Flow Items section) to the design surface of the control flow tab. Rename it as you want.To add a Data Flow Source:
1. Click on the Data flow tab.
2. Add a Flat File Source from the toolbox (under Data Flow Sources section) to the design surface of the data flow tab. Rename it as you want.
3. Double-click the Flat File source to open the Flat File Source Editor box.
4. In the Flat file connection manager dropdown, select the created flat file connection.
5. You can rename the output columns if you want.To add a OLE DB destination:
1. Add a OLE DB Destination from the toolbox (under DataFlow Destinations).
2. Drag the green arrow from the Flat file source to the OLE DB Destination.
Your data flow design surface will look something like this:
3. Double click OLE DB Destination to open OLE DB Destination Editor.
4. Select the created OLE DB connnection for OLE DB connection manager (it will be automatically selected).
5. Click New to create a new table or choose already created table.
6. Here, I have already created a table, so I'm just going to map the input columns with the table's columns.
My table structure:
Mapping:To run this package:
1. Click on the Debug menu and click Start Debugging.
2. After the package has completed running, on the Debug menu click Stop Debugging.Additional work:
If you see above, all the columns in my flat file are enclosed within double quotes, to strip them or to format the data.To format the format columns in the flat file before importing:
1. Add a Derived Column from the toolbox (under Data Flow Transformations).
2. Connect Flat file source's output to Derived Column and Derived Column's output to OLE DB Destination.
3. Double click the Derived Column to open Derived Column Transformation Editor.
4. Expand Columns tree and drag the column to be formatted to Derived Column Name, and add you expression. Here, I'm removing double quotes.To truncate data in the existing table before importing:
1. Click Control Flow tab.
2. Add Execute SQL Task from the toolbox (under Control Flow Items) to the design surface of control flow tab.
3. Double click it to open Execute SQL Task Editor, click the SQLStatement section ellipsis and add your SQL statement i.e., "Delete From TableName"
4. You can add it before or after the data flow task.
5. Add the connections appropriately.
Start running and the colors will change automatically:
There might be better ways to accomplish this, if so, please let me know.