SO I dont know why this is happening in EST as my T-SQL is perfectly running on SSMS. First Statement: IF EXISTS(Select * from sysobjects where xtype ='P' and name = 'Pr_InsertIP_Issue_Porting' ) BEGIN drop proc Pr_InsertIP_Issue_Porting END Go Second Statement: Create PROC ABC as BEGIN BLAH BLAH BLAH END And I am getting below error - [Execute SQL Task] Error: Executing the query " IF EXISTS(Select * from sysobjects where xtype ='..." failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. What surprising is when I did little R&D and tried to create multiple dummy tables and drop same I again got same error. All statements are seprated by "Go". NOTE: The proc that I am trying to create is calling 2 other procs with PARAMS internally. Even tried to execute T_SQL seprately in 2 different SQL tasks Example - IF Exists....... in 1 task and Create PROC......in another still both fails with same error. Please help !

Hi Guarav, are u passing any procedure names as parameter to Execute SQL task.Because the error ur getting usually can be seen when parameter passed to Execute SQL is maapped incorrectly.Please let me know on this.Please have look on the comment

No Params are used. I am simply checking if PROC is existing then DROP same and re-create with definition - (Would like to inform that my PROC definition contains creating multiple XML's , loops and also internally calls 2 other procs with spme params ) But unfortunately I dont think so that it is error. As when I segregated my ("DROP if EXISTS...")statement to a different EST I still got the same error...SO I dont think Create PROC is creating any problem here..

The other possibility may be sue to improper synatx or due to synatctical errors.What i mean to say their may be proble with inverted commas,check for ur synatx. Copy ur SQL command in management studio and check for the synatactical errors. Please have look on the comment

Thanks Santosh, But as I said my querrys are working perfectly in management studio without any glitch. Its just when i execute them in EST they fails..

I see you are trying to call procedure, why dont you create another wrapper proedure for all these procedures and then call that single wrapper procedure from SQL task. Also if you are nt too keen on creating a wrapper procedure try removing the GO statements it should ideally work out fine Abhinav http://bishtabhinav.wordpress.com/

Hi Gaurav, Could you also check whether following two properties are set correctly in Execute SQL Task component :- SQL Source Type - Should be "Direct Input" Bypass Prepare - Should be "True"HTH, Cheers!! Ashish Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

Yes they are. Both are set correctly. And still getting this bogus error. I wonder sometimes if SSIS is right ETL tool to choose for --

Cannot use warpper as If I will create wrapper then again I have to use IF EXISTS & CREATE WARPPER PROC. I am desiging this Package at DEV side and its supposed to run with one of the Clients which has PROD database. SO Even IF i Create a Wrapper here, client will not have wrapper so I manually need to create that there first. I want my package to take care of evrything. Hence Wrapper will not help me mutch as I will still get same error that I am getting now.

How about splitting the drop and create into 2 different execute sql tasks one after another. That should work fine. About whether SSIS is the right ETL tool, it depends on what your requirements are and the existing technology in use. Its a great ETL tool when used in the right place.http://btsbee.wordpress.com/

As suggested rightly by earlier poster, design your package in a manner that all Drop,Truncate, Create scripts are maintained together.Please vote as helpful or mark as answer, if it helps Cheers, Raunak | t: @raunakjhawar | My Blog

Tested EST with multiple T-SQL and it works fine There might be an issue with the code... Could you post your code?Meganathan Kanagaraj (MCTS - SQL 2005)

Tested EST with multiple T-SQL and it works fine There might be an issue with the code... Could you post your code?Meganathan Kanagaraj (MCTS - SQL 2005)

Hi Gaurav_X, If the Execute SQL task runs a batch of SQL statements, the following rules apply to the batch: 1. Only one statement can return a result set and it must be the first statement in the batch. 2. If the result set uses result bindings, the queries must return the same number of columns. If the queries return a different number of columns, the task fails. However, even if the task fails, the queries that it runs, such as DELETE or INSERT queries, may succeed. 3. If the result bindings use column names, the query must return columns that have the same names as the result set names that are used in the task. If the columns are missing, the task fails. 4. If the task uses parameter binding, all the queries in the batch must have the same number and types of parameters. For more information about it, please see: http://msdn.microsoft.com/en-us/library/ms141003.aspx Please feel free to ask if you have any question. Thanks, Eileen Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.