SSIS Import Export Wizard : Change the Default 50 Character DT_STR Column Mapping Size

Importing CSV files through the SSIS Import Export Wizard, will default string data to 50 characters. To override the default mappings you must click Advanced, then painfully go through and override each column mapping one by one from 50 to a larger size. Tedious when you have lots and lots of columns. A suggested workaround based on a 50 to 250 character expansion for string data (DT_STR) – Note for Unicode (DT_WSTR) you will need to adapt this example :

1. Run through the Import Export Wizard selecting your CSV source and SQL Server Destination, leaving the default size for column mappings as 50 characters
2. Get to the last screen, Save the DTSX package e.g. to D:\YourDTSXPackage.dtsx but do not execute
3. Locate the dtsx file on disk, open in Notepad or other. We want to change the default size from 50 to say…250 characters…
4. Perform a Find and Replace changing string pattern >50< to >250<
5. Perform a second Find and Replace changing string pattern varchar(50) to varchar(250) to catch the data types specified in the CREATE TABLE command
6. Perform a third Find and Replace changing string pattern length=”50″ to length=”250″ to catch the lineages
7. Open a command prompt and run your package using DTEXEC e.g. dtexec /f “D:\YourDTSXPackage.dtsx”
8. Keep an eye on the command prompt to make sure you don’t run into any errors. When the package is finished you will see this The package execution returned DTSER_SUCCESS (0). towards the end of the output and the DTEXEC process will return to the command prompt.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s