Create an SSIS package-create an end-to-end package

Source: Internet
Author: User
Tags bulk insert ssis

Through the basic task and transforms learning, you can now transfer to the practical application of SSIs. In the end-to-end package series, we first explain how to import a series of file data to SQL Server, and then add some complex conversions, finally, we will see how to handle errors in the package and dynamically create a package.

Create a project

The primary purpose of using SSIS is to read data from the data source and write it into destination. This wizard describes how to read data from a flat file. Use the following text

 Zipcode state zipname
32008 FL Branford
32009 FL bryceville
32011 FL Callahan
32013 FL Day
32033 FL Elkton
32034 FL Amelia Island
32038 FL Fort White
32040 FL Glen Saint Mary
32043 FL green Cove sprin
32044 FL Hampton
32046 FL Hilliard
32052 FL Jasper
32053 FL Jennings
32054 FL Lake butler
32055 FL Lake City
32058 FL lawtey
32059 FL Lee
32060 FL boys ranch
32061 FL Lulu
32062 FL Mc alpin
32063 FL macclenny
32065 FL orange park
32066 FL Mayo
32068 FL Middleburg
32071 Fl o Brien
32073 FL orange park
32082 FL Ponte Vedra BEAC
32083 FL Raiford
32084 FL Saint Augustine
32086 FL Saint Augustine
32087 FL Sanderson
32091 FL Starke
32092 FL Saint Augustine
32094 FL wellborn
32095 FL Saint Augustine
32096 FL white springs
32097 FL yulee
32102 FL Astor
32110 FL Bunnell
32112 FL Crescent City
32113 FL Citra

Create the zipcode.txt file, which contains the US Postal code. Create a new project, select project type integration service project, 5-1, and rename the default package file package. dtsx to zipload. dtsx.

Figure 5-1

Create a connection

Create a connection that can be used in multiple packages. In solution manager, right-click the data source folder to create a data source. Open the data source wizard, select "Create a data source based on an existing or new connection" and click New to open the Connection Manager.

Note: There are multiple ways to establish a data source connection. For example, you can drag and drop Data Source tasks in data flow.

Select native ole db \ SQL native client, enter the connection name, select the authorization mode for convenient read/write database, and then select adventureworks database, and click OK. If the adventureworks database is not installed, you can select any database, test the connection, and name the data source adventureworks. The data source cannot be shared by dragging and dropping data sources in dataflow. When you click Create connection in connectionmanagers below, you can see a connection we have created. You can select this connection in all packages.

Next, create a flat file link pointing to zipcode.txt, right-click the package designer and choose "New File Link", name it zipcode extract, and point to connection c: \ ssisdemos \ zipcode.txt.

Select the default delimited from the format drop-down list and text qualifier <none> from the drop-down list. Text qualifier is used to set the delimiter of a string. It is useful to set this option to separate the text with commas, and ignore these symbols to only capture the text. In this example, the text content does not have a separator, so keep the default value <none>. Set the header row delimiter option to tab {t} and select column names in First Data row single partition. The final interface is 5-2.

Figure 5-2

Click columns to go To the tag page. The default value of row delimiter is {Cr} {lf}, which means to separate rows by using carriage return. Column delimiter selection item keep default set tab {t}, meaning to use a tab to distinguish columns. Because the column names in First Data row option is selected, the data rather than the column name is displayed in the first row.

Click "advanced" To Go To The tag page. on this page, you can set the Data Type of each column. The default type is a string of 50 characters, which is longer than the actual one in this example. Select zipcode and click suggest types to open the suggest column types dialog box. Generally, keep the default options and click OK to select the appropriate type. Then we can see that the data type is changed to two-byte signed integer [dt_i2]. This type meets the requirements in general. If the zip code number starts with 0, an error occurs, select string [dt_str] from the drop-down list box, set outputcolumnwidth to 5, and set textqualified to false to 5 to 3.

Figure 5-3

Create a task

After creating the connection, you can create a task. First, drag and drop a Data Flow task in control flow and rename it as load zipcode info.

Create Data Flow

