import from Access to SQL, not knowing the table format

I need to import few tables from MS Access to MS SQL but the table structure in Access is always different, as I would like the destination table in SQL to be.

Therefore I would like that a table would be created in SQL at runtime, according to the structure the Access table accessed has.

You can't do this using a data-flow because for these you need to know the metadata of the source and destination at design-time and according to your post, you don't know that!

I don't know much about Access. Is there a way of interrogating the metadata at design-time? If so you could get that metadata (hopefully using an Execute SQL Task) and use that to build your data-flow programatically at runtime. That's a difficult thing to do though. If you really want to go down this route then there's some stuff in BOL to help you.

-Jamie

|||OK, got the point.
Just to be clear, I wuold like to do something like

select * into <table destination> from <table source>

But I cannot because the source is Access, the destination is SQL Server 64 bit and there is no MS Jet driver for 64 bit.

Anybody has a good idea?
|||

The only way to do this with a SELECT...INTO... is to set the Access mdb up as a linked server.

-Jamie

|||

srem wrote:

But I cannot because the source is Access, the destination is SQL Server 64 bit and there is no MS Jet driver for 64 bit.

You can still run this on a 64 bit machine, just call it through the 32 bit dtexec, see the Program Files x86 folder.

I think your bigger issue is the lack of metadata up front, as Jamie points out.

|||i'm not sure about this, but i think you can use the script task to determine the access table schema. then, you could use this schema information to dynamically create the sql server table.

import from Access to SQL, not knowing the table format

I need to import few tables from MS Access to MS SQL but the table structure in Access is always different, as I would like the destination table in SQL to be.

Therefore I would like that a table would be created in SQL at runtime, according to the structure the Access table accessed has.

You can't do this using a data-flow because for these you need to know the metadata of the source and destination at design-time and according to your post, you don't know that!

I don't know much about Access. Is there a way of interrogating the metadata at design-time? If so you could get that metadata (hopefully using an Execute SQL Task) and use that to build your data-flow programatically at runtime. That's a difficult thing to do though. If you really want to go down this route then there's some stuff in BOL to help you.

-Jamie

|||OK, got the point.
Just to be clear, I wuold like to do something like

select * into <table destination> from <table source>

But I cannot because the source is Access, the destination is SQL Server 64 bit and there is no MS Jet driver for 64 bit.

Anybody has a good idea?|||

The only way to do this with a SELECT...INTO... is to set the Access mdb up as a linked server.

-Jamie

|||

srem wrote:

But I cannot because the source is Access, the destination is SQL Server 64 bit and there is no MS Jet driver for 64 bit.

You can still run this on a 64 bit machine, just call it through the 32 bit dtexec, see the Program Files x86 folder.

I think your bigger issue is the lack of metadata up front, as Jamie points out.

|||i'm not sure about this, but i think you can use the script task to determine the access table schema. then, you could use this schema information to dynamically create the sql server table.

Import data error while delete rows in the destination table

Both SQLServer2005, different server, import tableA from serverA to serverB.
When we import tableA for the first time, serverB will get the whole
structure and data of tableA. So far is fine. By the way, we need to change
the type of a column from varchar to nvarchar.
Second time, we want to save the original structure and delete the exist data.
So we choose the "delete rows in the destination table" property. But when
we execute this, there is an error below while validation.

/******************
Error: 0xC02020F4 at Data Flow Task: The column "dbname" cannot be processed
because more than one code page (936 and 1252) are specified for it.
******************/

All the columns in this table have the same errors.
I don't know why. Thank you.

If you made this operation in a SSIS package to work , you have to change the property AlwaysUseDefaultCodepage of a OLEDB Source in a Data Flow Task . The value have to be "false"