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.UTF8.GetBytes(data)) format

DateTime 

DateTime output column data types should be of type database timestamp [DT_DBTIMESTAMP]

 

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.UTF8.GetBytes(content));
 

                    ApiOutputBuffer.ResponseDateTime = DateTime.Now;