SQL DTS Transform Task - new output table

[JasReedman]

Hi,

I am building a series of small dts packages of the form:
1. Drop work Table n
2. Transform, (by query), a previous table, (it exists), into the work Table n

The intention is to then create a master DTS which runs the small DTS packages in the correct order. In principle I have done this before, (from an existing table into a truncated existing table)

DTS sometimes allows this, and sometimes fails, with an invalid object even if the table does not exist.

I have tried the following series of table to table names:
400 => 400.1: worked once now stops with invalid object
400 => T400.1: worked then ditto
400 => T400_1: worked then ditto - then T400_1 => T400_2: worked but then original stopped working
400 = > 400A: worked but then 400A => 400B invalid object

Any ideas welcome

[iburyak]

Try to name your tables with alphanumeric starting with a letter.

[JasReedman]

Try to name your tables with alphanumeric starting with a letter.

Thanks for the suggestion but I already tried with T400.1 and T400.2 with similar problems.

I have got the 1st two packages to work using Truncate rather than Drop for the work tables.
I did not want that to be the solution because if anyone removes a work table it has to be reset up in its correct format for the Truncate and transformation to work time after time.

I will continue to look for a solution but my current thoughts are to do with the persistance of stored data within DTS - i.e. perhaps needing things to be refreshed? but I think I've tried that already.
Or, perhaps it is something to do with the period in the Table name causing DTS to get confused?

[iburyak]

Try to do this

[PHP]
Drop table table_name
go

your code here. [/PHP]

GO will make system finish first statement before moving to next.

I would change [.] to [_] in table name.

Good Luck.

[JasReedman]

Try to do this

[PHP]
Drop table table_name
go

your code here. [/PHP]

GO will make system finish first statement before moving to next.

I would change [.] to [_] in table name.

Good Luck.

I will try - thanks

[JasReedman]

Hi,

I am building a series of small dts packages of the form:
1. Drop work Table n
2. Transform, (by query), a previous table, (it exists), into the work Table n

The intention is to then create a master DTS which runs the small DTS packages in the correct order. In principle I have done this before, (from an existing table into a truncated existing table)

DTS sometimes allows this, and sometimes fails, with an invalid object even if the table does not exist.

I have tried the following series of table to table names:
400 => 400.1: worked once now stops with invalid object
400 => T400.1: worked then ditto
400 => T400_1: worked then ditto - then T400_1 => T400_2: worked but then original stopped working
400 = > 400A: worked but then 400A => 400B invalid object

Any ideas welcome

Apologies - I did not say that the problem occurs within the DTS Design Package and that I use a Workflow connection to sequence the events using the 'On Success' mode.

I hope there is someone out there that can help in this niche area.

Thanks again - John

[JasReedman]

Hi,

I am building a series of small dts packages of the form:
1. Drop work Table n
2. Transform, (by query), a previous table, (it exists), into the work Table n

The intention is to then create a master DTS which runs the small DTS packages in the correct order. In principle I have done this before, (from an existing table into a truncated existing table)

DTS sometimes allows this, and sometimes fails, with an invalid object even if the table does not exist.

I have tried the following series of table to table names:
400 => 400.1: worked once now stops with invalid object
400 => T400.1: worked then ditto
400 => T400_1: worked then ditto - then T400_1 => T400_2: worked but then original stopped working
400 = > 400A: worked but then 400A => 400B invalid object

Any ideas welcome

SOLUTION:

1.Click on any Table Object then All Tasks & Generate SQL Script

2, Save the default script to a location of your choice
(you now have the SQL to Drop the Table you identified, and also,
Create the equivalent table.)
3. In the DTS Design Package the first step, (which should be an 'Execute SQLTask'), can now be copied from the SQL Script to
(a) Drop the Table, and then,
(b) Recreate the table

The Script contains GO commands - don't worry - just copy the lot in.

Your New task will now effectively flush out the old copy and recreate a blank copy , allowing the following Transform Task to populate the empty table with the required data.