Copying data frm Sql Server to MS Access

Question

Copying data frm Sql Server to MS Access

burak30

SSC Rookie

Points: 44
More actions
May 18, 2010 at 9:21 am

#72838

Hello,
I want to create a DTS package that will receive 6 bit parameters and based on those parameters, it will copy any number of 6 Sql Server tables to an MS Access database.
How can I do this?
Thank you,
Burak

Viewing 4 posts - 1 through 3 (of 3 total)

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

Tom Carnahan SSCommitted Points: 1788 More actions · Answer 1

Its not clear to me what part of this problem you are having difficulty with. Is it the handling of parameters in DTS? Is it exporting to Access.

If its the former, you might look at SQLDTS.com. To start, look at , then use their search feature and look for info on "global variables". Using global variables is the way to change the flow of a DTS process dynamically.

Are you trying to interactively decide which file to export to? If not, what values are you examining to determine the flow?

burak30 SSC Rookie Points: 44 More actions · Answer 2

Hello Tom,

I'm having trouble writing the equivalent sql query for this pseudo code

if global_variable1 = value1

export table1 to access_table1

if global_variable2 = value2

export table2 to access_table2

I am using sql server 2000. I don't want to use ActiveX Script tasks since we will be migrating this DTS package to Sql Server 2008 in the near future and I don't want to go in and change the code again.

Any suggestions?

PaVeRa22 SSCommitted Points: 1965 More actions · Answer 3

Hi burak30,

Pls follow the steps

1. Create source connection (SQL Server)

2. Create Destination (Access DB)

you can achieve in two ways

i. Create 6 DataFlow tasks and enable/disable required tasks, each DFTask must contain table structure to hold data. map source and struture u created.

ii. if you are storing all the queries in variales, you can use for loop task 6 times, you have to create table structure first dynamically then DFtask will take of data pumping.

I just tried in SQL 2008, its working

regards

RB