0

I am working on a SSIS package. I needed a SQL query to be executed as a part of it. 

IF NOT EXISTS (SELECT * FROM sys.objects 
               WHERE object_id = Object_id(N'[dbo].[ProcCalls]') 
                 AND type IN (N'U')) 
BEGIN 
    CREATE TABLE [dbo].[ProcCalls]
    (
         [ProcedureName] VARCHAR(100), 
         [ProcedureCall] VARCHAR(MAX), 
         [PodName] VARCHAR(50), 
         [AccountName] VARCHAR(50), 
         [OrganizationID] VARCHAR(50), 
         [CenterID] VARCHAR(50)
    ) 
END 
GO

TRUNCATE TABLE [ProcCalls]

I tried using both direct input and file connection input but both the times build query tab fails and I get this error:

Unable to parse query text

Also upon execution, the package fails

PS: stackoverflow.com/a/11128418 This answer worked for me. I did put queries not starting with CREATE/ALTER in Exec() and the package executed successfully.

Nikhil Nilawar
  • 1
  • 2
  • If I understand correctly, you can always drop and create the table. `DROP TABLE IF EXISTS [dbo].[ProcCalls]; CREATE TABLE [dbo].[ProcCalls] ()` It's a little simpler than your current code. – qxg Aug 10 '18 at 05:38
  • @qxg that depends on his SQL server version – SqlKindaGuy Aug 10 '18 at 06:44
  • I am using SQL Server 2016. @qxg Drop table construct is not allowed in SS 2016 – Nikhil Nilawar Aug 10 '18 at 06:54
  • https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-table-transact-sql?view=sql-server-2017 states _IF EXISTS Applies to: SQL Server ( SQL Server 2016 (13.x) through current version)._ – qxg Aug 10 '18 at 07:04
  • Show us your result set - It looks like your trying to insert it into a variable based on your comments to keith harris – SqlKindaGuy Aug 10 '18 at 08:18
  • @Thomas Yeah. There was a redundant variable and I removed it to solve the problem. Anyway package has no errors now. Thanks :) – Nikhil Nilawar Aug 10 '18 at 09:43

1 Answers1

0

Remove the "GO" and replace it with a semi-colon ";"

Keith Harris
  • 1,118
  • 3
  • 13
  • 25
  • Tried it. But exact same problem remains. I found following error in output log "Error: 0xC002F210 at Create Stage table to Populate from File, Execute SQL Task: Executing the query "IF NOT EXISTS (SELECT * ..." failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4) " – Nikhil Nilawar Aug 10 '18 at 06:45
  • Are you using full resultset? Show us the ssis package setup from your execute task – SqlKindaGuy Aug 10 '18 at 08:14
  • I am using result set as none. https://stackoverflow.com/a/11128418 This answer worked for me. – Nikhil Nilawar Aug 10 '18 at 09:35