I wanted to become a little more familiar with SQL Server Integration Services, so I’ve posted the steps I used to create import data from a file into a test database and then archive the file.
Save the Test Import File as a .txt file locally. Create a database and run the Test Database Script to create and fill the test tables.

I used SQL Server 2008 with 2008 Integration services installed, as well as BIDS (Business Intelligence Development Studio) with Visual Studio 2008.

To import contents of a text file into a database and then archive the file.

Have the import file, database and archive directory already created.

1) Open BIDS and create a new project – Project types ‘Business Intelligence Projects’ – ‘Integration Services Project’.

Now we need to create connections to the file and to the database.
2) In the ‘Connection Managers’ pane, right click and select ‘New OLE DB Connection’ – Create a connection to the database that you want to import the data into.
3) In the ‘Connection Managers’ pane, right click and select ‘New Flat File Connection’ – Navigate to the file to import – Check ‘Column names in the first row’.
4) In the ‘Connection Managers’ pane, right click and select ‘New File Connection’ – For usage type, select ‘Existing Folder’. Navigate to the directory that will be used for the file archive.

Now we add the tasks to the workflow.
5) Go to the toolbox and drag a ‘Data Flow Task’ control onto the ‘Control Flow’ pane.
6) Go to the toolbox and drag a ‘File System Task’ control onto the ‘Control Flow’ pane.
7) Drag the green arrow from the ‘Data Flow’ task to the ‘File System’ box. This will designate the the Data job runs first and then the File Archive job.

Now we need to link a task to a connection.
8) Double click on the ‘File System’ to edit. Under ‘Source Connection’, select the import text file connection created in step #3.
Under ‘Operation’ select ‘Move File’. For ‘DestinationConnection’, select the connection for the archive directory created in step #4.

Now we set up the Data Flow task.
9) Double click on the ‘Data Flow System’ task to edit.
10) From the toolbox, drag a ‘Flat File Source’ control to the ‘Data Flow’ pane.
11) From the toolbox, drag a ‘OLE DB Destination’ control to the ‘Data Flow’ pane.
12) Drag the green arrow from the ‘Flat File’ source to the ‘OLE DB’ destination.
13) Double click the ‘Flat File’ source – For the connection manager select the Flat File connection created in step #3.
14) Double click the ‘OLE DB’ destination to edit:
a – In ‘Connection Manager’, select the database connection created in step #2.
b – In ‘Connection Manager’, select the table to insert into (Sales)
c – In ‘Mapping’, match the column of the flat file to the database column that it goes to.

To Execute package:
To run the package from BIDS, just click the ‘Start Debugging’ icon (green arrow or F5).

To Save Package:
Packages can also be stored in the SQL Server msdb table or in a package file so that the packages can be executed within SQL Server.
From the menu select ‘File’ then ‘Save Copy Of [PackageName]’ to open a dialog.
For the package location, you can select ‘SQL Server’ to store the package in a msdb database, ‘File System’ to create a .dtsx file, or ‘SSIS Package Store’ to save a file that will be accessible from SQL Server.