SSIS related question

healthcheckinc
healthcheckinc used Ask the Experts™
on
I am trying to incorporate the script tasks in my package but I getthis error on this line of code

Row.OtherData.Split(";"c)

Its says that
'Split' is not a member of 'Microsoft.SqlServer.Dts.Pipeline.BlobColumn'

Not sure what to add to make this work?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Reza RadConsultant, Trainer

Commented:
you must read blob column data to string first,
like this:
Dim result As String = _
System.Text.Encoding.Unicode.GetString(Row.OtherData.GetBlobData(0, CInt(Row.OtherData.Length)))

and then use split function on this string
like this:
result.Split(";"c)


Author

Commented:
The reason I ask is that I put this in a data flow task yesterday and it had no problem. I tried to put it in my package today and it doesnt recognize the Split function. Do I still have to run the code above? Or can I add a reference or something?
Reza RadConsultant, Trainer

Commented:
I think YES .
because this error means that you have blob column and you can not apply string functions like split on it,
of course there maybe another way, and that is change column to other type like dt-str
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
can you take a look at this error. Im seeing the precompile binary code error alot with this. Im just wondering why it worked in the dtsx from yesterday and not here. Im thinkng its something minor. Plus the blob can take up alot of resources....Please see attcahed error
Error.doc
Where is the Blob column coming from? Is this still from a flat file?
Also, it looks like a task has been renamed with a '1' on the end of it. If you put the package out here we can look at it.
Thanks

Author

Commented:
yeah, I dont know if its a blob thing. Ijust have the squiggly line under Row.Split function on the script and when I hover over it it gives me that error. It also says something about the script cpmponent is configured to pre-compile the script but binary code is not found. Check out the error above
Reza RadConsultant, Trainer

Commented:
it's better to upload your full package here
Couple of things to check.
1. Is there a reason you have a Script Component AMOUNT 1? and not just Script Component AMOUNT?
2. In the Flat File Connection Manager for the Source file, the column OtherData is what DataType and Length. (Advanced Tab)
Thanks,

Author

Commented:
I changed the length and it cleared up the blob error. Now I get the error attached
Error.doc
An error like that, "VS_ISBROKEN", is almost always due to an object changed or missing. Open the Script Component and look for compile errors. Sometimes just opening the Script will provide you with an automatic fix.
But like rad said, if you upload the package we could probably help better.
HZ

Author

Commented:
ok here ya go
Package1.txt

Author

Commented:
anything???
Once I opened the package and changed the transfile to point to my copy of the source file, then I opened each Script Component, the Design Script window and then saved it. After that it ran for me.
If you still have issues, pass a long a screen shot of your Data Flow tab and open the Error List tab and send us the errors.
Thanks,
HZ

Author

Commented:
ok heres the problem. My fields are greater than DT_STR 8000. What would I use as my data type in my connection manager for OtherData? Other than DT_STR? Text? If so, how would I write that in the script? Would I have to use the Blob code?
I apologize for not having much time to help with this. There could be another way around this problem, but here is my suggestion. If SSIS will not let you set the size of DT_STR column in the flat file to larger than 8000, then add multiple columns, each 8000. In the Script Component, concatenate the columns together to form the original string again.
So if you determine 20K is the max you will ever have, add 2 more columns, 8000 each. That will give you 24K chars per line.
Does that make sense?
HZ

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial