I have an SSIS 2005 Package that includes a number of Execute SQL Tasks which contain a parameter in the WHERE clause (and only in the WHERE clause). These have worked perfectly since they were created 6 months ago. However, I just had to reinstall SQL Server 2005 due to an unrelated issue. After reinstalling, the pacakge is now failing on those Execute SQL Tasks. As an example, my SQL Syntax for the Execute SQL Task looks like this: UPDATE a SET a.awd_type_id = w.awd_type_id FROM etl_award_master a INNER JOIN wkg_awd_type w ON a.obj_id = w.obj_id AND a.source_db = ? I have a parameter "0" that takes it's input from a user variable. I have confirmed that the user variable is populated correctly, and it is used may other places in the package without a problem. If I remove the parameter from the Execute SQL Task and hard code it's contents in place of the "?" in the query then it executes perfectly. If I keep the parameter (which I need to do) I get the following error: Execute SQL Task: Executing the query "UPDATE a SET a.awd_type_id = w.awd_type_id FROM etl_award_master a INNER JOIN wkg_awd_type w ON a.obj_id = w.obj_id AND a.source_db = ?" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. In a different Execute SQL Task that uses the same parameter I got a slightly different error: The multi-part identifier "e.source_db" could not be bound.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Any thoughts or feedback on this would be appreciated. The code has not changed and I even tried older backup copies of the code to be sure. The variable that fees the parameter is populating correctly and DFTs that use the variable have no problem. I believe it has something to do with my SQL Server 2005 reinstall but can't think what. When I reinstalled I set-up SQL 2005 the exact same way it was before (or so I believe). I am using code page 1252 in case that is relevant (the default). Thanks in advance, - Charles Charles

do you execute this statement on correct database? and another though is that did you changed the connection in the execute sql task? it should be OLEDB .http://www.rad.pasfu.com

Thanks for the quick replay Reza. The connection is the same as it was before. Absolutely nothing changed in the package and all the other parts of the package that use that same connection still work perfectly.... strange eh? - CharlesCharles

Thanks for the quick replay Reza. The connection is the same as it was before. Absolutely nothing changed in the package and all the other parts of the package that use that same connection still work perfectly.... strange eh? - Charles Charles Also as I mentioned in my original post, if i just change the ? to a hard coded value so that here is no parameter then the Execute SQL Task works perfect. I think this is further evidence that the database is there and the connection string is correct. It only fails when there is a parameter in the where clause. The weird thing is that the parameter has been there for 6 months working perfectly. It is only since reinstalling SQL Server that the error occurs in the package.Charles

Yes, its strange, but we can try to track the issue, first step: replace the question mark ( ? ) with an actual value in the execute sql task and try again, I want to find out that there is no problem with connection. let me know result.http://www.rad.pasfu.com

Thanks for the quick replay Reza. The connection is the same as it was before. Absolutely nothing changed in the package and all the other parts of the package that use that same connection still work perfectly.... strange eh? - Charles Charles Also as I mentioned in my original post, if i just change the ? to a hard coded value so that here is no parameter then the Execute SQL Task works perfect. I think this is further evidence that the database is there and the connection string is correct. It only fails when there is a parameter in the where clause. The weird thing is that the parameter has been there for 6 months working perfectly. It is only since reinstalling SQL Server that the error occurs in the package. Charles OK, I didn't read this part carefully, Where your parameter comes from ?http://www.rad.pasfu.com

Thanks Reza - yes as I mentioned in my original and last posts.. "if i just change the ? to a hard coded value so that here is no parameter then the Execute SQL Task works..." So it seems the parameter is causing the problem though I can't figure out why. The parameter pulls from a VARCHAR variable and that variable has package level scope and is used in many DFTs without problem. I have verified the variable has the contents it should... - CharlesCharles

could you try to create new string variable and use this in parameter mappings of execute sql task instead of previous one? what is the result?http://www.rad.pasfu.com

Reza, I just figured out something out that may help.... The original SQL was: UPDATE a SET a.awd_type_id = w.awd_type_id FROM etl_award_master a INNER JOIN wkg_awd_type w ON a.obj_id = w.obj_id AND a.source_db = ? I just tried changing it to the following and it worked!: UPDATE a SET a.awd_type_id = w.awd_type_id FROM etl_award_master a INNER JOIN wkg_awd_type w ON a.obj_id = w.obj_id AND a.source_db = ' ? ' The only difference is the single quotes around the question mark for the parameter... ' ? ' instead of just ? So I think somehow I my SQL Server set-up or maybe my SSIS set-up is now requiring the single quotes around the parameter where before it did not.... Any idea how I can fix this? To answer your other question about where the parameter comes from it is stored in a user string variable with package level scope. For the parameter it is listed as VARCHAR and the name is "0" so that it matches to the first and only ? mark in the SQL statement. All that said I don't think this is relavant. I think the root cause of the problem is my SQL or SSIS install / reinstall and the handling of the ' marks.... Any ideas?Charles

seems weird , in the second installation of sql server what things changed? collation? edition?...?http://www.rad.pasfu.com

It's been a while since I did the original installation but I recall I pretty much stuck with the defaults... default instance, code page 1252, etc... so I did the same thing this time. Since I reinstalled everything from SQL Server that included integration services and the development tools (i.e. BIDS) so I wonder if something could be different there... I guess I could fix my development machine just by adding the single quotes but I am worried that if I do that it will break when I deploy to my customer's environment...Charles

unfortunately I haven't any more idea about it.http://www.rad.pasfu.com

Thanks for the help Reza, I appreciate all the ideas. I also just tried using a different variable which is evaluated as an expression and creating the SQL there (as described by the thread link pasted below) and it also error unless I now add the single quotes... that being the case I can add the single quotes which is what one often has to do in dynamically created SQL statements I just wonder why it worked for the last 6 months without them... weird. I've even http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/2c98aa9a-c5d8-4901-93b0-05a3b986f9bcCharles