Using SQL Server 2008 R2 64 bit. The OLE DB Command throws an error when executing code within an IF statement that should never execute. For the sake of this question, assume a stored procedure contains a line of code that reads "if 1=0 begin set @x='hello' end". Assume the @x is an integer. The stored procedure will run fine in all other tools like Management Studio but will give the error that 'hello' cannot be converted to an int when trying to use the SP in an SSIS OLE DB Command. Considering the if statement always resolves to false, why would the parser execute or pretend to execute the code within the if statement? The problem happens at design time and run time (fails validation). You may ask me why I would want to assign a varchar to an int. Of course I do not. In the real scenario I am checking staging data for a date and then executing a block of code only if a date exists in a field. I can work around this problem by adding code to the stored procedure. Is this issue a bug or design? If design, why? It makes perfectly good stored procedures fail and have to be recoded just for SSIS. For the curious, the real code is below. First the code that will not run in SSIS and then my workaround. -- Will not work in SSIS If Isdate(@EQUIP_SUPT_END_DT)=1 BEGIN Set @EQUIP_SUPT_END_DT2_SK = convert(varchar(50),datepart(yyyy,@EQUIP_SUPT_END_DT)) + right('00' + convert(varchar(50),datepart(mm,@EQUIP_SUPT_END_DT)),2) + right('00' + convert(varchar(50),datepart(dd,@EQUIP_SUPT_END_DT)),2) END -- Will work in SSIS If Isdate(@EQUIP_SUPT_END_DT)=0 Begin Set @EQUIP_SUPT_END_DT = convert(varchar(11),CONVERT(datetime,'1/1/1753',101),101) End If convert(varchar(11),CONVERT(datetime,@EQUIP_SUPT_END_DT,101),101) <> '01/01/1753' BEGIN Set @EQUIP_SUPT_END_DT2_SK = convert(varchar(50),datepart(yyyy,@EQUIP_SUPT_END_DT)) + right('00' + convert(varchar(50),datepart(mm,@EQUIP_SUPT_END_DT)),2) + right('00' + convert(varchar(50),datepart(dd,@EQUIP_SUPT_END_DT)),2) END

Seems that if you would turn off the validation your original SP could work (DelayValidation set to TRUE).So you just wanted to check if a value is date, why you did not use ELSE in the 1st IF statement?Arthur My Blog

If the above code is inside a Stored Procedure, then SSIS or OLE DB is not executing or parsing it at all. If it's part of the text of the SQL command inside an OLE DB Command, then it is. However, I would recommend that you use a Derived Column or Data Conversion transform prior to the OLE DB Command to convert the string to a date (and detect if it is a date), then use a simple OLE DB Command to perform the update. Talk to me now on

Todd, your recommendation is valid and is the ultimate solution. The SP is very complex and I plan to convert but it will take time. I have to disagree regarding executing or parsing an SP in an OLE DB Command at least until I understand what SSIS is actually doing. If you have time, please take a look at the video on YouTube at http://www.youtube.com/watch?v=i1x7E0yk-Qo . Sorry to use a video but that is the best method to see what I see. I created a test SP to make it simple. Notice when I press refresh on the OLE Command, the code in the SP is either executed or parsed or something else because it is pulling the text "test-abcd" into the error message. Considering @STG_CERP_EQUIP_SK will never equal -999999, the value for @y should never be set to "test-abcd" and the integer @x should never be set to @y because the if statement 1=0 always returns false. Why does the error about converting Varchar to Int appear? The OLE Command should have no knowledge of the conversion at design time. Is it executing the SP when I hit Refresh? Even so, an error should not happen based on the code. I am very confused about this situation and just want to fully understand what is happening. If you can shed additional light I really appreciate it. Full SP code below. ALTER PROCEDURE FORUM_TEST @STG_CERP_EQUIP_SK int as -- FORUM_TEST 45327 Declare @x int, @y VARCHAR(50) Set @y = '0' If @STG_CERP_EQUIP_SK = -999999 Begin Set @y='test-abcd' End if 1=0 Begin Set @x = @y End

Not what I would have expected either - but fire up SQL Profiler, and you'll see this has nothing to do with SSIS. It does differ from SSMS - and I'm sure it's due to the OLE DB provider as opposed to the SNAC client that SSMS uses (I think). For some reason, the OLE DB request goes through some other hoops - to detect parameters, etc, as well as a different method of executing the stored procedure with sample values in order to "test" it. You'd have to talk to OLE DB or T-SQL forum guys to know why it's behaving the way it is... Talk to me now on

Thank-you for taking a look. I appreciate the time. I understand what you are saying. I'll do some more experimenting. Since it is nothing to do with SSIS, I am marking your reply as answer and I'll work to convert to derived columns as that would be the true solution. Thanks again! Oh, Arthur... Sorry but the validation change did not work. Thanks for replying.