This is the fifth day of my participation in the November Gwen Challenge. Check out the details: The last Gwen Challenge 2021

Through a complete practical SSIS project, its basic use and processing process is introduced. First, of course, is the installation of SSIS and the creation of the SSIS project.

Installation of SSIS

Make sure integration services are installed.

During the SQL Server 2012 installation, select Integration Services and SQL Server Data Tools(SSDT, Business Intelligence Development Studio Business Intelligence Development Studio

SSDT can also be installed in Visual Studio, or installed independently.

Create the SSIS project

In the Start menu, locate SQL Server Data Tools under SQL Server, open it and run it.

The first run will let you choose which environment Settings to use. Including Business Intelligence Settings, General Development Settions, Visual C# Development Settings, etc. Usually select Business Intelligence Settings.

Welcome page click “New project”, or, under file – > New – > Project. Select the Integration Services template and create an SSIS project named FirstSSIS.

An empty SSIS project is created.

SSIS data pump

SSIS was originally created to move Data, and the Data Flow Task provides this capability.

Data flow task

Start with the basics of a data flow task:

Figure 1 shows a very simple data flow task: data is read from a database to a data flow task through an OLE DB source adapter and written to a target database through an OLE DB target adapter. Source and target adapters interact with databases or other data stores through connection managers.

There are three objects in Figure 1:

  1. OLE DB source adapter
  2. OLE DB target adapter
  3. Data flow path connecting OLE DB source and OLE DB target adapter (Data Flow Path) (Green arrow)

Connection manager and OLE DB source/target adapter

The most important thing about Connection Managers is that they are used to bridge (connect)SSIS packages to external data sources. The connection manager handles things like server names, database instances (if available), database names, and authentication. The OLE DB source/target adapter handles things like tables and columns.

The OLE DB source adapter performs queries against the server/database configured in the connection manager in the context of credentials provided by the connection manager. A few important notes:

First, SSIS never stores or saves decrypted password fields. If your connection requires a password and the “Save my Password” (or “Save password”) check box is checked, the SSIS package will be encrypted.

If Windows authentication is used, SSIS stores the password internally and encrypts it, and the connection manager connects to the database in the context of the user executing the package.

The OLE DB target database also acts as an interface to the data flow task and connection manager, and every piece of data that enters the OLE DB target adapter is written to the target data table specified by the OLE DB target editor.

The OLE DB source adapter brings data into the data flow task. The OLE DB target adapter writes the data output from the data flow task to the target database table.

Data pipeline (Pipeline)

The data flow path connects the source adapter, transformation, and target adapter within a data flow task.

There is no transformation in the simple data flow task in Figure 1 above — so the data flow path transfers data directly from the OLEDB source adapter to the OLEDB target adapter.

All components in the data flow task operate on rows of data. The row is the basic unit. Rows are grouped into buffers, which are used to move rows through the data pipeline. It is called a pipe because the process of rows flowing in, through, and out of a data flow task is much like the flow of pipes.

The data is read in chunks into the OLE DB source adapter. A single data block fills a buffer. The data buffer is processed first, then the data is moved “downstream.”

Example Start a data flow task

Add a data flow task

Open the SSIS project FirstSSIS created above. From the Toolbox, drag and drop a data Flow task onto the Control Flow canvas.

The Data Flow Task is a Control Flow Task. This is important because when you open the Data Flow TAB, you can see a lot of similarities between control Flow and Data Flow tasks, such as toolboxes, green and red arrow connectors, and so on. It’s also easy to get confused.

The data flow task name can be changed by double-clicking.

Right-click the Data Flow task, Edit, and you can see that the Data Flow TAB opens, which is the Data Flow Task editor.

Adding a source adapter

Drag an OLE DB source adapter from the toolbox to the canvas

When you add an OLEDB source to a data flow task, the component displays an error icon. You can view the Error List for details. In the menu bar View, click Error List

SSIS automatically validates operations. The error list contains details about SSIS errors and warnings.

In the error list, you can see that the OLE DB source was not assigned a connection manager in the Data flow task.

Source adapters add connectors

  • Adding connectors

Double click on the OLE DB source adapter (double click on the icon instead of the text section), or right-click and select Edit Edit… Open the OLE DB source editor.

In the OLE DB source editor, you can select an existing connector or create a new one.

To create a new connector, you can add the connections you want, then select:

  • How to import source data

After selecting the connector, go back to the OLEDB source editor as follows, with a warning message: “Select a table of view from the list.”

So you have to decide how do you bring in the data? The Data Access Mode property specifies how the OLEDB source adapter imports Data.

Table or View: Allows you to select a Table or view as a data source from a database configured in the connection manager. This is the default option. If you choose to use this option to get data from the source database, simply select the table or view Name from the Name of the Table or View drop-down list below the Data Access Mode drop-down list:

Table name or View name variable: You can save the Table name or view name as an SSIS variable. This provides flexibility for dynamic source tables. It is important to remember, however, that the OLE DB source adapter is designed to be “coupled” to the data flow task. This means that you can change the name of the table, not the column name or its data type. Therefore, it is a common misconception to load tables with different schemas in a single data stream

SQL Command and SQL Command From Variable: provides a way to use SQL statements to query databases configured in the connection manager drop-down list. Such as getting columns from a table or view, or from joined tables or views. After selecting this item, the SQL Command Text Text box appears below

After you select the SQL command, you need to fill in the SQL statement. Otherwise, the “Write SQL statement” message is displayed. Set it as follows:

You can visually Build SQL queries by clicking the “Build Query” button.

Query Builder Utility

  • Columns for source data import

In the Columns page on the left side of the OLE DB source editor, you can see the Columns provided by the Data Access Mode in the connection manager

“Available External Columns” : Columns that are exported from the source adapter to “downstream “(transformation or target table). If you do not want to export data for a particular column, you should cancel it in the connection Manager statement above to avoid data redundancy and waste.

The “Output Column” below defines Column aliases. This can be done directly in SQL statements using AS.

Then click “OK”.

OLE DB target adapter

  • Add the OLE DB target adapter to the data flow task canvas

After you add a target adapter, you need to give the target adapter an input, the target adapter needs to accept an input, and then write the input data to the target.

Click on “OLE DB Source” and drag the green arrow at the bottom onto “OLE DB Target”. As follows:

If you edit the target adapter without adding input, you will be prompted as follows:

You can change the name of the target adapter to “FullName”.

  • Data flow path (Data Flow Path)

Right-click the connected data stream path and select Edit.

The Metadata page of the Data Flow path editor displays the contents of the Data pipe that connects to the Data Flow components.

  • OLE DB target editor

After the target editor is opened, the connection manager still selects the connector created above.

The default data access mode property is “Table or View — fast load”.

The main differences between “Table or view — fast load” and “Table or view” are: The fast load option can execute a BULK INSERT statement, while the non-fast load option (” table or view “) restricts the generation of INSERT INTO statements.

If the target table or view exists, you can select it from the Name of the Table or View drop-down list. If not, click the New button to the right of Table or view name. In the Create Table dialog box that pops up, click OK and the statement will be executed to create a new table or view.

The name of the new table is the name of the OLE DB target adapter, and the columns are from the dataflow path metadata, where the column data type is DT_WSTR, where the statement is nvARCHar (50), The DT_WSTR data type in SSIS is equivalent to the NVARCHAR of SQL Server.

After the table is created, it is displayed in Table or View Name.

  • Pay special attention to

== In normal or actual production, if the table does not exist, you should not use the command == to create a table. The reason is that the corresponding table is only created automatically according to the incoming Matedate data, but there is no key, constraint, index and so on for the fields in the table structure, which will lead to subsequent operation errors, data integrity and other serious problems.

Therefore, the statement to create a table here is only a reference.

  • mapping

You can see the hint at the bottom above: Map the columns on the Mappings page.

Click on the Mappings page on the left,

Available Input Columns are the Columns that flow from the data flow path to the OLE DB target; Available Destination Columns are the Columns of the FullName table of the target database. Because the column names and data types are the same, the OLE DB target editor maps them automatically.

Click OK to close the OLEDB target editor.

Test the SSIS package

Click “Start Debugging” or F5 to test the SSIS package you just created.

The successful operation is as follows:

The data has also been moved to the target table FullName

conclusion

This article mainly introduces:

  • SSIS installation.
  • Creation and use of SSIS projects.
  • The relationship between connection managers and adapters (in OLE DB sources and OLE DB targets) was discussed.
  • You examined the roles and some of the capabilities of the data flow paths that interconnect the data flow task components and present a “data flow pipeline.”
  • Learn about design-time validation and how to view error and warning information.
  • Explored some cool time-saving features of the adapter editor.
  • Peep the content of “Data flow task” and view the metadata of “data flow path”.
  • You learned how OLE DB targets use metadata to build CREATE TABLE statements.

reference

Integration Services (SSIS) Packages The SSIS Data Pump – Level 2 of The Stairway to Integration Services Content, etc.