Migrating Data (.dat) from on-premise file system to SQL Azure

Jun 8, 2011 at 6:33 AM

First of all, thanks for delivering such a great tool to community :)

In the SSMW homepage, it's stated:

SQLAzureMWBatchUpload Project Details
The SQLAzureMWBatchUpload is a command line application that will run a TSQL script file (and use BCP to upload data) against a target server. SQLAzureMW and SQLAzureMWBatchBackup can be used to generate the TSQL script and the BCP files that can then be used by SQLAzureMWBatchUpload for uploading to a target server. The target server can be SQL Azure or SQL Server.

However, in the SQLAzureMWBatchUpload.exe.config, I cannot find the option / parameter that describe the BCP directory in our client machine.

There's a key FileToProcess but I think it's actually refer to TSQL file (.sql) which is more intend to schema.

Assuming that my data is kept in .dat, somewhere on-premise. How to export the data using SQLAzureMWBatchUpload?

Looking forward to hearing from you soon.

 

Coordinator
Jun 8, 2011 at 4:19 PM

Hi, thanks for the kind words!

 

For SQLAzureMWBatchUpload, the file that has all of the info is the file created by SQLAzureMW or SQLAzureMWBatchBackup.  All you have to do is point SQLAzureMWBatchUpload to the file i.e.:

 

<add key="FileToProcess" value="C:\SQLAzureMW\BCPData\23-May-2011 1601\HousingDB.sql"/>

 

If you open up the sql file (in my case HousingDB.sql), you will see toward the bottom a list of BCPArgs (commented out).  This is the actual BCP command, I just trap “-- BCPArgs:9:” and replace it with BCP.EXE.  Note that the 9 in this case shows the number of records in the file.  Example of sql file:

 

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[zctaState]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[zctaState](

       [state_code] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

       [state_name] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [fips_code] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

PRIMARY KEY CLUSTERED

(

       [state_code] ASC

)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)

)

END

GO

-- BCPArgs:9:[dbo].[AcquiredAssetNew] in "c:\SQLAzureMW\BCPData\23-May-2011 1601\dbo.AcquiredAssetNew.dat" -E -n -b 10000 -a 16384

GO

-- BCPArgs:4:[dbo].[route] in "c:\SQLAzureMW\BCPData\23-May-2011 1601\dbo.route.dat" -E -n -b 10000 -a 16384

GO

-- BCPArgs:24:[dbo].[site] in "c:\SQLAzureMW\BCPData\23-May-2011 1601\dbo.site.dat" -E -n -b 10000 -a 16384

 

You will note in the BCPArgs, that the pointer to your dat file is there.

 

Does this help?  Everything you need in schema and BCP commands should be in your saved SQL file.

 

Let me know if you have any questions.

 

Regards,

George

 

 

 


Jul 8, 2011 at 10:39 AM

Hi all,

I have the scripts generated by SQL Azure Migration Wizard v3.7.4 (tables and its data) and when i run the script on the new query tab on SQL Azure, only the tables are created.

I know that the BCP data is specifited in the end of the script and IS COMMENTED. I want to know how to copy this database data (.dat file) to the database on the SQL Azure.

I have tried performing
BCP.EXE.[dbo].[DB_Attachments] in "c:\SQLAzureMW\BCPData\dbo.DB_Attachments.dat" -E -n -b 10000 -a 1638

but it doesnt work.

Can you please let met know how you copy the data from the BCP file to the database on azure.