Create a simple package step by step (1)

Before creating the package, we understand the requirements first:

A data source is a set of historical currency data contained in a flat file SampleCurrencyData.txt, with four columns in the source data.

Here is a sample set of data for the SampleCurrencyData.txt file:

1.00010001 ARS 9/3/2001 0:00 0.99960016

1.00010001 ARS 9/4/2001 0:00 1.001001001

1.00020004 ARS 9/5/2001 0:00 0.99990001

1.00020004 ARS 9/6/2001 0:00 1.00040016

1.00050025 ARS 9/7/2001 0:00 0.99990001

1.00050025 ARS 9/8/2001 0:00 1.001001001

1.00050025 ARS 9/9/2001 0:00 1

1.00010001 ARS 9/10/2001 0:00 1.00040016

1.00020004 ARS 9/11/2001 0:00 0.99990001

1.00020004 ARS 9/12/2001 0:00 1.001101211

1. First open SQL Server business Intelligence Development Studio, new project, Intergration Services Project

Named Item SSISStudy1, 1.1

2. Right-click the SSIS package, Solution Manager, create a new pack, named packages, LOADEMPLOYEEDATA.DTSX, 1.2

In Figure 1.2, the left-hand toolbar is the tool that implements the ETL functionality (there will be a functional description of the tools that are used later), and when development we need to drag the tool to the control flow interface, and in the Connection manager interface we can right-click the connection created on the data source.

3. Below we start to implement the package function according to the requirements.

First create a flat file connection in the Connection Manager interface, 1.3

FileName data source file path plus filename

Header line delimiter: The comma is the same as the delimiter in the file, and you can view the data format correctly by the left preview key.

Then click on the left Advanced option to set 1.4:

The file plane Manager generates the corresponding number of columns according to the rules set in general Options, the default name is column 0, column 1, column 2. We need to define a meaningful name in the name attribute in Miscellaneous.

If there is a change in the number of columns in the post data source, we can make adjustments by new and delete.

Defining the type of field in datatype, integration Services automatically suggests the most appropriate data type based on the first 200 rows of data. You can also change these suggestion options to increase or decrease sampling data to specify the default data type for integer data or boolean data, or to add spaces that are added to the string column as padding.

4. Right-click on the Connection Manager interface to create an OLE DB connection, 1.5:

The data in the file is loaded into the database AdventureWorks through the database connection.

5. Drag the Data Flow task control in the toolbar to the control flow interface, 1.6:

6. Double-click the data stream to enter the data flow interface

Drag the flat file source to the Data flow interface, select Flat File Connection Manager department (if only one plane Connection Manager is selected by default), 1.7:

7. Locate the OLE DB target under the Data Flow target list in the Toolbox, drag to the data flow interface, connect to the flat file, 1.8:

Set OLE DB destination, OLE DB connection Manager is AdventureWorks, table is Department, 1.9:

Enter the mapping option so that the column (Source) in the file corresponds to the column (Target) one by one in the database table, such as: 1.10:

So far the basic functions of the package have been completed, and the following will start debugging to see the results

Discover that data is inserted into the table by viewing the results

At this end a simple package is complete, and of course we will find that the package is just the basic function, the lack of exception handling, so we have to add exception handling , how to do exception handling will be described in detail later.

Create a simple package step by step (1)