Import from Access - Datatype conversions

I have to do a lot of inporting from Access files. Is there a place where I can change the default datatype conversion for Access Text from nvarchar to varchar?

Thanks.

Kato

If you are using the import wizard then you can affect this with the mapping files at:

%PROGRAMFILES%\Microsoft SQL Server\90\DTS\MappingFiles

I'm guessing that the one you want is JetToSSIS.xml

If you are building packages manually then you can change this in the source adapter.

-Jamie

|||

Outstanding. Exactly what I was looking for.

Thank you.

sql

Import Export in SQL Server 2005

In case of Sql Server 2000, we have the option for import / Export data using DTS by creating objects.

Doing this we are getting the default value of colms of tables int the destination database.

But in case of SQL Server 2005 i am not getting this feature.

If i am using the Import /Export I am not getting the default values.

So first i am generating the script for table and create table in the destination database and then using import/export option to copy data.

Is there any other option to do this?

DEFAULT values are elements of the table schema. If you are not getting the default values, there are two possible explanitions.

1. Your script that creates the tables does not include the DEFAULT values.

2. Your data has empty strings instead of NULL values (DEFAULT values are ONLY used if the data is missing or NULL.)

|||

Yah what you are saying is right.

But what i am asking is , by default i am not getting default values while using import/ export to a new data base.

For that i am first generating script for tables and the table syntax include the default. and then using import/export data.

Is it possible, with out generating the script for tables from the existing database will get the default value in the new database.

|||Yes it is possible -but ONLY if the missing data is [NULL], NOT empty string [''].|||

You need to use the SQL Server specific version of import/export, look here for more info;

http://blogs.msdn.com/euanga/archive/2006/07/18/668916.aspx

sql

Import Export in SQL Server 2005

In case of Sql Server 2000, we have the option for import / Export data using DTS by creating objects.

Doing this we are getting the default value of colms of tables int the destination database.

But in case of SQL Server 2005 i am not getting this feature.

If i am using the Import /Export I am not getting the default values.

So first i am generating the script for table and create table in the destination database and then using import/export option to copy data.

Is there any other option to do this?

DEFAULT values are elements of the table schema. If you are not getting the default values, there are two possible explanitions.

1. Your script that creates the tables does not include the DEFAULT values.

2. Your data has empty strings instead of NULL values (DEFAULT values are ONLY used if the data is missing or NULL.)

|||

Yah what you are saying is right.

But what i am asking is , by default i am not getting default values while using import/ export to a new data base.

For that i am first generating script for tables and the table syntax include the default. and then using import/export data.

Is it possible, with out generating the script for tables from the existing database will get the default value in the new database.

|||Yes it is possible -but ONLY if the missing data is [NULL], NOT empty string [''].|||

You need to use the SQL Server specific version of import/export, look here for more info;

http://blogs.msdn.com/euanga/archive/2006/07/18/668916.aspx