Export SQL Data to Access mdb Database

Question

Post reply

Export SQL Data to Access mdb Database

swendrich

SSChasing Mays

Points: 607
More actions
June 29, 2010 at 9:25 am

#141142

Hi,
i will export some tables from an sql 2008 with SSIS to an access db. But the access db don't exist.
is it possible, that SSIS creates the mdb database and write down the data from the sql server ?
With an Excel oder Flat file it runs perfectly, but i don't found the option for access database.
Can someonne help me ?
Kind regards
Stefan

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

You can create one on the fly but it requires more steps than what I am going to recommend..

1. Create an empty mdb file with all the structures you need. This is effectively a template.

2. Copy that file to a new name with your package.

3. Use the new file in the package.

CEWII

swendrich SSChasing Mays Points: 607 More actions · Answer 2

Hi thanks for the answer.

But i have the Problem, that i must export 20 tables with 50 columns.

So the work, to create a blank mdb table is very much.

How can i create those mdb database automatically ?

Kind regards

Stefan

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 3

Not really automatically, but if you already have the structure of the tables in a database (ie you are copying column for column from existing source) you can generate the code to create those tables then modify it for Access and then run it in access to create the tables. Not easy but a lot less difficult to do than creating them all by hand.

As far as the modifying the script that is really about changing SQL datatypes to Access, such as varchar usually becomes string.

If you have specific examples where you need help let me know.

CEWII

swendrich SSChasing Mays Points: 607 More actions · Answer 4

thanks for the help.

i searched a lot, and i found out, how i can create a mdb database.

I have to write a active x script, or use the ADOX component in a vb .net script.

is that right?

That's a shame, that ssis is not in the position to export some sql data automatically in a access db.

Thanks for help.

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 5

I think the problem you'll find is you still need to add the structure to the MDB file, creating the file is nothing, the dataflow has to have something to go into.

I did have a thought, have you considered using the import/export wizard and then saving the package? You could probably do most of your work that way..

CEWII

swendrich SSChasing Mays Points: 607 More actions · Answer 6

yes i have considered the import/export wizard.

But you are right. The problem is to create the structure in the mdb file.

I think, i can do a SELECT COLUMN_NAME FROM InformationenSchema.Columns WHERE table_name = "tabelle"

and then create with this data with CREATE TABLE the structure in the mdb file.

But is that right, that i only can create a mdb file with vb script ?

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 7

swendrich (6/29/2010)
yes i have considered the import/export wizard.
But you are right. The problem is to create the structure in the mdb file.
I think, i can do a SELECT COLUMN_NAME FROM InformationenSchema.Columns WHERE table_name = "tabelle"
and then create with this data with CREATE TABLE the structure in the mdb file.
But is that right, that i only can create a mdb file with vb script ?

Using the information schema will give you the names of the columns but it would be easier to just select all the tables from the the list of tables in that database in the Object Exporer Details window of SSMS and then right click and select Script Table As and then select Create To and then select New Query Editor window. That would give you a CREATE TABLE statement with datatypes that just need to be modified for Access.

The reason I suggested the import/export wizard was that I think the wizard would handle the datatype conversions for you. You just have to have an empty MDB to start with. I also know that there are tools to convert an MDB into a VBScript if you REALLY want to create the entire file on the fly.

As far as that script, it has to have something to create the structure from so you are going to have to do some work to get the tables created either way. I don't know exactly what script you are using but creation of the file itself is not difficult.

CEWII