SSIS – RecordSet Object set to NULL after ForLoop

Standard

This was a weird and frustrating one. I’ve got a package that has a ForLoop in it and inside the ForLoop I’m loading records that need to use a Lookup. I thought what better time to use a CascheTransform than now! The only issue was that I needed to use the Lookup from inside a Script Component in my Data Flow (not a Script Task).

Now, knowing little about c# I figured I could simply load a new ADO RecordSet into a package variable and use that in my Script Component. so I created a OleDb source and a RecordSet destination:

lookup

Then populated a variable (User::Result) of type Object with my RecordSet for use later in my Script Component

lookup1

Now later in my Script Component I passed the variable in as a ReadOnly variable and populated a new DataTable using the source variable. And it worked for the first run of the ForLoop and then the underlying variable (User::Result) got set to Null. Turns out the issue was caused by using an OleDb adapter to fill a data table:

DataTable dt = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.Fill(dt, Variables.Result);
Credit: Brad2575

The above section of code causes the object to be emptied.

The Answer

The solution to this is to populate the DataTable first, not a RecordSet and pass the DataTable into the object. This avoids having to create the DataTable from the RecordSet each time.

lookup2

You can use code similar to the below to populate the DataTable: 

public class ScriptMain : UserComponent
{
    //Create a new DataTable. This will end up getting passed around your package
    DataTable tbl = new DataTable();


public override void PreExecute()
    {
        base.PreExecute();

        //Set up the columns and PrimaryKey
        tbl.Columns.Add("Column1", typeof(int));
        tbl.Columns.Add("Column2", typeof(int));

        tbl.PrimaryKey = new DataColumn[] { tbl.Columns["Column1"] };

    }
        public override void PostExecute()
    {
        base.PostExecute();

        //Populate the PackageVariable with the newly create DataTable object.
        Variables.LookupTable = tbl;
    }

    public override void LookupInput_ProcessInputRow(LookupInputBuffer Row)
    {
        //Now populate the table with rows from your query
        tbl.Rows.Add(Row.Column1, Row.Column1);
    }

}

 

There are a couple of blog posts here and here that helped me to work out the solution for this.

I hope this can help you too 🙂

Advertisements

SSIS – Insert fails on column with a default value

Standard

Adding a ‘Created On’ field is a great idea for determining when a particular record was created in your data warehouse. This can help with a number of things not least of which is trouble shooting.

I recently added a new CreatedOn field with a default of GETDATE(), however I purposefully didn’t hook this up in my ETL so that it would automatically be populated by SQL Server itself.

I quickly ran into a problem when the package used to populate this table started failing with the below error:

“An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Cannot insert the value NULL into column ‘CreatedOn’, table ‘Sales.fact.Package’; column does not allow nulls. INSERT fails.”.”

I checked my package and this field wasn’t hooked up, so what was going on? It should have been getting a NULL inserted and then SQL Server should have put in a value of GETDATE()?!!

The problem was with the Keep Nulls check box in the OLEDB Destination:

DefaultGetdate

SSIS will override the default on the column upon insert. Because I don’t need to keep any of the nulls unchecking this check box solved the problem.

There’s a good article here on www.sqlservercentral.com with a bit more information.bit more information.