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 a text file without commas to an sql table

how i can import a text file without commas to an sql table, knowing the
subject of every clomn in the text file (col 1 to col 8- name, col 9 to col
15 - address e.c.)?If you are using DTS, you have to specify the file type as 'fixed width',
and then DTS lets you specify the position of each column. When using BULK
INSERT, you'll have to create a format file that describes the columns.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sam" <focus10@.zahav.net.il> wrote in message
news:%23vvM3nejFHA.1372@.TK2MSFTNGP10.phx.gbl...
how i can import a text file without commas to an sql table, knowing the
subject of every clomn in the text file (col 1 to col 8- name, col 9 to col
15 - address e.c.)?|||Are you using some other delimiter instead of commas? BCP and DTS let
you configure the delimiter character.
If it's non-delimited then I assume each column is a fixed width. The
text file source driver supports fixed width columns too. In the DTS
designer select the connection properties dialog to configure the
column positions.
David Portas
SQL Server MVP
--|||Why not use the files through a linked server to text files?
More info here:
http://msdn.microsoft.com/library/d...r />
_6a44.asp
And of course *you* have the power to describe, rather to transform your
text files:
http://msdn.microsoft.com/library/d...ma_ini_file.asp
After establishing links to your text files, you can query them with T-SQL
as you would any other SQL table.
ML