Post reply

Import into a temp table in DTS

  • November 12, 2007 at 8:38 pm

    #70996

    Hi

    I was wondering whether i can import data from another server into a temp table ie #[Table Name] using a Transform Data Task between 2 connections. The reason i want to do this is because on Prod i don't have create permissions, so i cant create then drop the table. I want to create a temp table then fill the table using Transform Data Task and then access the temp table from other SP's in the DTS. DBA's won't create linked servers, or enable us to use OPENROWSET,OPENQUERY etc.

    Cheers

  • November 13, 2007 at 7:54 am

    #750697

    You cannot use a # temporary table - the table would be created in one object but dropped when DTS moves onto the next object as the connection is broken and # tables persist only during the connection session in which they are created.

    Why are you doing this on the prod server? Surely you should be developing this on a dev/test server and then migrate the package and objects into live. In the live environment you could execute the pacakge from an SQL Job running with a different level of permissions allowing it to create and then drop a table. Or you could get the DBAs to create the table and leave it there permanently and all you have to do is ensure that the table is empty before you import any data (you might want to clear the table at the end of the package as well).

    Alternatively, have you tried creating the table in tempdb rather than a # temporary table? This table will persist until SQL Server is restarted or explicitly dropped.

    Jez

  • November 13, 2007 at 3:17 pm

    #750862

    Thanks for the reply

    I'll have them set up the table i think as anything outside of the norm will send IT into a spin.

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

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