Scribd
Upload
113 views2 pages

Bulk Insert Task for Sales Data

The document describes using the Bulk Insert Task in SQL Server Integration Services (SSIS) to load a sales data file into a SQL Server table. It provides steps to add the Bulk Insert Task to an existing SSIS package, configure it to load data from a text file into the sales_stage table with specified row and column delimiters, and set the task to skip the first row containing column headers. The package is then executed to load the data and verify that 2155 records were inserted into the sales_stage table.

Uploaded by

jbascribd
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
113 views2 pages

Bulk Insert Task for Sales Data

The document describes using the Bulk Insert Task in SQL Server Integration Services (SSIS) to load a sales data file into a SQL Server table. It provides steps to add the Bulk Insert Task to an existing SSIS package, configure it to load data from a text file into the sales_stage table with specified row and column delimiters, and set the task to skip the first row containing column headers. The package is then executed to load the data and verify that 2155 records were inserted into the sales_stage table.

Uploaded by

jbascribd
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
You are on page 1/ 2

Lab C: Using the Bulk Insert Task

DTS Lab C: Using the Bulk Insert Task


In this lab, you will use the Bulk Insert Task to load file extracts of sales data
into the sales_stage table.
If you have trouble completing the lab, please refer to the answer file at
C:\BITechnicalTraining\DTS\AnswerFiles\Lab C sales_stage.dts.

Exercise 1
Loading Sales_stage
Open up the Lab C starter package
.1 Right-click the Data Transformation Services folder, and then click Open
Package.
.2 In the Select File dialog box, type
C:\BITechnicalTraining\DTS\Packages\Lab C.dts in the File name box,
and then click Open.
.3 In the Select Package dialog box, double-click sales_stage.

To add the Bulk Insert Task


.1 Click the Bulk Insert Task icon in the Task toolbar.
.2 In the Description box, type bulk load sales_stage data
.3 Click polaris in the Existing connection list.
.4 Click [polaris].[dbo].[sales _stage] in the Destination table list.
.5 Type C:\BITechnicalTraining\DTS\DataFiles\sales.txt in the Source data
file box.
.6 Click Specify format.
.7 For the Row delimiter, click {CR}{LF}.
.8 For the Column delimiter, click Tab.

To configure advanced Bulk Insert Task options


The first record of the sales.txt file contains column headings. In this procedure,
you will configure a setting in the Options tab to start the data load with the
second record.
.1 Click the Options tab.
.2 Click the Only copy selected rows box.
.3 Type 2 for the First row.
.4 Type 0 for the Last row, and then click OK.

To set precedence constraints


.1 Click the Truncate Stage Execute SQL Task, press the CTRL key, and then
click the bulk load sales_stage data Bulk Insert Task.
.2 Click the Workflow menu and then click On Success.

APAC BI Technical Training

Lab C: Using the Bulk Insert Task

To execute the DTS package


.1 Click the Execute play button on the toolbar, or click the Package menu,
and then click Execute.
.2 Click OK in the Package Execution Results dialog box.
.3 If any step failed, double-click the steps name to display the error message
for the step.
.4 Click Done.

To save the DTS package


.1 Click the Package menu, and then click Save As.
.2 Use the information in the following table to save the DTS package to your
local SQL Server. Accept the defaults for any options that are not listed,
click OK, and then close DTS Package Designer.
Parameter

Value

Package name

sales_stage

Location

SQL Server

To verify package results


.1 Click Start, point to Programs, point to Microsoft SQL Server, and then
click Query Analyzer.
.2 In the Connect to SQL Server dialog box, click OK.
.3 Go to the database list in the toolbar and click polaris.
.4 Type the following query:
SELECT * FROM sales_stage

.5 Click the Execute Query play button on the toolbar.


.6 Review data results. You should have 2155 records in sales_stage. Verify a
single record of data by using the following table:
Column name

Value

customer_id

VINET

product_id

11

amount_sales

168.0000

quantity_sales

12

order_date

1997-07-29 00:00:00.000

APAC BI Technical Training

You might also like