CONFIGURING SSIS FLAT FILE CONNECTION MANAGER

A connection manager creates a pipeline between SSIS and the file location on your machine.

Add a Flat File connection manager to the SSIS package

1. In the Solution Explorer pane, right-click on Connection Managers and select New Connection Manager.

2. In the Add SSIS Connection Manager dialog, select FLATFILE, then Add.

3. Search google for SampleCurrencyData.txt.

4. Open the data and download as Raw

5. Save it on your system, where you can easily navigate to, for this example, we save it in documents. 

6. In the Flat File Connection Manager Editor dialog box, for Connection manager name, enter Sample Flat File Source Data

7. Select Browse.

8. In the Open dialog box, locate the SampleCurrencyData.txt file on your computer and click open.

9. Un-mark the Column names in the first data row checkbox 

Set Locale-Sensitive Properties

1. In the Flat File Connection Manager Editor dialog box, select General.
2. Set Locale to English (United States) and Code page to 1252.

Rename Columns in the Flat File Connection Manager

1. In the Flat File Connection Manager Editor dialog box, select Advanced.

2. In the property pane, change the Column 0 name property to AverageRate.

3. Change the Column 1 name property to CurrencyID.

4. Change the Column 2 name property to CurrencyDate.

5. Change the Column 3 name property to EndOfDayRate.

Remap Column Data Types

By default, all four of the columns are initially set to a string data type [DT_STR] with an OutputColumnWidth of 50.

Integration Services automatically suggests appropriate data types based on the first 200 rows of data. You can also change these suggestion options to sample more or less data, to specify the default data type for integer or Boolean data, or to add spaces as padding to string columns.

In this tutorial, Integration Services suggests the data types shown in the second column of the following table for the data from the SampleCurrencyData.txt file. The fourth column provides the data types required for the columns in the destination, which are defined in a subsequent step.

Expand table

Flat File column	Suggested type	Destination column	Destination type
AverageRate	float [DT_R4]	FactCurrencyRate.AverageRate	float
CurrencyID	string [DT_STR]	DimCurrency.CurrencyAlternateKey	nchar(3)
CurrencyDate	date [DT_DATE]	DimDate.FullDateAlternateKey	date
EndOfDayRate	float [DT_R4]	FactCurrencyRate.EndOfDayRate	float

1. In the list, select AverageRate, click on the data type, you will see a drop down, click on it and change to float[DT_R4].

2. In the list, select the CurrencyID column and in the property pane, change the Data Type of column CurrencyID from string [DT_STR] to Unicode string [DT_WSTR].

3. In the property pane, change the data type of column CurrencyDate from date [DT_DATE] to database date [DT_DBDATE].

4. Change the data type of column EndofDayRate from string[DT_STR] to float[DT_R4]

5. Select OK.

6. We have now created a flat file source connection manager which will bring in our data.