Microsoft Docs

Step 1: Create a New SSIS Project

Open SQL Server Data Tools (SSDT) and create a new Integration Services Project.

1. Go to File > New > Project...
2. Under Installed, select Business Intelligence, then Integration Services.
3. Choose the Integration Services Project template.
4. Provide a project name (e.g., "FlatFileImportTutorial") and a location. Click OK.

Step 2: Add a Data Flow Task

Drag and drop a Data Flow Task onto the Control Flow designer.

1. In the SSIS Toolbox, find Data Flow Task under the "Favorites" or "Common" category.
2. Drag it onto the Control Flow tab of your SSIS package.
3. Double-click the Data Flow Task to switch to the Data Flow tab.

Step 3: Configure the Flat File Source

Add a Flat File Source component to read data from your flat file.

1. In the SSIS Toolbox, find Flat File Source under the "Sources" category.
2. Drag it onto the Data Flow tab.
3. Double-click the Flat File Source to open its editor.
4. Click New... to create a new Flat File connection manager.
5. In the Flat File Connection Manager editor:
   • Connection manager name: Enter a descriptive name (e.g., "MyFlatFileSource").
   • File name: Browse to and select your flat file.
   • Format: Choose Delimited.
   • Header row delimiter: Select the correct delimiter for your file (e.g., Comma {,} for CSV).
   • Header rows to skip: Set to 1 if your file has a header row.
   • Go to the Columns page to verify the column names and delimiters.
   • Go to the Advanced page to review and adjust data types if necessary.
   • Click OK to close the Connection Manager editor.
6. In the Flat File Source Editor, ensure the correct connection manager is selected and review the columns to be extracted. Click OK.

Step 4: Configure the OLE DB Destination

Add an OLE DB Destination component to load data into your SQL Server table.

1. In the SSIS Toolbox, find OLE DB Destination under the "Destinations" category.
2. Drag it onto the Data Flow tab.
3. Connect the output of the Flat File Source to the input of the OLE DB Destination by dragging the blue arrow.
4. Double-click the OLE DB Destination to open its editor.
5. Click New... to create a new OLE DB connection manager.
6. In the Configure OLE DB Connection Manager editor:
   • Click New....
   • Enter your SQL Server name and select the authentication method.
   • Choose or enter the database name where you want to import the data.
   • Click Test Connection to verify.
   • Click OK.
7. Back in the OLE DB Destination editor, select the newly created connection manager.
8. Choose the Table or view - fast load option for performance.
9. Select the destination table from the dropdown. If the table doesn't exist, you can click New... to create it based on the input columns.
10. Go to the Mappings page. Ensure that the columns from the Flat File Source are correctly mapped to the columns in the destination table.
11. Click OK.

Step 5: Run the SSIS Package

Execute the SSIS package to import the data.

1. Save your SSIS package.
2. Right-click on the package in the Solution Explorer and select Execute Package, or click the Start button (green play icon) in the SSIS Designer.

Observe the Data Flow Task. If successful, it will turn green. You can also view the execution results in the "Progress" tab.