SSIS ScriptComponent Outputs

 Something else for the "Stuff I Always Forget" category.

 There's a couple of tricks when using the SSIS Script Component as a data source.

Nulls

If one of the outputs is null you need to set the _IsNull property to true

(N)Varchar Max 

Max string columns need to be output using the Column.AddBlobData(System.Text.Encoding.Unicode.GetBytes(data)) format. 

Note that this needs to be Encoding.Unicode not Encoding.UTF8 otherwise you get the infamous odd byte number error  

DateTime 

DateTime output column data types should be of type database timestamp [DT_DBTIMESTAMP], even for datetime2 otherwise you get an overflow error

 

Example

ApiOutputBuffer.AddRow();

// Check nulls
                    if (string.IsNullOrEmpty(fullEndpoint))
                    {
                        ApiOutputBuffer.Endpoint_IsNull = true;
                    }
                    else
                    {
                        ApiOutputBuffer.Endpoint = fullEndpoint;
                    }

// Nvarchar max
                        ApiOutputBuffer.Response.AddBlobData(System.Text.Encoding.Unicode.GetBytes(content));
 

                    ApiOutputBuffer.ResponseDateTime = DateTime.Now;