Common SSIS packages-use bulk insert to execute tasks

Now execute a simpleBULK INSERTTask to practice the topic just discussed. First, createSSISProject. SetPackage. dtsxRenameBulkloadzip. dtsxIf the dialog box is displayed, whether to rename or select YES.

Create a folder firstC: \ ssisdemos, Put the file (/files/tylerdonet/zipcode.txt) in this folder. Create a database locally and use the name of a common Microsoft database.Adventureworks, Use the followingCodeCreate a table:

1 Create   Table  Insertcontent(
2 Zipcode Char ( 5 ),
3 State Char ( 2 ),
4 Zipname Varchar ( 16 )
5 )

Create an SSIS project, use the default package, and rename it bulkinserttask. dtsx, switch to control flow interface , if you put Execute SQL task and bulk insert task. Double-click to open Execute SQL task editing interface, renamed as purge insertcontent table. Enter the description in the description column.

InConnectionCreateLocalhost. adventureworksLink, inSqlstatemenEnter the followingSQLStatement: 

1 Delete   From  Insertcontent

The final interface is as follows: 3-5. Click OK to exit the interface.

 

 

Drag and Drop a bulk insert task from the toolbar, double-click it to open the editing page, and select<New Connection......>Create an ole db connection,Create a new connection in this interface to connect to the local serverAdventureworksDatabase, Interface3-3The information on your own machine may be different., The new connection interface is as follows.

InConnectionThe link you just created is displayed in the drop-down list box. Select a table from the target table.[Adventureworks]. [DBO]. [Insertcontent]. Keep other settings by default. The line Delimiter is{Cr} {lf}(Line breaks). The column Delimiter is a Tab character. InFileSelect again<New Connection...>Create a connection and open the connection management interface. InUsage typeSelectExisting file, SelectC: \ ssisdemos \ zipcode.txt. The final interface is shown in figure3-4

NextOptionsTag settingsFirstrowIs2. IfZipcode.txtFile, you can find that there is a header between the data rows. SetFirstrowIs2RunTaskWill ignore the header of the first line, insert from the second line. ClickOKUser return interface.

Click taskThe first purge insertcontent tableMove the green arrow at the bottom to point to the second task.Load zip codes, There will be a green line with arrows. This link creates twoTaskIn the orderTaskThe second task can be executed only after the execution is successful. ThisPackageIt is finished. Click Save and execute it. UseSQLStatement to clear the table, and thenBULK INSERTRe-insert data. After the execution is successful, the icon turns green, as shown in figure3-6

 

ClickStopThe button can interrupt execution.BULK INSERTThe disadvantage is that you cannot handle errors. It is a task that is either all or no. If one row fails to be inserted, the entire task will fail (this can be changed by setting the maximum number of errors ).