I'm trying to create a SSIS pkg that reads a excel file which contains a date. Then I test that date to make a decision on what route to take within the ssis pkg. I first started to do this with one data flow task and within that data flow I had a excel connection source and then a conditional splitter but after the splitter I can not add a ole db source. i guess the ole db source can not get inputs. So I have to move the decision out to the control flow and then have 2 data flows and only execute one depeding on the date found in excel. SO now I havea execute sql task with Connection type = excel connection = excel connection manager (connects to the excel file) sqlsourcetype = direct input sql statement = select date from [Dates$] I have a local variable that I'm trying to assign the date value from the excel. local variable I tried type date, string, int32, etc... in excel I tried to format the column to number, text, general, date. I get this error The type of the value being assigned to variable "User::LastRunDate" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ANy suggestion would be great. even if there is another way to do what I'm trying to accomplish which is depending on the date contain in the excel file execute different old db queries.

Use a Precedence Constraint to direct the flow (the constraint should be based on the variable). To make this variable assessed properly use dt_String in the Excel metadata definition, then you can convert it to date in a Derived Column Transformation and compare to another date value in the precedence constraint mentioned. Example: http://blogs.msdn.com/b/mattm/archive/2006/10/30/using-expression-based-constraints.aspxArthur My Blog

thanks for your reply but I don't understand what you are suggesting since your suggetions about setting the excel metadata definition would take place in the the data flow using the excel source and using the derived column to change the data type. But the precedence constraints are on the control flow so I don't know how you can set a user variable on the data flow ? so I can in turn compare it on the precedence constraint.

Perhaps I was not clear enough, how about you take a look at this document: http://www.texastoo.com/images/Expressions_White_Paper.pdf ?Arthur My Blog

sorry but sitll not sure what your propose solution is. I understand how to set precedence constraints and I know how to use the derevied column trans. WHat I'm not sure is how in the precedence constraints I'm going to evaluate or compare the date value is stored in excel file and used in the data flow by using hte excel source. Are you suggesting to grab the excel date value within the control flow so I can use it in the presedence constraint? Only way I that I know of grabbing the date from excel within the control flow is to use the execute sql task and assign the value to a local user variable which is what I'm trying to do but I'm having issues with incorrect data types.

... WHat I'm not sure is how in the precedence constraints I'm going to evaluate or compare the date value is stored in excel file and used in the data flow by using hte excel source. Are you suggesting to grab the excel date value within the control flow so I can use it in the presedence constraint? Only way I that I know of grabbing the date from excel within the control flow is to use the execute sql task and assign the value to a local user variable which is what I'm trying to do but I'm having issues with incorrect data types. You capture and store the date value from the Excel in a variable that you convert to date, then later use inside the Precedence Constraint.Arthur My Blog

You set an Expression like in http://beyondrelational.com/blogs/sudeep/archive/2010/08/17/control-flow-based-on-the-input-file-name.aspx but in your case it need to involve the dates e.g. LEN(@[User::strMyDate] > GETDATE() Arthur My Blog

You capture and store the date value from the Excel in a variable that you convert to date THis is the part I'm having challenges with not the precedence constraint. Capturing the date from excel which I'm doing with a "Execute SQL task" and trying to set the result set to the user variable but as mentioned above the data types are not matchin up. How do you suggest I "capture and store the date value from the Excel in a variable that you convert to date" ?

What is the issue, any error? Use string for all and make sure you mapped the result query properly. Example on how to capture output: http://www.julian-kuiters.id.au/article.php/ssis-execute-sql-task-output-parameters (use your SQL against the Excel file) Arthur My Blog

I' not sure why ArthurZ isn't realizing that your issue isn't with precedence constraints, but with retrieving the date value from Excel. I'm not sure I can help - it seems like you've tried what I would have (using differently typed variables). Mucking about with the Execute SQL Task a bit, it seems like it plain-old doesn't like Excel date "values" (I won't say data types, because Excel doesn't use data types). If I changed the value in Excel to what a human would consider a string, it all worked fine (with a string typed SSIS variable). So - instead of that, your only choice seems to be to use a Data Flow Task, with an Excel source and a Script destination. In the Excel source, select the sheet with the date in it. In the Script destination, add your SSIS date variable to the ReadWriteVariables property, and select the column containing the date on the Input Columns tab. Inside the code, in the ProcessInputRow method, assign the SSIS variable (using Variables.) from the Row.date column. Talk to me now on