I have a variable that the user sets to an excel file. Because the file can be of a different version of Excel, I'm testing the version first in a script task. Depending upon the version I then want to call a specific DataFlowTask and also want to set the connection Manager accordingly for that DataFlow task. i.e. if XLS extension then use the DataFlowTask with the Excel OLEDB Provider setting the connection to the variable passed. if XLSX extension then use the DataFlowTask that uses the Office OLDEDB Provider with the Extension of Excel 12.0 again using the Variable passed as the connection setting. An example on how to achieve this would be good. Thanks

Hi Bill, Using the approach you've described: Script Task which would set a variable to, for instance, "XLS" or "XLSX" (let's call it @ExcelExt) depending on excel file extention. Then 2 precedent constrains: @ExcelExt=="XLS" (leading to 1 Data Flow) and @ExcelExt=="XLSX" (leading to another Data Flow). If that's what you were asking about... Regards, Akim

in my script task I have: Dim fso As FileIO.FileSystem Dim strFileName As String If Dts.Variables("Filename").Value.ToString() = "" Then Dts.TaskResult = Dts.Results.Failure End If fso = New FileIO.FileSystem strFileName = fso.GetName(Dts.Variables("Filename").Value.ToString()) If strFileName.EndsWith(".xls") Then ElseIf strFileName.EndsWith(".xlsx") Then Else Dts.TaskResult = Dts.Results.Failure End If fso = Nothing Dts.TaskResult = Dts.Results.Success So as you can see I now need to call 1 of 2 DataFlows. To elaborate: In the Designer I have this Script task with 2 Data Flow tasks hanging off it. In the first one that is for .xls files I have an Excel Source set as the Source and so in here I need to set the "Excel File Path" to my variable and force the script to hand control over to this DataFlow. If the file has an .XlSX extension then I want to pass control to my second dataflow task, but not before I have set the OLE DB Source of this object to the Variable supplied. So each data flow task has a different data source type depending on the type of file the user wants to process

Bill, There are a number of things here: "hand control over to this dataflow" - To conditionally decide which task to execute you need to use, as Akim said above, conditional precedence constraints. See Introduction to expressions on workflow for instructions. Your conditional precedence constraint needs to be based on the value in a variable hence you are going to have to write an appropriate value to a variable from within your Script Task. See Writing to a variable from a Script Task. "set the OLE DB Source of this object to the Variable supplied" - You can accomplish this using expressions. See Setting Expressions. Hope that helps. Jamie http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Thanks very much for this.