Database Reference
In-Depth Information
Other Cases to Consider
This example was simple; the source and destination tables were mirrors of each other, including
column names and data types. This made data migration easy. However, in some cases the source and
destination tables differ in column names and data types. There are tasks that help with this, such as the
Derived Column, Data Conversion, and Lookup tasks. If you're using these tasks and are getting errors,
start by looking at these tasks to make sure they aren't the source of data-truncation or data-conversion
errors.
Again, this section isn't intended to be an SSIS primer. Great topics about SSIS are available that
focus on beginner topics all the way to advanced topics. Brian Knight is a SQL Server MVP who has
written numerous topics on SSIS; his topics are highly recommended if you're looking for SSIS
information and instruction.So far we have talked about SSIS and the SQL Server Generate and Publish
Scripts wizard which both offer viable options for migrating your data, but with little differences. For
example, SSIS doesn't migrate schema while the Scripts wizard does. Let's talk about the third tool, Bcp,
which also provides a method for migrating data to SQL Azure.
Bcp
The bcp utility provides bulk copying of data between instances of Microsoft SQL Server. This utility is
installed with SQL Server and requires no knowledge or understanding of T-SQL syntax. If you aren't
familiar with the bcp utility, don't confuse or associate its functionality with that of the Import/Export
Wizard in SQL Server. Although the bcp documentation refers to what bcp does as a “bulk copy,” be
aware that you can't bcp data from a source into a destination with a single statement. You must first
bcp the data out of the source; then, you can bcp the data in to the destination.
Note
The bcp utility is very flexible and powerful, and you can apply a lot of options to it. This section doesn't
go into the entire range of bcp options or dive deep into the many uses of the utility. You can find that information
in the SQL Server Books Online or on the Microsoft MSDN web site at
http://msdn.microsoft.com/en-
us/library/ms162802.aspx
.
This section describe show to use the bcp utility to export data from a local database and import the
data into your SQL Azure database. It also discusses some things you should watch out for when using
the bcp utility for SQL Azure.
Invoking BCP
The bcp utility has no GUI; it's a command prompt-driven utility. But don't let that intimidate you,
especially given what you're using it for. It's very flexible and can seem a bit overwhelming, but it's quite
simple. The basic syntax for the bcp utility is as follows:
bcp
table
direction
filename
-
servername
-
username
-
password
where:
table
is the source or destination table based on the direction parameter.
•
