May 1, 2015 at 3:15 pm
I have a T-SQL Statement Task to create table every time the package run. It doesn't return error but it doesn't drop the table either. The data is appended every time. The code is working fine in SQL server query window. Can you see what is wrong in the code below? TIA
IF OBJECT_ID (N'M020_Vendor', N'U') IS NOT NULL
DROP TABLE M020_Vendor
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [M020_Vendor]
( list of fields
)
GO
May 1, 2015 at 3:22 pm
1) Have you checked that the statement is running in the right database?
2) Why not truncate the table rather than drop/create?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 1, 2015 at 3:37 pm
Code seems fine - have you inadvertently disabled that script task? Possibly just running the subsequent inserts resulting in appended data?
May 2, 2015 at 4:55 am
Quick thought, schema qualify the table name by adding the schema name + dot prefix.
😎
May 3, 2015 at 3:55 am
check SQLAgent account permissions
May 7, 2015 at 10:45 am
Thank you for all the replies.
The code was working in SQL query.
I wanted to drop the table because the table schema may change so I only need to update it in this package and don't need to open up SQL server to delete the table.
I took me many days to find out that the T-SQL statement task connection wasn't connected to the database I want it to create table so I have been drop and create table in master database. I didn't check this connection as I thought I had it set up correctly.
May 7, 2015 at 1:19 pm
____ KD (5/7/2015)
Thank you for all the replies.The code was working in SQL query.
I wanted to drop the table because the table schema may change so I only need to update it in this package and don't need to open up SQL server to delete the table.
I took me many days to find out that the T-SQL statement task connection wasn't connected to the database I want it to create table so I have been drop and create table in master database. I didn't check this connection as I thought I had it set up correctly.
This is normally referred to as the <Head><Desk> loop:-D
😎
May 7, 2015 at 1:30 pm
It was also the first question in the first response!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply