SSIS data conversion component _ Export and Import Conversion

1. Export conversion component

Export column conversion reads data from the data stream and inserts the data into the specified file. the secondary conversion uses paired data columns: one column is the data to be output, and the other column is the files to which the data is output. during conversion, data is inserted into the specified file. if these files do not exist, the conversion creates these files and then writes the data to the files. the data to be written must be of the dt_text, dt_ntext, or dt_image data type.

The following example describes how to use the conversion tool. This example shows how to export the document column from the production. Document Table in the database adventureworks to a file.

1. Create a folder E: \ SSIS \ ssisdemo \ export. The imported file will be placed in this folder.
2. Create a new package named exportcolumndemo and add a Data Flow task to the control flow. Create an oledb connection in the Connection Manager to connect to the adventureworks database. :

 

1. Open the data flow interface and add an ole dbto connect to the production.doc ument table in localhost.adventureworks. Preview this table and you will see a column named filename, which specifies the file storage path. When exporting data, we can export the data in the document column to E: \ SSIS \ ssisdemo \ Export

 

1. Adding a derived column conversion will be connected to the newly created data source. Forward to the derived column. Edit a derived column. Set according to the yellow flag:

1. Add a column-to-column conversion to connect the derived column with it. Double-click the export column to display the window. Select the document column in the extract column. The document column should be in the [production] table. in [document], this column is a file column. In the file path column, select newpath, because this column is the file path generated by the derived column.

 

The following describes in detail the meaning of allowed append, forced intercept, and compiling of byte sequence columns in the window:

Column name	Description
Append allowed	Specifies whether to append data to an existing file during conversion. Default Value: false.
Forcible Interception	Specifies whether to delete the content of an existing file before the conversion is written. The default value is false.
Write byte order	Specify whether to write the BITs (BOM) into the file. Only when the data has the dt_text or dt_ntext data type and the data is not appended to the existing data file will the mark (BOM) be written)

1. Run the package, 1-1. You can see that there are nine files in the folder. See Figure 1-2

 

Figure 1-1

 

Figure 1-2

1. When we reset the attribute of the export column, set as shown in the following figure:

Then run the package, and an error is returned:

 

The following error message is displayed in the execution result:

[Export column [37] error: the file "E: \ SSIS \ ssisdemo \ export \ crank arm and tire maintenance.doc" to be written cannot be opened ". This file exists and cannot be overwritten. If the allowappend attribute is false and the forcetruncate attribute is set to false, this file will cause this failure. To avoid these errors, refer to the following:

Append	Truncation	File exists	Result
False	False	No	This conversion creates a new file and writes data to the file.
True	False	No	This conversion creates a new file and writes data to the file.
False	True	No	This conversion creates a new file and writes data to the file.
True	True	No	Verification Failed during the design of the conversion. Setting both attributes to true is invalid.
False	False	Yes	A running error occurs. The file exists, but the conversion cannot be written to the file.
False	True	Yes	Conversion will delete the file, re-create the file, and write data to the file.
True	False	Yes	Conversion will open the file and write data to the end of the file.
True	True	Yes	Verification Failed during the design of the conversion. Setting both attributes to true is invalid.

Binary import column Conversion

Import column conversion is the opposite of export column conversion. It reads data from the text and adds the data to the columns in the data stream. with this conversion, the package can add text and images stored in individual files to the data stream. the data type of the input column must be dt_text, dt_ntext, or dt_image.

Here is an example to introduce how to import an image file to the adventureworks database.

1. Create path E: \ Bi materials \ integrationservices \ importcolumn, and then create any five images in the file. Create a TXT text named "pictureinfo" in the file. The content is as follows:

E: \ Bi materials \ integrationservices \ importcolumn \ 1.png

E: \ Bi materials \ integrationservices \ importcolumn \ 2.png

E: \ Bi materials \ integrationservices \ importcolumn \ 3.png

E: \ Bi materials \ integrationservices \ importcolumn \ 4.png

E: \ Bi materials \ integrationservices \ importcolumn \ 5.png

The contents of the entire file are as follows:

2. Execute the following SQL statement in the adventureworks Database

Create Table [DBO]. [myimages] (

[ID] [int] identity (1, 1) not null,

[Imagefilepath] [varchar] (100) not null,

[Document] [Image] not null,

[Createtime] [datetime] not null,

Constraint [pK _ tblmyima _ 3214ec272d47b39a] primary key clustered

(

[ID] ASC

) With (pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF, allow_row_locks = on, allow_page_locks = on) on [primary]

) On [primary] textimage_on [primary]

Go

3. Create a new package named "importcolum", drag and drop a "Data Flow task" in the control flow, and then create an ole db connection in the Connection Manager to connect to the adventureworks database. :

 

4. Drag and Drop a flat file data source on the data flow tab. Name it "Image Source", right-click Edit, and a window is displayed. In the window, click Create. The data source is set to E: \ Bi materials \ integrationservices \ importcolumn \ pictureinfo.txt. Set general and advanced options as follows:

 

After the setting is successful, you can click preview to view the setting result:

1. After the flat data source is set, an import column is moved to the data flow tab, named "improt column", and connected to the flat data source. The green arrow points to the import column Conversion Tool. Double-click Import column conversion. In the input column tab, select the available input columns. For example:

1. Select the input and output attributes tabs in the pop-up box. You will find that there is a imagefilepath column in the input column set, but there is no output column in the output column set. Now we add an input column named "document" and the type is dt_image. Pay attention to the red mark.

 

We can see a row of warning information below. To solve this warning, perform the following operations. Select the input column named imagefilepath in the box. Set the filedatacolumnid attribute to the ID of the document in the output column. Such as the place marked in red.

 

Set successfully. The warning information disappears. The column conversion configuration is successful.

1. A derived column is attached to the data stream to convert the imported column. The arrow points to the derived column. Set a derived Column

1. Add ole db desination, connect import column and ole db destination, edit ole db destination and set it to the table created above (myimages), and click mappings setting to set column ing.

 

1. Run the package after the setting is successful:

 

View the myimages table in the target database. As follows:

We have learned how to export and import components. Thank you!