Base64 string in SSIS

0
votes

I have a database table with a field "FileName" and a second field which is a base64 string (nvarchar(MAX)). It's an archive from my financial system. I want to convert this string back into a file using an Byte[] in a SSIS script task but i can't get the string value out of this object variable.

First I get the value from the SQL database in a SSIS variable (Base64Data). This variable is of type Object since the SQL type is nvarchar(MAX). I use sql statement: SELECT Base64Data FROM SubjectConnector WHERE FileName= '16-VMA-37041.pdf' which returns only one row. I then connect the Base64Data to a variable [User::Base64Data] in the Result Set window of the Execute SQL Task Editor. No problems here (at least so it seems).

But when I check the value of this object variable with: MessageBox.Show(Dts.Variables["User::Base64Data"].Value.ToString()); it states: System._ComObject

What it's going on? Is the result from the SQL query empty? How can I check this or what else is wrong?

Here's my SQL data

Please help.

1
ssisbase64

1 Answers

0
votes

Variable type Object is in fact ADO recordset, so you cannot get it directly with Dts.Variables... statement. I extracted NVARCHAR(MAX) value with FOR EACH enumerator for ADO.NET recordset, and fetched value from the first column into text variable.