SSIS: “Incorrect syntax near 'GO'.” error

Question

I am using SSIS within Visual Studio 2012 running against SQL Server 2012 database. I get the error shown below when using OLE DB Source. This OLE DB Source is executing the SQL similar to that shown further below. When I remove the GO statement at the end, the SSIS package executes ok.

Within SQL Server Management Studio, the SQL executes ok irrespective of whether the GO statement is present or not.

The OLE DB Source is being executed within a Data Flow Task.

Why does this GO cause this error when being executed within a SSIS package, and what should be done about it? Thanks very much.

Error:

[OLE DB Source from SQL variable [33]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "Incorrect syntax near 'GO'.".

SQL:

/* Test statement */
SELECT 'test' as N'test'
GO

Answer 1

GO is a batch terminator and using it in an OLE DB Source, as the error message indicates, is incorrect.

Answer 2

Your SQL is:

SELECT 'test' as N'test'
GO

GO is a command line thing - remove it when passing SQL into the server using OLE.
Try just this:

SELECT 'test' as N'test'

Answer 3

You should remove all the "GO" from the script and it will resolve the issue.

Check this out for more info:

https://agilewebhosting.com/knowledgebase/63/SQL-Error-Incorrect-syntax-near-andsharp039GOandsharp039.html

Matrix

Answer 4

Suggested best practice: Use semi-colon as t-SQL statement terminator.Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version.

http://msdn.microsoft.com/en-us/library/ms177563.aspx