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

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.