T-SQL script skipping lines of code (it shouldn't)

[BrooksMT]

I have a T-SQL script that is almost 1,100 lines of code that is acting strangely.

It has 19 "steps" and if you copy and paste the code into a query window in SSMS, it runs perfectly.

But if you add a link to the script in an SSIS connection and run it from an Execute SQL task, it runs steps 1 to 12, skips 13 to 18, then runs step 19.

The code is not commented out and there are no GOTO's or other flow control statements that I see.

Any of the veterans here have any ideas or suggestions?

Thanks,

Brooks

 

[Olaf Doschke]

Are there any GO statements?

This is a command not really part of T-SQL, but of the SSMS query window. It means you have many scripts. Some commands need to run as first command of a script, but they'd error, not silently being skipped.

Anyway, first look out for GO. If you have that, you rather need several scripts running sequential.

Bye, Olaf.

 

[BrooksMT]

Thanks for the post, Olaf. No, no 'GO's. I found a workaround. I took the entire script and made a stored procedure out of it and that seems to be working. My conclusion is that some SSIS objects do not always perform correctly.

Brooks

 

[gk53]

this is dependence from how you add script into script task. If just copy and past into script task log script it will be truncated and not give you any notification... the work around is: save script to file and in script task click browse and select file you saved, that will added entire script regardless of script size

 

[BrooksMT]

I agree gk53, up to a point. I found there is a 32,767 character limit to the text, so above that, even the browse won't work. My script is 51,000+.

Even pointing to the SQL file in a connection, when pointing to the connection from the Execute SQL task was not working in the end.

The SP is working fine.

I'm wondering if there is also a character limit on one line. I have a few with 200+ characters (I didn't write this script ).