SQL Server Bi step by step 2 simple Import and Export with SSIS

Step by Step 2 Use SSIS for simple Import and Export

1. Create an integration service project

Open vs.net 2005, "file" -- "new" -- select business intelligence project -- select the integration service project in the template -- enter the project name

 

 

 

Ii. Export database tables to excel

After creating the package, we will design it in the default package [details 1 ].

 

 

 SSIS designer Interface

• Create a data source
  First, add a control flow component (Data Flow task) to the control flow, double-click to enter the data flow. Select the oledb data source (ole db source) from the Toolbox on the left, and add a new red error message. Double-click the component to configure its Connection Manager. Create a Connection Manager.

 

Create a data source Connection Manager

• Configure the ole db Source
  After the database is selected, set the produt table to be directly accessed. Of course, you can also obtain the data source through SQL statements here, where you can call the stored procedure, and you can also set variables, you can directly store the table or view name or SQL command in the variable.

 

ConfiguredOLE DBConnector

After configuration, the red error message does not exist. Add another target data source, and export the data to the Excel data format. Therefore, select Excel destination. Similarly, double-click to configure the Excel Connection Manager, configure the file name and path (the column name is displayed in the first line, and the data is displayed from the second line ).

 

 

Drag inExcel destinationAfter Components

•  Configure Excel destination
  Double-click the Excel destination component to report an error. Because the data source is not connected to the target source.

 

 

 

 

 

• Configure the connector and create a table

 

 

Click switch from the left to the ing column to map the metadata columns in the data stream to the columns in the Excel table. Because the Excel worksheet was created automatically just now, it corresponds to the name by default. In this way, the product table is exported. In the solution on the right, right-click the execution package or run (F5), and you can see that the green execution is successful.

 

 

 

After ing

 

 Data exported successfully

 

Iii. Export data queried using SQL statements
Data source: Set the Data Access Mode of the ole db data source to an SQL command, and then enter the query statement.

 

 

At the Excel destination end, set the corresponding table and ing. When you execute the statement again, you will find that the generated Excel table only contains data of color = 'black '.(Note: If the Excel files of all your data are not deleted, you will find that the exported data is added to the end of the previous data ).
 

 

4. Import database tables in Excel
Next, we will import the exported product data into the product table. Then we will add the Excel source and ole db destination, which is actually the opposite process to export.. (Note: To store the newly imported data in the product table, back up the database ).
The current source is Excel source.
Target: ole db destination. Note: The data access mode is [table or view].

 

After confirmation, there is a red error message, because the product in the database is identified by productid as the primary key, so it cannot be inserted. we will delete the produtid field from the ing settings. Similarly, we need to ignore the rowguid field, which is automatically generated in the database.

 

After confirmation, we will find that there is no error and only a yellow warning is left. We will ignore this warning temporarily. we deleted the generated Excel file and reconfigured the Excel Connection Manager to generate a new empty Excel file.(Or delete the data in the generated Excel file ),Then run the package again.
You will find that the data export just now is still normal, but the data import shows that no data is imported, because the data export and import just now are not sequential, therefore, they are executed synchronously. When the import is executed, the data is found to be empty, so no data is successfully imported.
Experiment: it fails to put the data import operation directly after Excel destination. when Excel destination is the data flow target, it means the end of the whole process.. (At this time, only one error output can be defined in Excel destination ).
Therefore, we add another data flow task-data export stream to point the first data stream to this (drag the Green Arrow with the mouse ):

 

 

After the import and export operations are configured, clear the Excel Data and run the package again. Why is there an error? "Why ?" Look at the following error message:
Error: 0xc0202009, which is located in data import. ole db destination [960]: ole db error. Error code: 0x80040e2f.
Ole db records have been obtained. Source: "Microsoft SQL native client" hresult: 0x80040e2f Description: "The statement has been terminated .".
Ole db records have been obtained. Source: "Microsoft SQL native client" hresult: 0x80040e2f Description: "Duplicate keys cannot be inserted in the 'production. product' object with the unique index 'ak _ product_productnumber .".

Because the data in the name, productnumber, and rowguid fields in the product table has a unique constraint! Duplicate records cannot be inserted. However, rowguid is automatically generated by the database. Therefore, ignore it during ing.(Remarks: ViewThe purpose of a unique index ).

Product table

 

Solution: For now, add a conversion component (derived column, updates column values using expressions) between the data stream source and data stream target ). add a new newproductnumber column and add xxh to the Product Number Column in Excel to form a new product number. Similarly, we derive a new product name, because there are also uniqueness constraints in the database.(Note: The added data xxh must have a new record and other records in the tableDifference! For example, if "1" is added, it may be the same as the record in the table, resulting in the immediate termination of the import. Import only a part of data ).

At the same time, we also need to modify the ing in the ole db target, and change the productnumber of the input column corresponding to the productnumber of the target column to the derived newproductnumber. change the name of the input column corresponding to the name of the target column to the derived newname. clear the Excel Data and run the package again. All the packages are green and executed successfully.

 

Clear the Excel Data and run the package again. The package is green and all operations are successful.

 

 

By comparing the database, the database has successfully added 504 rows of new data.
You may find that the data export and import components in the control flow are both constraints, that is, the data export must be successful before the import can be performed.(Will be introduced later ).In addition, the so-called data export is not limited to Database Import and export. The data stream source and data stream target can be Excel, flat file (txt, CSV), XML, datareader and other connections. that is to say, you can also import TXT files to excel files or import XML files to databases.
Well, today is an introduction to SSIs. We use SSIS to import and export data and export the data in the product table to excel, then, the product number and name fields are converted by the derived function and then imported to the database. In this case, we understand the control flow and data flow, data flow source, and data flow target, we also introduced the derived column component to implement our import.

Problem: Text truncation error found in Excel import SQL:

 

An error always occurs when importing data from an Excel file to a table: the data in the column with the name length of 255 in the data stream is inserted into the column with the name length of 25 in the database name. data may be truncated.
This indicates that duplicate values are inserted, which violates uniqueness.
------------------------------------------------------------- Error Report -------------------------------------------------------------------
Error: 0xc0202009, which is located at data export. ole db destination [520]: ole db error. Error code: 0x80040e2f.
Ole db records have been obtained. Source: "Microsoft SQL native client" hresult: 0x80040e2f Description: "The statement has been terminated .".
Ole db records have been obtained. Source: "Microsoft SQL native client" hresult: 0x80040e2f Description: "Duplicate keys cannot be inserted in the 'production. product' object with the unique index 'ak _ product_productnumber ."
---------------------------------------------------------------- End -------------------------------------------------------------------------

 

 

Project Step1 --- 4 source code file: the version is SQL 2005. You must install the Excel application before running the code.

/Files/cocole/Step1-4Sql05.rar

 

Author: Wukong's Sky (tianma xingkong)
Source: http://www.cnblogs.com/cocole/
The copyright of this article is shared by the author and the blog Park. You are welcome to repost this article. However, you must retain this statement without the author's consent and provide a clear link to the original article on the article page. Otherwise, you will be held legally liable.