1

I need to export varbinary data to file. But, when I do it using Column Transformations in SSIS, the exported files are corrupt. There are few junk characters at the start of the file. On removing them, the file opens fine.

A similar post for BCP, says that these characters specify the data length.

Would like to know how to address this issue in SSIS?

Thanks

Ivan Ferić
  • 4,725
  • 11
  • 37
  • 47
user2021115
  • 11
  • 1
  • I have not had issues with the Export Column Transformation task. Could you elaborate on what your package looks like, the source structure, etc? Gowdhaman008's answer looks comparable to mine [Export varbinary max](http://stackoverflow.com/questions/12712632/export-varbinarymax-column-with-ssis/12713897#12713897) – billinkc Jan 29 '13 at 20:58

1 Answers1

0

Export transformation is used for converting the varbinary to files.I have tried something similar using Adventure works which has image type of var-binary data.

  1. Following Query is used for the Source query. I have Modified the query since it does not have the full path to write image files.

    SELECT [ProductPhotoID]
      ,[ThumbNailPhoto]
      ,'D:\SSISTesting\ThumnailPhotos\'+[ThumbnailPhotoFileName]
      ,[LargePhoto]
      ,'D:\SSISTesting\LargePhotos\'+[LargePhotoFileName]
      ,[ModifiedDate]
FROM [Production].[ProductPhoto]

  2. Used the Export column transformation[also available in 2005 and 2008] and configured as follows. enter image description here

  3. Mapped rest of the columns to the destination.

  4. After running package all the image files are written into the respective folders[D:\SSISTesting\ThumnailPhotos\ and D:\SSISTesting\LargePhotos].

Hope this helps!

Gowdhaman008
  • 1,283
  • 9
  • 20