Transform Data from Access to Temp tables (#)

Question

Transform Data from Access to Temp tables (#)

Sql Junkie-204042

Hall of Fame

Points: 3874
More actions
May 5, 2005 at 9:18 am

#60017

Here is the issue - I have a DTS package that need to transfer data from 4 Access tables to SQL, process the data using a SP and return a value. At the end, the Access data in SQL is not needed, so the tables need to be dropped. I prefer not to create permanent tables and drop it at the end of the process, as multiple users can be executing the package at the same time.
Can I use temp(#) tables to transfer data from Access to SQL using the "Transfer Data Task" ? Thanks in advance.

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

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

You can create "execute SQL task" that create a temp table with same structure as the Access table as precedence of transferring of Access data process.

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 2

How do I reference the temp(#) table in the 'Destination' tab of the Transform Data Task properties ?

Am I missing something ?

philcart SSC-Forever Points: 47795 More actions · Answer 3

You can't, the meta data is not available because the table hasn't been created.

I would create a permanent table and use the under utilised lineage variables to sort out which user owns the data. Check the topic "Recording Data Lineage in DTS" in Books Online as a starting point.

You could then pass the lineage information to a stored proc that doe the processing.

--------------------
Colt 45 - the original point and click interface

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 4

Yes you can with some tricks but you can only use global temp table. Create the the temp table from QA first, then create "execute SQL task" that create a temp table with same structure as the Access table as precedence of transferring of Access data process.

Try it.