August 21, 2003 at 12:34 am
hi,
when i make a DTS package and run it once it works ok..but when i run it the second time it gives invalid table name altough i define the table name in the create statement and save it but i still get this problem when ever i run the package..what should i do about this any suggestions.
i would be very glad.thnx
August 21, 2003 at 11:56 am
Make sure you are not trying to re-create the table when it already exists from the first time you ran the package.
August 22, 2003 at 5:25 am
What exactly are you doing in the package. I've run into similar problems in trying to output data to an Excel file. Using Active X object, I've checked for the existence of the table, if exist, drop and re-create, if not exists, create, then execute transformation.
August 22, 2003 at 9:49 am
If you are fighting with Excel, this doesn't have error trapping for when the table DOES NOT exist but (if you're not too concerned about that scenario happening), this works pretty well without activex...
Using normal DTS Execute SQL Tasks (connected with On Success workflow):
Task 1
a) Set existing connection to your XLS connection
b) Drop table mytablename
(Refer to tablename box on destination tab on transform data task. This actually drops the table from within excel which is actually a 'defined name' space in excel speak)
Task 2
a) Set existing connection to your XLS connection
b) CREATE TABLE `mytablename` (
`Column1` VarChar (30) ,
`Column2` VarChar (40) ,
`Column3` VarChar (3) )
(Copy your create table statement from your transform data task, destination tab, Create button.) Connect Task 2 to your SQL connection and continue on with transformation like normal.
Good luck!
August 22, 2003 at 3:12 pm
Don't create the table from within DTS. copy the "create" script and run in Query Analyser. Then you can just choose the table; shouldn't have any problems that way.
Signature is NULL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply