CODEWITHSUNDEEP
sqlssisexecute
SQL_Guy
SQL_Guy

Reputation: 353

SSIS Execute SQL Task - Statement from Variable - Variable value from a table

Folks,

I am creating SSIS package, which contains Execute SQL task. The "SQL Source Type" property is set to be Variable and it uses one of my package variables. So far so good. Now all of this is wrapped inside "For Each" loop, which reads values from a database table. You see, I have a table with I bunch of SQL statements (stored in VARCHAR column), which I would like to execute. All of this seems to work until I have a multi-line SQL statement in my table. If there is a carriage return present, here is what I observe. Let's say I have this statement stored in my table and I would like to run it 

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.MyTable') AND name = N'MyIndex')
DROP INDEX MyTable.MyIndex

If I set a breakpoint in SSIS package an look at the value of the variable, which gets populated with this sql text, it looks as follows (notice \r\n):

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.MyTable') AND name = N'MyIndex')\r\nDROP INDEX MyTable.MyIndex

and if I let it execute and capture the results using Profiler, the statement, which actually runs will be everything up to \r, in other words, only the first line:

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.MyTable') AND name = N'MyIndex')

This is obviously not what I want. The actual statements I need to run are much more complicated and would prefer to keep them nicely formatted, so they can be visually examined if necessary. Is this a known limitation I am encountering and are there any workarounds?

Thank you!

Upvotes: 0

Views: 1774

Answers (2)

SQL_Guy
SQL_Guy

Reputation: 353

Thank you for the recommendation about unwrapping lines with Notepad ++. This is good to know.

Nevertheless my issue was a self-inflicted one. The SSIS works just fine even if the queries I am trying to execute contain new lines. What confused me was the fact that in cases when IF EXISTS () evaluates to FALSE, the subsequent statement will not be executed and will not show up in the profiler trace. For example, if you were to run this:

IF 1 = 0 SELECT 'No Way'

you will only see "IF 1 = 0" in the trace output. I misinterpreted this result as an issue with carriage returns. False alarm!

Upvotes: 0

davmos
davmos

Reputation: 9587

Workaround #1: see discussion about ways to convert T-SQL code to one line and back again here: Format TSQL onto one line

Upvotes: 1

Related Questions