Using LINQ, Lambda and Expression Trees in .net

LINQ and Expression Trees

Expression Trees represent code in a tree-like data structure.  In LINQ, expression trees represent structured queries that target sources of data that implement IQueryable<T>.  For example, LINQ to SQL provider implements the IQueryable<T> interface, wherein during run-time, when a LINQ to SQL query is executed the underlying implementation leverages the Expression Tree to convert the LINQ query to a “tree like data structure” and then constructs the required output (which in this case is the SQL query), i.e., translate it into a query language appropriate for the data source.  However, on the contrary, when we use LINQ queries to filter data from objects, it returns an IEnumerable<T> which doesn’t use Expression Trees internally.  For instance if you look at the below IEnumerable<T> interface definition,it doesn’t have an Expression defined in it.

Continue reading

2,332 total views, 1 views today

Script Task – Read / Reuse same Recordset twice in a SSIS yields no records or empty

When using a script task/component in SSIS, we often try to read the data from the recordset using an OLEDBAdapter and fill the dataset, like below :

    DataTable dtValidatedResultset = new DataTable();

    OleDbDataAdapter adapter = new OleDbDataAdapter();

    adapter.Fill(dtValidatedResultset, Dts.Variables["User::InputData"].Value);

(The above code uses Script Task and hence it accesses the variable using Dts.Variables collection.  However if you were to use a Script Component, then you would use the strongly typed Variables collection object and access it as Variables.InputData).

However when you use consequtive Script Tasks or Script Components that try to access and read data from the SAME RECORDSET object, the first time it will work fine, but the second time it will read empty records.  This behavior is due to the fact that the Recordset is an ADODB object and has a record pointer / CURSOR that helps to navigate/move between the records.  Since the first time when the Dataset.Fill is invoked, it would have read all the records from the recordset and would have parked the record pointer at the last record in the recordset.

The next time when the same recordset is used in another Script Task/component, it will not rewind or take the recordset pointer to the first record, but would rather try to read it from its current EOR (End of  Record) position and hence you see your DataTable empty.  The only option is to CLONE the RECORDSET before using it again, which would ensure that the original recordset always stays intact for use any number of times in the package.

11,880 total views, 9 views today