question

Lawndemon avatar image
Lawndemon asked

SSIS SQL Task: Two statements, One variable

I'm trying desperately to execute two statements in a single expression variable. I'm using the expression as a variable because this is intended as a template for other extractions and I want to minimize the customization. Basically this is my code: *"Truncate Table xxx.dbo."+@LoadTableName +" GO " +"Truncate Table xxx.dbo."+@LoadTableName +"_Rejects" +" GO"* The expression evaluates as: *Truncate Table xxx.dbo.TableName GO Truncate Table xxx.dbo.TableName_Rejects GO* I think it's the fact that it's all jumbled up on one line that is the problem. I'm pretty sure GO needs to be isolated for it to execute. However, I can't for the life of me figure out how to force the expression to carriage return when a variable is used. When no variable is used it's a no-brainer but the moment you include a variable it compacts. Any advice is greatly appreciated!
sqlssisvariabletaskjumble
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
What about using a semicolon rather than the "GO" statements?
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 This is the best way to go. One might optionally consider new lines in between the statement, i.e. **char(13) + char(10)** but these are for human readability, the database engine does not care much about line breaks. **GO** is not really T-SQL, it is a batch separator out of the box and can be changed to something else via Options in SSMS.
1 Like 1 ·
Daniel Ross avatar image
Daniel Ross answered
copy and paste this into the expression window of the variable, and see how it goes. If you look at the variables proerties, in the Expression property you should be able to see two little squares before and after each go statement "Truncate Table xxx.dbo."+@LoadTableName +" GO " +"Truncate Table xxx.dbo."+@LoadTableName +"_Rejects" +" GO"
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
You can try passing the characters for "New Line" = CHAR(13) + CHAR(10): DECLARE @Command varchar(255), @LoadTableName varchar(255)='TestTable' SELECT @Command='Truncate Table xxx.dbo.'+@LoadTableName +' ; ' + CHAR(13) + CHAR(10) +'Truncate Table xxx.dbo.'+@LoadTableName +'_Rejects' +' ;' PRINT @Command IIRC, `GO` is a special command that may not be interpretted correctly if sent to SQL Server in anything other than SSMS. Separate commands with a semicolon. *EDIT: added char(10) to script and description, fingers were faster than my brain!*
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 but the statement "New Line" = char(13) is not correct. char(13) is not a new line. In Windows the new line consists of 2 characters, namely **carriage return** and **line feed**. This is perfect implementation, because it replicates the behaviour of the good old typewriter which would require the operator to:
1. Grab the handle and move the carriage 
   from right to left (carriage return or char(13))
2. Release the handle which will drop the carriage 
   one line (line feed or char(10))
Any editor but Notepad will fix user errors and display inappropriate new lines correctly. For example, SSMS easily handles abuse in the form of char(13) or even char(10) and still displays the line break properly in the results window. Try this to see:
declare @v varchar(100);
set @v = 'Line 1' + char(13) + 'Line 2';
print @v;

This happily prints
Line 1
Line 2
Copy the results and paste them to Notepad, and it does not look as happy anymore producing ugly looking Line 1 followed by a square followed by Line 2. The above might seem like not a big deal until someone creates a script and saves it to Notepad to be opened and executed later :(
1 Like 1 ·
Oleg, absolutely right. I wrote the "character**s** for "New Line", and got it right in my head at the time, but left off CHAR(10). It really is bad when the head thinks one thing and the hands another! I have edited my post to reflect the change.
0 Likes 0 ·
pits avatar image
pits answered
I agree with Thomas,Semicolons separate multiple SQL statements.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Lawndemon avatar image
Lawndemon answered
Thanks for all the answers! I tested both the semicolon suggestion and the solution that Daniel Ross put forward. Both ideas work great. It looks like the GO command works fine as long as it is isolated on its own line but the semicolons are even easier because line formatting is irrelevant. Cheers!
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You should voe @ThomasRushton answer and accept it if it solved your problem
1 Like 1 ·
I'd love to do that but I have no idea what you are talking about.
0 Likes 0 ·
Scott Abrants avatar image
Scott Abrants answered
GO is meant to define a batch, it sounds like this is not something you care about in this situation. I would suggest the semicolons in this case as well.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Narsi avatar image
Narsi answered
"Truncate Table xxx.dbo."+@LoadTableName +" " +"Truncate Table xxx.dbo."+@LoadTableName +"_Rejects" Try this
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That doesn't work. However, the problem has been solved as per the above comments. Cheers!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.