Double-click load zipcode info to go to the data flow interface. Here we will perform a more detailed design. If you are careful, you will see a change in the tool list in the toolbar.

Drag and Drop a flat file source on the dataflow interface and name it Florida zipcode file. Double-click to open the editing page 5-4. The connection zipcode we created above is selected by default. Click columns to view the column to be exported, click OK to exit the editing page.

Figure 5-4

Next, drag and drop an SQL Server Destination to rename it as adventureworks, and use the green arrow to connect it to Florida zipcode file. Double-click adventureworks and select adventureworks data connection. In the drop-down menu of user a table or view, new can be written here.CodeCreate a table instead of creating a table in SQL Server Management studio. Modify the default table creation statement as follows:

 1   Create     Table     [  Zipcode  ] (
2 [ Zipcode ] Varchar ( 5 ),
3 [ State ] Varchar ( 2 ),
4 [ Zipname ] Varchar ( 16 )
5 )

Note: If the arrow is not used to connect to the previous task, you cannot click New here. After the table creation statement is complete, click the drop-down list box to view zipcode, and you can also see Field ing on the Mappings tab page. SSIS always maps by name. The field names in the created table are the same as those in the data source. Therefore, we can see three parallel mappings, 5-5.

 

Figure 5-5

To make the package more complete, ensure that the package can be run every time, return to the tontrol flow interface, drag and drop an execute SQL task, rename it as purge zipcode table, and double-click to open the editing interface, select adventureworks connection from the connection drop-down list. On the SQL statement option, enter the following SQL statement on the editing page:

Delete from zipcode

Click OK to complete the editing, place it on the load zipcode info task, and connect them with the arrow. The entire package is complete. Click the Save button. It is worth noting that the. dtsx file is saved here and not saved to the server. Deploying the package will be explained in the subsequent documents.

Execution package

After editing, you can click the debug button to run the task, or right-click zipcode. dtsx and click Execute Package. It takes several seconds to compile and verify the code before running it. On the output page, you can see the running process. Then, the task icon turns yellow to green, proving that the package is successfully executed. If the execution fails, the error message is displayed on the output interface. If the output page is not displayed, click View-other Windows output.

On the data flow page, you can see the number of rows imported. The page is 5-5.

Figure 5-5

Note that I only copied a part of the content when creating the TXT file above. If you experiment on your machine, 41 rows should be used here. By default, any modifications made to the package during debugging will not be compiled and run until the next operation. Click Stop debugging to stop running.

Problem

There may be a problem here. When you click "run", it is not as smooth as mentioned above. An error occurs, prompting 5-6:

Figure 5-6

With the help of the powerful Baidu, we found the crux of the problem and the solution. First, we posted this address.

Http://support.microsoft.com/kb/2009672

    1. symptom: in SSIs, create a data flow task that contains SQL Server Destination. When you try to run a package, if you are using Windows 7 or Vista, The UAC is enabled, is a new technology introduced by Microsoft in Windows Vista to improve system security, it requires all users to run Programs and tasks in standard account mode to prevent unauthenticated program installation, and prevent Standard Users from making improper system settings changes. In this case, you will encounter the following error: SQL Server Destination] error: unable to prepare the SSIS BULK INSERT for data insertion.
      [SSIs. pipeline] error: component "SQL Server Destination" failed the pre-Execute phase and returned error code 0xc02071.
    2. cause: in a system that enables the UAC function, when a program such as SSIS is executed by a user in the Administrator group, there are two security tokens: low-level and high-level. When you select "Run as administrator" to run the program, the higher level will be used. By default, the SSIS program uses the lower level when connecting to the database.
    3. solution: If you are running a package in SQL Server Management Studio (SSMs), business intelligence development studio (bids), or dtexecui.exe, re-run the program and select Run as administrator. 5-7. Then, the dialog box 5-8 is displayed. Click Yes to open the program and run the package again. Figure 5-7 Figure 5-8

So far, a simple package is complete. We will import the content in the TXT file into the database and use the select * From zipcode statement in the database to find the data, they have the same content as TXT.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.