Bulk Insert Task - SSIS

Creating Format File

bcp <DB Name>..<TableName> format nul -c -t, -x -f myTestFormatFile.Xml -T

ex: bcp Test..Employee format nul -c -t, -x -f myTestFormatFile.Xml -T

// If Database supports windows Authentication
bcp dbname.schema.Products format -c -t -f d:\test.fmt -T
// For Sql Authentication use id and password with -U and -P in place of -T
bcp dbname.schema.Products format -c -t -f d:\test.fmt -Uusername -Ppassword

Code pages

CODEPAGE value	Description
ACP	Columns of char, varchar, or text data type are converted from the ANSI/Microsoft Windows® code page (ISO 1252) to the SQL Server code page.
OEM (default)	Columns of char, varchar, or text data type are converted from the system OEM code page to the SQL Server code page.
RAW	No conversion from one code page to another occurs; this is the fastest option.
code_page	Specific code page number, for example, 850.

DATAFILETYPE [ = {'char' | 'native' | 'widechar' | 'widenative' } ]

Specifies that BULK INSERT performs the copy operation using the specified default.

DATAFILETYPE value	Description
char (default)	Performs the bulk copy operation from a data file containing character data.
native	Performs the bulk copy operation using the native (database) data types. The data file to load is created by bulk copying data from SQL Server using the bcp utility.
widechar	Performs the bulk copy operation from a data file containing Unicode characters.
widenative	Performs the same bulk copy operation as native, except char, varchar, and text columns are stored as Unicode in the data file. The data file to be loaded was created by bulk copying data from SQL Server using the bcp utility. This option offers a higher performance alternative to the widechar option, and is intended for transferring data from one computer running SQL Server to another by using a data file. Use this option when transferring data that contains ANSI extended characters in order to take advantage of native mode performance.