Post reply

DTS : SQL to Access with unknow source table

  • March 10, 2005 at 10:27 am

    #166322

    Hi,

    I got two database working together. One server as temporary holder for the data to be imported inside the main database. The other is the production database. The data is imported inside the temporary holder database as a table itself and then the table is processed later on.

    I have to do some kind of recurring process that will take all table that have been processed and are older than x day and dump then in access mdb for history purpose.

    I researched about the linked server to see what was the possibility it could offer me. And without the possibility of doing a select into, I forgot that alternative.

    I am now trying to do it through DTS and T-SQL script. I got the script selecting the table to be backup in the mdb. I got the DTS working for the first table. If I try to pass it other table using global variable, the DTS crash.

    What I think it is, is that when i created the DTS, i submitted the first table to be put in the mdb which create at the same time the table. When I pass other table to it, it does dynamicly create the table, thus making it crash with a table not found at the destination.

    While doing the DTS, I had to take into consideration that the table layout is not always the same. That it might not always be the same table.  And, that the mdb might not always be the same.

    That's why I invoke the DTS package using cmdshell, and passing global variable value though the cmd line.

    So, in short, I want to know, is there any way to make the DTS create the destination table without, me, knowing in advance, what will be the layout of the destination table?

    If any of you have any sugestion or comments about my problem, feel free to reply.

    Thanks

    Warlockzzz

  • March 14, 2005 at 8:00 am

    This was removed by the editor as SPAM

  • March 15, 2005 at 8:36 am

    #547076

    That's a good idea. In a way, I have been going that way. I have been trying to generate the sql stament that should be use to create the table on Access side. Hoping that sql server would adjust it's transformation, would he?

    Trying to adjust my vb coding to the one of sql has been a pain, ill use your idea to get an idea on how sql expect the coding and addapt my code to make it fit in there.

    I think I can make through the 'dark and mysterious ActiveX Scripting'.

    BTW, i like the 'dark and mysterious ActiveX Scripting' part.

    Thanks

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

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