Documentation

Creating a project.

The best place to start a project is in Visual Studio. You will need a template called "Integration Services Project". (I'm using VS2019 as at the time of writing this VS2022 has issues in finding this template.) Press Next, enter a name and press Create. The packages.dtsx is a standard package name under SSIS Packages folder, so I would advise you right click on it and rename it.

Flat file connection.

To use a flat file, or any file in SSIS, you must first create a connection. This is set up by the Connection Manager.
In the solution Explorer, right click on the Connection Manager, select New Connection Manager. Select FLATFILE and Press Add... You will then need to enter the details of the file. Check the 4 options on the left, General, Columns, Advanced and Preview and then press OK.

OLE DB connection.

To connect to the a SQL Server table, you will need a OLD DB connection. This is handled by the same Connectin Manager.
In the solution Explorer, right click on the Connection Manager, select New Connection Manager. Select OLEDB and Press Add... and then New... Now put in the Server name, the Database name and Test the connection using the Test Connection button. Then Press OK.

Data Flow.

We now have a source (flat file) and a destination (Sql). We now need to move and transform the data from one to the other.
From the Control Flow tab, go to the SSIS Toolbox (left) and drag the Data Flow Task onto the Control Flow part of the screen. Right click on the Data Flow Task and rename it to something sensible.
Double click on the Data Flow Task or select it and go to the Data Flow tab. From the SSIS Toolbox, drag the Flat File Source control on to the screen and rename.
Let's now add a Lookup transformation. This is to match data. Drag the Lookup from the SSIS Toolbox onto the screen below the current Data Flow Task and rename. Now drag the blue line from the Data Flow Task onto the new Lookup Task. Now double click on the Lookup component and check the OLE DB connection. Now we need to either select a table name, or a query. In the columns, you drag from the input columns to the output to show the data match between the input file and the table/query and then tick any box in the output fields where you want to add to the data. You can obviuosly have a number of lookup tasks before writing out to the destination.
For the destination drag the OLE DB Destination to the Data Flow screen. Link the Lookup blue line from above and rename the destination. Now go to the Edit screen (or just double click on the destination) and locate the destination file. In the Columns, you can re-route any links, but SSIS will do most of the work for you. That's it. Now we need to test.

Testing.

As this is a Visual Studio project, the testing is pretty standard. Press F5, click on the Start button or use the debug menu. The tasks will be amber when in progress, green when done OK and red if failed.

Adding Container Loops.

There are two types of loop that can be used in SSIS. For loop and ForEach loop. These are used for being able to loop through multiple files. Very handy for importing all files from a directory. You will get the SSIS Containers from the SSIS Toolbar and then drag the Data Flow task into it. You will need to amend the file connection manager and change the connectionString property to use a varisble called user::varFileName using the expression builder. The configuration is in the Property of the connection manager. Use the expression properties (F4) and in Expressions, select the ConnectionString from the property column.

Scheduling.

Variables (and scope).

To edit the variables, there is a screen and this can be found in the menu, View, Other Windows and Variables. Variables is at the top. Here you can create the variables, give them a data type and scope.

Processing Multiple Rows.

This is using the ForEach loop again, but this time it is looping through rows and not files as in the previous example.
Consider a SQL task that was selecting data from a table.

Precedence Constraints.

There are 3 basic constraints. These can be changed by right clicking on the connector between two components.
On success: The next step executes if the previous step completed successfully
On failure: The next step executes if the previous step failed
On completion: The next step executes regardless of whether the previous step completed successfully or failed

Common SSIS Tasks.