Hi All, I am running a Db2 query on a column having data type as Date. I am ruuning the query in Exec Sql Task and assigning the resultset to a SSIS variable having Datatype as DateTime. Its giving me an error saying that the variables datatype should not change during execution. Please suggest how to fix the problem. Thanks

Hi, May be you should cast your column to DateTime using either "Data Conversion" or "Derived Column" transformations and then assign the resultant value to your local variable. Make sense? Thanks,Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem --Kiran

Normally resultset return from execute sql task is assigned to object data type. Randy Aldrich Paulo MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog BizTalk Message Archiving - SQL and File Automating/Silent Installation of BizTalk Deployment Framework using Powershell > Sending IDOCs using SSIS

Thanks for the response What I observed is when I am making the SSIS variabe as String it works fine. But the prob is i cant make it as I want the variable datatype to be DateTime in some other task. Even If I am making it string then while converting using Data Conversion gives me error. Is there any way I can change the ssis variable datatype to string dynamically inside exec sql task. Thanks

My first instinct here is to think that since your package works when the variable is a string but not when it's a date type means that the source data you're pulling from is a string, not a date. You can't just shove string data into a date variable and expect nice things to happen :) What you can do is keep your variable set as a string, then work to convert it (properly) to a date. I'm assuming you're doing this in an Execute SQL Task because this data retrieval isn't being done on a row by row basis. If it is, please say so. First, we need to know some sample values for what gets retrieved into this string variable. Second, you can create a second variable - of date type - that we can craft an expression for that will read the value in the first variable, and convert it to a date type. You'd then use the second variable (the date typed one) in places where you wanted a date. Talk to me now on

Hi Todd Even I am surprised when i changed the SSIS variable to string and it worked the reason is The table am querying in db2 has all the columns having Date as the datatype & am using one of the column. thanks

Perhaps (quite likely) an Oracle Date data type is not equivalent to the SSIS DateTime data type. (To be clear, if you don't know, SSIS has several type systems used in different places. The variables use .Net framework data types.) I don't use Oracle, so can you please let me know the range of that type? It's likely larger than the DateTime type is designed to handle. Unless we figure out how to read the date in a way SSIS likes, we should use what we have, which is reading it into a string variable. There's a cast involved there, so it's not exactly what we'd like, but any performance hit will be lost in the general execution of a task to retrieve the date anyway. Read the date into a string variable. Create a date typed variable, set the EvaluateAsExpression property to TRUE, then craft an expression to convert the formatted date in the first variable to a date. Talk to me now on

Perhaps (quite likely) an Oracle Date data type is not equivalent to the SSIS DateTime data type. (To be clear, if you don't know, SSIS has several type systems used in different places. The variables use .Net framework data types.) I don't use Oracle, so can you please let me know the range of that type? It's likely larger than the DateTime type is designed to handle. Unless we figure out how to read the date in a way SSIS likes, we should use what we have, which is reading it into a string variable. There's a cast involved there, so it's not exactly what we'd like, but any performance hit will be lost in the general execution of a task to retrieve the date anyway. Read the date into a string variable. Create a date typed variable, set the EvaluateAsExpression property to TRUE, then craft an expression to convert the formatted date in the first variable to a date. Talk to me now on