Clicky

I am creating SSIS 2008 package. It has an execute sql task that runs an sproc to read a byte array (pdf document binary) which needs to go to a package variable named User::PDfDocument. I tried declaring the variable User::PDfDocument of type byte and also Sbyte, but in both cases I got the following error:
The type of the value being assigned to variable "User::PDfDocument" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

1. Please suggest what should be the data type of this variable that will hold byte[].

2. I need to embed this byte array in a flat file appended to a string as shown in the following example. please suggest how do I append this byte array to the string.

partial sample text file content:
OBX|1|ED|PDFReport^PDFReport||PDF^Base64^JVBERi0xLjQKJeLjz9MKMiAwIG9iago8PC9GaWx0ZXIvRmxhdGVEZWNvZGUvTGVuZ3RoIDQ5Pj5zdHJlYW0

In the above example the string is: OBX|1|ED|PDFReport^PDFReport||PDF^Base64^
and partial byte array is:
JVBERi0xLjQKJeLjz9MKMiAwIG9iago8PC9GaWx0ZXIvRmxhdGVEZWNvZGUvTGVuZ3RoIDQ5Pj5zdHJlYW0


Thank You.

asked 06/26/2011 02:24

patd1's gravatar image

patd1 ♦♦


11 Answers:
Do you have a file in varbinary filed in database? and do you want to fetch it out as a physical file?
if yes; you can simply use data flow task with oledb source as your source query, and EXPORT COLUMN trasnformation. the export column will turn the binary results to physical file with the path and name you provided in source query.
link

answered

reza_rad's gravatar image

reza_rad

Hi patd1,
declaring the variable User::PDfDocument as string after that script componet to change into byte array.please refer below link for your reference.
http://www.chilkatsoft.com/faq/dotnetstrtobytes.html.

link

answered 2011-06-26 at 23:30:39

anandarajpandian's gravatar image

anandarajpandian

Here Import and Export Column (TRansformation Task) are available to work on Binary data.
link

answered 2011-06-26 at 23:36:19

PatelAlpesh's gravatar image

PatelAlpesh

Listening...
link

answered 2011-06-27 at 01:35:28

Goodangel's gravatar image

Goodangel

I don't want to save the binary as a physical file on my end, but embed it in a text file. The text file will be used by another user. I am hoping anandarajpandian's solution would work if the user of this text file will be able to convert the sting to binary to save it as physical file.

Thanks.
link

answered 2011-06-27 at 05:32:16

patd1's gravatar image

patd1

How familiar are you with CLR? It might be an option in this case if you want to create a text file out of the data.
link

answered 2011-06-27 at 05:53:50

Goodangel's gravatar image

Goodangel

Goodangel: I am not familiar with CLR, can you please give me an idea?

Thanks.
link

answered 2011-06-27 at 05:59:32

patd1's gravatar image

patd1

CLR allows you to write C# or VB.Net code and use it within SQL Server. It allows you to basically do "anything" that is possible in C# or VB.Net inside SQL Server

This is a tutorial on CLR and it gives you an idea what its all about

http://www.sqlteam.com/article/writing-clr-stored-procedures-in-charp-introduction-to-charp-part-1

And this is a tutorial on writing text files in CLR. You might want to read your data field into a stream, then write it to the text file in the manner shown in this link

http://www.mssqltips.com/tip.asp?tip=2341

You might need to do a bit of thinking, but with a bit of brains and internet access, you can do a LOT with CLR
link

answered 2011-06-27 at 06:26:25

Goodangel's gravatar image

Goodangel

My SSIS package is doing a lot more data transformation in addition to the above problem described and I have used script tasks in this package to write data to text file. I can use c# code in the script task to do this. I am encountering problem with this last piece because the data that is being read is binary data. I just need to know how to read binary data in execute sql task, I will then use script task to embed it in my text file.

Thanks,

link

answered 2011-06-27 at 06:37:24

patd1's gravatar image

patd1

PatelAlpesh:
Where is the transformation task? I think you forgot to attache a link.

Thanks.
link

answered 2011-06-27 at 06:49:35

patd1's gravatar image

patd1

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

link

answered 2011-06-27 at 09:29:39

PedroCGD's gravatar image

PedroCGD

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

Asked: 06/26/2011 02:24

Seen: 327 times

Last updated: 12/16/2011 05:21