SQL Server – Join to list of Strings

This one can also be used when passing a multi-value parameter from SSRS to the database.

Firstly you need to use the delimited8k split function that was created and refined by many people such as Itzek Ben Gan, Lynn Pettis and Jeff Modern. If you don’t know who these people are, go and find out as there’s some excellent reading to be had. Anyway, you can download the function here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Here’s some code you can use to see it in action: 

DECLARE @IntsAsStrings VARCHAR(100) = '1,2,3,4,5,6,7,8,9';

DECLARE @TestTable TABLE 
(
	Id INTEGER IDENTITY(1,1),
	ValueString VARCHAR(50)
);

INSERT INTO @TestTable VALUES ('We'),('Are'),('Leeds'),('MOT');

SELECT *
FROM @TestTable t
RIGHT JOIN [dbo].[DelimitedSplit8K](@IntsAsStrings, ',') ss ON ss.ItemNumber = t.Id

You can then pass a string into your proc and at the top split that out into a table or join directly onto the split function table results (as it’s a Table Valued Function (TVF)).

SSIS – RecordSet Object set to NULL after ForLoop

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:

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

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.

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 🙂