In the above screenshot, the Data Viewer is
showing the data at the output terminals of the DataReader.
Data Transfer to an MS Access Database
[ 122 ]
Summary
This chapter described extracting data from an SQL Server 2005, and loading it to the
MS Access Database using the Microsoft Jet 4.0 OLE DB Provider. This chapter also
showed how to use a Data Viewer to monitor the flow in the data path.
Data Transfer from a Text File
Using the Bulk Insert Task
This chapter shows you how to create a package that can transfer data from a text file
to a table in a SQL Server database using the Bulk Insert Task.
The Bulk Insert Task is used when you need to transfer a large amount of data
residing in flat files onto a table in SQL Server 2005's database. In previous versions
of SQL Servers, this was the method of choice to transfer large amounts data rapidly,
utilizing an executable program called BCP.exe (Bulk Copy). This utility is also
available in SQL Server 2005 and works in either direction, from a SQL Server table
(view) to a data file in a user-specified format, or from a data file to a SQL Server
table (view). However, the Bulk Insert Task can only transfer from a flat file to a table
(view) in a SQL Server.
The Bulk Insert Task cannot be used with any of the data flow transformation
components, and in this respect it is not as versatile as a data flow task. This task has
been provided for backward compatibility.
Hands-On Exercise: Transferring
Data from a Flat File to a SQL Server
Database Table
In order to follow the steps as indicated, you will need access to a table in a database
on the SQL Server 2005 as well as knowledge of the nature (format) of the file and
its location.
Pages:
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100