SQL Server Bi step by step 2-using SSIS for simple data import and export

Let's start learning about SSIS and use SSIS to export data from SQL Server. First, open vs.net 2005, select the Business Intelligence Project, select the integration service project in the template, and enter the Project name:

After the package is created, we design it in the default package (I will not introduce some concepts. Please refer to thisArticleOr other tutorials or books.). First, add a data flow component to the control flow (Data Flow task), Double-click to enter the data flow. Select the oledb data source from the Toolbox on the left (Ole db Source. Double-click to set the connection. If no link has been created, create a connection. After the connection is selected, directly access the produt table. Of course, you can also obtain the data source through SQL statements, the stored procedure can be called, and the table or view name or SQL command can be directly placed in the variable through variable setting.

OK. After confirmation, the red error prompt does not exist. next, we will directly add another target data source. We will export the data to the Excel data format, so select Excel destination. Similarly, double-click to configure the Excel Connection Manager, after the file name and path are configured (Select to display the column name in the first line. The data will be displayed from the second line.). If the file does not exist, select the new one below to create a new Excel worksheet.

Switch from the left to the ing, and map the metadata columns in the data stream to the columns in the Excel table one by one. Because the Excel worksheet was just created automatically, 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 and you can see that the green execution is successful.

Open the Excel file in the specified path, which already contains the exported data. Is it worse than writing C # manually #CodeHow convenient is it to export database data to an Excel file? This is only the most basic function. We can export all products with black colors by setting SQL statements. Let's change the access mode of the oledb data source to SQL commands, and then enter the query statement:

 
Select * from production. productwhere (color ='Black')
 

 
When you execute the statement again, you will find that the generated Excel table only contains color = 'black' data (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.).
 

Next, we will import the exported product data to the product table. Then we will add the Excel source and ole db destination, which is the opposite process to export.
The oledb connection of Excel source points to the Excel Connection Manager (Set in export Excel destination just nowOf, Which will be listed in the Connection Manager below),
The connection of ole db destination is set to the database connection in ole db source. Similarly, it is set to access the product table in the form of [table or view]. After confirming that there is a red error
Note: this is 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 set 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 delete the generated Excel file and reconfigure the Excel Connection Manager to generate a new empty Excel file (Or delete the data in the generated Excel file.), And 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, this is because the export and export of the data are not in sequence, so they are executed synchronously. When the import is executed, it is found that the data is empty, so no data is successfully imported. however, an attempt to import data directly after Excel destination fails, and Excel destination is the data flow target, which means the end of the entire process. (At this time, only one error output can be defined in Excel destination.).

We switch to the control flow, add another data flow task, and point the first data flow to this (drag the Green Arrow with the mouse ):

Double-click the added data import (the edited data stream component name) and cut the component in the data stream. clear the Excel Data and run the package again. Why is there an error? "Why?" Look at the following error message:

An ole db record is available. Source: "Microsoft SQL native client" hresult: 0x80040e2f Description: "The statement has been terminated. ".

An ole db record is available. source: "Microsoft SQL native client" hresult: 0x80040e2f Description: "The 'production. insert duplicate key rows in product. ".

In fact, the product number productnumber is also the primary key. The problem is that the SSIS did not prompt before running. What should I do? For this purpose, we will temporarily 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 1 after the product number in Excel to form a new product number. Similarly, we derive a new product name, because there are also uniqueness constraints in the database.

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.

By comparing the database, 93 rows of new data have been successfully added.

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 (Next we will introduce). 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 (This is mainly for import. It may be because the product name is equal to 1, leading to an error.).

Download this project file. (For VS 2005)

Author: lone knight (like a year of water)

Source: http://lonely7345.cnblogs.com

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.