SSIS setting column with data in Script Component

Question

I have a script component that take an image, make it smaller with c# code and then try to set the byte[] result to the column. At this point I get an error:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSManagedComponentWrapper100.AddBLOBData(IDTSBuffer100 pIDTSBuffer, Int32 hRow, Int32 hCol, Byte[]& ppsaData) at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.AddBlobData(Int32 columnIndex, Byte[] data) at ScriptMain.Input0_ProcessInputRow(Input0Buffer Row) in c:\Users\e032955999\AppData\Local\Temp\vsta\65b7c0a9d6444e7f987741e111376505\main.cs:line 127 at UserComponent.Input0_ProcessInput(Input0Buffer Buffer) in c:\Users\e032955999\AppData\Local\Temp\vsta\65b7c0a9d6444e7f987741e111376505\ComponentWrapper.cs:line 36 at UserComponent.ProcessInput(Int32 InputID, String InputName, PipelineBuffer Buffer, OutputNameMap OutputMap) in c:\Users\e032955999\AppData\Local\Temp\vsta\65b7c0a9d6444e7f987741e111376505\ComponentWrapper.cs:line 27 at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.ProcessInput(Int32 InputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

enter image description here

Code:

    public override void Input0_ProcessInputRow(Input0Buffer Row)
{

    //Row.BigImage = Row.Image;
    Row.FilePath = Row.Path;
    byte[] arr = MakeSmallImage(Row.Image);
    Row.SmallImage.ResetBlobData();
    Row.BigImage.AddBlobData(arr);
    Row.SmallImage.AddBlobData(arr);

}



private byte[] MakeSmallImage(Microsoft.SqlServer.Dts.Pipeline.BlobColumn blobColumn)
{
    const int iSmallImageMaxSize = 15 * 1024;
    const int iSmallImageMinSize = 10 * 1024;

    byte[] BytePicture = blobColumn.GetBlobData(0, (int)blobColumn.Length);//Get the picture bytes from the blob.
    byte[] SmallPicture = null;

    if (BytePicture.Length < iSmallImageMaxSize)
    {

        using (MemoryStream oOriginalPictureStream = new MemoryStream(BytePicture))
        {
            Image oPicture;

            int sourceX = 0;
            int sourceY = 0;

            int destX = 0;
            int destY = 0;

            oPicture = Image.FromStream(oOriginalPictureStream);
            Size OriginalPictureSize = oPicture.Size;

            int CompressedPictureBytes = BytePicture.Length;
            MemoryStream oCompressedPictureStream = new MemoryStream(iSmallImageMaxSize);
            Size CompressedPictureSize = OriginalPictureSize;

            double Ratio = ((double)OriginalPictureSize.Width) / OriginalPictureSize.Height;
            Size CompressedPictureMinSize = new Size((int)(120 * Ratio), 120);
            Size CompressedPictureMaxSize = OriginalPictureSize;

            int Count = 1;
            do
            {
                oCompressedPictureStream.Position = 0;
                switch (Count)
                {
                    case 1:
                        Count++;
                        break;
                    case 2:
                        Ratio = Math.Sqrt(((double)iSmallImageMaxSize) / CompressedPictureBytes);
                        CompressedPictureSize.Width = (int)(CompressedPictureSize.Width * Ratio);
                        CompressedPictureSize.Height = (int)(CompressedPictureSize.Height * Ratio);
                        Count++;
                        break;
                    default:
                        if (iSmallImageMaxSize > CompressedPictureBytes)
                        {
                            CompressedPictureMinSize = CompressedPictureSize;
                        }
                        else
                        {
                            CompressedPictureMaxSize = CompressedPictureSize;
                        }
                        CompressedPictureSize = (CompressedPictureMaxSize - CompressedPictureMinSize);
                        CompressedPictureSize.Width = CompressedPictureSize.Width / 2;
                        CompressedPictureSize.Height = CompressedPictureSize.Height / 2;
                        CompressedPictureSize += CompressedPictureMinSize;
                        break;
                }

                Bitmap CompressedPicture = new Bitmap(CompressedPictureSize.Width, CompressedPictureSize.Height,
                                                      PixelFormat.Format24bppRgb);
                CompressedPicture.SetResolution(Math.Min(oPicture.HorizontalResolution, 72),
                                                Math.Min(oPicture.VerticalResolution, 72));

                Graphics grPhoto = Graphics.FromImage(CompressedPicture);
                grPhoto.InterpolationMode = InterpolationMode.HighQualityBicubic;

                grPhoto.DrawImage(oPicture,
                                  new Rectangle(destX, destY, CompressedPictureSize.Width,
                                                CompressedPictureSize.Height),
                                  new Rectangle(sourceX, sourceY, OriginalPictureSize.Width,
                                                OriginalPictureSize.Height),
                                  GraphicsUnit.Pixel);

                grPhoto.Dispose();
                CompressedPicture.Save(oCompressedPictureStream, System.Drawing.Imaging.ImageFormat.Jpeg);

                CompressedPicture.Dispose();
                CompressedPictureBytes = (int)oCompressedPictureStream.Position;
                oCompressedPictureStream.SetLength(CompressedPictureBytes);
            } while (((CompressedPictureMaxSize - CompressedPictureMinSize).Width > 50) &&
                     ((CompressedPictureBytes > iSmallImageMaxSize) ||
                      (CompressedPictureBytes < iSmallImageMinSize)));
            oPicture.Dispose();
            //SmallPicture = new byte[oCompressedPictureStream.Length];
            SmallPicture = oCompressedPictureStream.ToArray();
            oCompressedPictureStream.Dispose();
        }

        //oDAL.UploadFileToDatabase(SmallPicture, BytePicture, lTmunaID);

    }

    return SmallPicture;
}

In MakeSmallImage if you wrap that if statement with a try/catch block, does the error go away? — billinkc

Piotr Sobiegraj Piotr Sobiegraj · Accepted Answer · 2015-01-16T02:37:42

Maybe it's due to null? When BytePicture.Length < iSmallImageMaxSize condition is false MakeSmallImage method will return null.
BlobColumn class has SetNull method, so you should use it for nulling the blob.

SSIS setting column with data in Script Component

1 Answers