SSIS Execute SQL Task SQL Statement Build Query problem

[scooterny]

Folks,

I am new to SQL Server 2005 and SSIS. I am trying to recreate and amend at DTS Package someone else created in SQL Server 2000. This step is the first step in the package and involves the dropping and creating of a table. This code worked fine in the old DTS Package and it works fine if I execute it as a query in SSMS. The issue, I think, is that the SSIS SQL Statement Build Query editor, in the Execute SQL Task Editor, condences the code. It seems to be an error in the "GO" statement and there are several of them in this script. The error statement I am getting is "Error: Incorrect syntax near 'GO'". When I take the query and put it in SSMS and put the word "go" on its own line, no compaints. SSIS, no matter how much I finagle, will not allow me to save an edited version of the query with "go" on its' own line. I suppose I could put each statement in the script in its own Execute SQL Task but that seems counterintuitive to what I should have to do. Here is an example of the code in SSIS that won't work:

IF EXISTS (SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[tblANEXC]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[tblANEXC] GO USE master go EXEC sp_dboption AA_DW, 'select into/bulkcopy',
true go USE AA_DW go CREATE TABLE tblANEXC(RptDate char(10) NULL, Bank int, Ades char(1) NULL, AcctNo numeric(18, 0), Appl int, ECBaseCode char(5) NULL,
ECFactor char(1) NULL, ECRate float, ECExpirationDate char(10) NULL, ECMonth char(1) NULL, ECYear char(1) NULL, ODIntBaseCode char(5) NULL,
ODIntFactor char(1) NULL, ODIntRate float, ODIntExpirationDate char(10) NULL, ODIntMonth char(1) NULL, ODIntYear char(1) NULL, CFBaseCode char(5) NULL,
CFFactor char(1) NULL, CFRate float, CFExpDate char(10) NULL, CFMonth char(1) NULL, CFYear char(1) NULL, SCBaseCode char(5) NULL, SCFactor char(1) NULL,
SCRate float, SCExpirationDate char(10) NULL, SCMonth char(1) NULL, SCYear char(1) NULL, INTBaseCD char(5) NULL, INTFactor char(1) NULL, INTRate float,
INTExpirationDate char(10) NULL, INTMonth char(1) NULL, INTYear char(1) NULL, RESBaseCode char(5) NULL, RESFactor char(1) NULL, RESRate float,
RESExpirationDate char(10) NULL, PROFITOBJBaseCode char(5) NULL, PROFITOBJFactor char(1) NULL, PROFITOBJRate float,
PROFITOBJExpirationDate char(10) NULL, CompBalDeficitBaseCode char(5) NULL, CompBalDeficitFactor char(1) NULL, CompBalDeficitRate float,
CompBalDeficitExpirationDate char(10) NULL, CompBalDeficitMonth char(1) NULL, CompBalDeficitYear char(1) NULL, FDICBaseCode char(5) NULL,
FDICFactor char(1) NULL, FDICRate float, FDICExpirationDate char(10) NULL, FDICMonth char(1) NULL, FDICYear char(1) NULL, ALT1ECBaseCode char(5) NULL,
ALT1ECFactor char(1) NULL, ALT1ECRate float, ALT1ECExpirationDate char(10) NULL, ALT1ECBalance money, ALT2ECBase_Code char(5) NULL,
ALT2ECFactor char(1) NULL, ALT2ECRate float, RSV1 char(5) NULL, ALT2ECBalance money, ALT1IntBaseCode char(5) NULL, ALT1IntFactor char(1) NULL,
ALT1IntRate float, ALT1IntExpirationDate char(10) NULL, ALT1IntBalance money, ALT2IntBaseCode char(5) NULL, ALT2IntFactor char(1) NULL, ALT2IntRate float,
RSV2 char(5) NULL, ALT2IntBalance money, CompRsvBaseCode char(5) NULL, CompRsvFactor char(1) NULL, CompRsvRate float,
CompRsvExpirationDate char(10) NULL, MaintenanceDate char(10) NULL, ALT3ECBASECD char(5) NULL, ALT3ECFACTOR char(1) NULL, ALT3ECVAR3 float,
ALT3ECBAL money, ALT4ECBASECD char(5) NULL, ALT4ECFACTOR char(1) NULL, ALT4ECVAR3 float, ALT4ECBAL money, ALT5ECBASECD char(5) NULL,
ALT5ECFACTOR char(1) NULL, ALT5ECVAR3 float, ALT5ECBAL money, ALT6ECBASECD char(5) NULL, ALT6ECFACTOR char(5) NULL, ALT6ECVAR3 float,
ALT6ECBAL money, ALT7ECBASECD char(5) NULL, ALT7ECFACTOR char(1) NULL, ALT7ECVAR3 float, ALT7ECBAL money, ALT8ECBASECD char(5) NULL,
ALT8ECFACTOR char(1) NULL, ALT8ECVAR3 float, ALT8ECBAL money, ALT9ECBASECD char(5) NULL, ALT9ECFACTOR char(1) NULL, ALT9ECVAR3 float,
ALT9ECBAL money, ALT10ECBASECD char(5) NULL, ALT10ECFACTOR char(1) NULL, ALT10ECVAR3 float, ALT10ECBAL money, ALT11ECBASECD char(5) NULL,
ALT11ECFACTOR char(1) NULL, ALT11ECVAR3 float, ALT11ECBAL money, ECCEIL3 float, ECFLOOR3 float, ODCEIL3 float, ODFLOOR3 float, SCCEIL3 float,
SCFLOOR3 float, INTCEIL3 float, INTFLOOR3 float, RSVCEIL3 float, RSVFLOOR3 float, COMPDEFCEIL3 float, COMPDEFFLOOR3 float, FDICCEIL3 float,
FDICFLOOR3 float, ALT1ECCEIL3 float, ALT1ECFLOOR3 float, ALT2ECCEIL3 float, ALT2ECFLOOR3 float, ALT3ECCEIL3 float, ALT3ECFLOOR3 float,
ALT4ECCEIL3 float, ALT4ECFLOOR3 float, ALT5ECCEIL3 float, ALT5ECFLOOR3 float, ALT6ECCEIL3 float, ALT6ECFLOOR3 float, ALT7ECCEIL3 float,
ALT7ECFLOOR3 float, ALT8ECCEIL3 float, ALT8ECFLOOR3 float, ALT9ECCEIL3 float, ALT9ECFLOOR3 float, ALT10ECCEIL3 float, ALT10ECFLOOR3 float,
ALT11ECCEIL3 float, ALT11ECFLOOR3 float, ALT1INTCEIL3 float, ALT1INTFLOOR3 float, ALT2INTCEIL3 float, ALT2INTFLOOR3 float, COMPRSVCEIL3 float,
COMPRSVFLOOR3 float, UPD_TIME char(10) NULL, UPD_USER char(8) NULL, UPD_ORG char(6) NULL) go

Here is the updated code is SSMS that does work:

IF EXISTS (SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[tblANEXC]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[tblANEXC]
GO

USE master
go

EXEC sp_dboption AA_DW, 'select into/bulkcopy', true
go

USE AA_DW
go

CREATE TABLE tblANEXC(RptDate char(10) NULL, Bank int, Ades char(1) NULL, AcctNo numeric(18, 0), Appl int, ECBaseCode char(5) NULL,
ECFactor char(1) NULL, ECRate float, ECExpirationDate char(10) NULL, ECMonth char(1) NULL, ECYear char(1) NULL, ODIntBaseCode char(5) NULL,
ODIntFactor char(1) NULL, ODIntRate float, ODIntExpirationDate char(10) NULL, ODIntMonth char(1) NULL, ODIntYear char(1) NULL, CFBaseCode char(5) NULL,
CFFactor char(1) NULL, CFRate float, CFExpDate char(10) NULL, CFMonth char(1) NULL, CFYear char(1) NULL, SCBaseCode char(5) NULL, SCFactor char(1) NULL,
SCRate float, SCExpirationDate char(10) NULL, SCMonth char(1) NULL, SCYear char(1) NULL, INTBaseCD char(5) NULL, INTFactor char(1) NULL, INTRate float,
INTExpirationDate char(10) NULL, INTMonth char(1) NULL, INTYear char(1) NULL, RESBaseCode char(5) NULL, RESFactor char(1) NULL, RESRate float,
RESExpirationDate char(10) NULL, PROFITOBJBaseCode char(5) NULL, PROFITOBJFactor char(1) NULL, PROFITOBJRate float,
PROFITOBJExpirationDate char(10) NULL, CompBalDeficitBaseCode char(5) NULL, CompBalDeficitFactor char(1) NULL, CompBalDeficitRate float,
CompBalDeficitExpirationDate char(10) NULL, CompBalDeficitMonth char(1) NULL, CompBalDeficitYear char(1) NULL, FDICBaseCode char(5) NULL,
FDICFactor char(1) NULL, FDICRate float, FDICExpirationDate char(10) NULL, FDICMonth char(1) NULL, FDICYear char(1) NULL, ALT1ECBaseCode char(5) NULL,
ALT1ECFactor char(1) NULL, ALT1ECRate float, ALT1ECExpirationDate char(10) NULL, ALT1ECBalance money, ALT2ECBase_Code char(5) NULL,
ALT2ECFactor char(1) NULL, ALT2ECRate float, RSV1 char(5) NULL, ALT2ECBalance money, ALT1IntBaseCode char(5) NULL, ALT1IntFactor char(1) NULL,
ALT1IntRate float, ALT1IntExpirationDate char(10) NULL, ALT1IntBalance money, ALT2IntBaseCode char(5) NULL, ALT2IntFactor char(1) NULL, ALT2IntRate float,
RSV2 char(5) NULL, ALT2IntBalance money, CompRsvBaseCode char(5) NULL, CompRsvFactor char(1) NULL, CompRsvRate float,
CompRsvExpirationDate char(10) NULL, MaintenanceDate char(10) NULL, ALT3ECBASECD char(5) NULL, ALT3ECFACTOR char(1) NULL, ALT3ECVAR3 float,
ALT3ECBAL money, ALT4ECBASECD char(5) NULL, ALT4ECFACTOR char(1) NULL, ALT4ECVAR3 float, ALT4ECBAL money, ALT5ECBASECD char(5) NULL,
ALT5ECFACTOR char(1) NULL, ALT5ECVAR3 float, ALT5ECBAL money, ALT6ECBASECD char(5) NULL, ALT6ECFACTOR char(5) NULL, ALT6ECVAR3 float,
ALT6ECBAL money, ALT7ECBASECD char(5) NULL, ALT7ECFACTOR char(1) NULL, ALT7ECVAR3 float, ALT7ECBAL money, ALT8ECBASECD char(5) NULL,
ALT8ECFACTOR char(1) NULL, ALT8ECVAR3 float, ALT8ECBAL money, ALT9ECBASECD char(5) NULL, ALT9ECFACTOR char(1) NULL, ALT9ECVAR3 float,
ALT9ECBAL money, ALT10ECBASECD char(5) NULL, ALT10ECFACTOR char(1) NULL, ALT10ECVAR3 float, ALT10ECBAL money, ALT11ECBASECD char(5) NULL,
ALT11ECFACTOR char(1) NULL, ALT11ECVAR3 float, ALT11ECBAL money, ECCEIL3 float, ECFLOOR3 float, ODCEIL3 float, ODFLOOR3 float, SCCEIL3 float,
SCFLOOR3 float, INTCEIL3 float, INTFLOOR3 float, RSVCEIL3 float, RSVFLOOR3 float, COMPDEFCEIL3 float, COMPDEFFLOOR3 float, FDICCEIL3 float,
FDICFLOOR3 float, ALT1ECCEIL3 float, ALT1ECFLOOR3 float, ALT2ECCEIL3 float, ALT2ECFLOOR3 float, ALT3ECCEIL3 float, ALT3ECFLOOR3 float,
ALT4ECCEIL3 float, ALT4ECFLOOR3 float, ALT5ECCEIL3 float, ALT5ECFLOOR3 float, ALT6ECCEIL3 float, ALT6ECFLOOR3 float, ALT7ECCEIL3 float,
ALT7ECFLOOR3 float, ALT8ECCEIL3 float, ALT8ECFLOOR3 float, ALT9ECCEIL3 float, ALT9ECFLOOR3 float, ALT10ECCEIL3 float, ALT10ECFLOOR3 float,
ALT11ECCEIL3 float, ALT11ECFLOOR3 float, ALT1INTCEIL3 float, ALT1INTFLOOR3 float, ALT2INTCEIL3 float, ALT2INTFLOOR3 float, COMPRSVCEIL3 float,
COMPRSVFLOOR3 float, UPD_TIME char(10) NULL, UPD_USER char(8) NULL, UPD_ORG char(6) NULL)
go
Can someone please advise?

Thanks

 

[LNBruno]

You might have better luck getting a reply by posting in the SSIS forum:

http://www.tek-tips.com/threadminder.cfm?pid=1555

< M!ke >
[small]First Rule of Holes: When you're in one, stop digging.[/small]

 

[AlexCuse]

and for the love of bob, use code tags!

(click "Process TGML" link below posting window to find out how)

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[LNBruno]

hmmm, thought that was "for the love of dog" but it's probably just my dyslexia acting up again....

< M!ke >
[small]First Rule of Holes: When you're in one, stop digging.[/small]

 

[scooterny]

Folks,

Thanks for responding. Sorry I didn't put code tags on my message and I will look into the SSIS users group in the future. For now, I figured it out myself, I just had to click on the elipses ( . . . ) in the SQL Statement Editor and that gave me another editor to layout my query as I wished, rather than use the build query editor.

Scott