Tech question for the geeks: SSIS & C#

I'm running into an error and think I know what it means - at least generally speaking - but I've not been able to find anything relevant online to point me toward a solution.

I have an SSIS package that uses two different Script Tasks. The 2nd of which uses some C# code I found online that (should) combine single-page TIF images into a single multi-page file. I'm basically C# ignorant, but managed to get it into the script task and was able to build it without any errors. However, when I run the package I get the following message:

"...TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.DirectoryNotFoundException: Could not find a part of the path 'c:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\System.__ComObject'..."

I've discovered that "System.__ComObject" means it's looking for an unknown System object that it is unable to find. Checking the c:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\ directory, there are no files named "System" contained within. I did* find some system dll files in the .NET directory, and tried copying them into the directory above, but still no luck.

Before I post this over on Stack Overflow, etc. and deal with all that noise, can any of you provide any further insight into what I'm doing wrong, or how I can troubleshoot this further? The full error message is below:

SSIS package "Package.dtsx" starting. Error: 0x1 at Merge Tiffs: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.DirectoryNotFoundException: Could not find a part of the path 'c:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\System.__ComObject'. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption) at System.IO.Directory.GetFiles(String path, String searchPattern, SearchOption searchOption) at System.IO.Directory.GetFiles(String path) at ST_20f6839868194421a379dbf1f80ad4a0.csproj.ScriptMain.Main() --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() Task failed: Merge Tiffs Warning: 0x80019002 at Parse Filenames by Subfolder: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "Package.dtsx" finished: Failure.

Does the C# code that you are using maybe reference a library that you don't have?

In reply to szeis4cookie:

Quite possibly. I had to add "using System.Drawing", but I was able to select it from the list - I don't know whether that means I actually have it though?

One other point that may/may not be relevant - the SSIS package is stored on a remote drive, not my local machine. Would that cause problems? I've built plenty of SSIS packages before, but I've never done as complex of script as this one.

Put in some debug code to find out what the string "maybeFullPath" is before the crash.

Edit: Looks like this guy is having a similar problem with the same code:

https://social.msdn.microsoft.com/Forums/vstudio/en-US/c7655fbc-5265-4508-8542-64f65df8db2a/file-not-supporting-for-image-system-file-not-found-exception?forum=netfxbcl

Looks like "maybeFullPath" is a variable in the .NET framework, so you won't be able to debug it. The program crashes when it tries to get a list of files in that directory. It might be a file permissions problem like this:

http://www.codeproject.com/Questions/559999/SystemplusIOplusFileplusCopyplusPermissions

In reply to GameboyRMH:

Awesome!

I'm pretty sure it's not permissions-related, as I'm running SSIS on my local PC, and have Full Control rights over the network folder.

From this comment in the first link: "You have hard coded the path in your code." It appears the Chrysanthemum.jpg file is the one in question. Correct?

The error I receive I think is indicating a missing dll: "Could not find a part of the path 'c:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\System.__ComObject'"

Is System.__ComObject a dll???

Could be a .dll for a library this script needs, but it's hard to tell. I'm pretty sure it's not looking for "System.__ComObject.dll" Maybe some image-handling library?

If you send me a link to the C# code you found online for combining TIFFs I could have a look.

In reply to GameboyRMH:

Thank you - I've been stumbling my way through this for several days now...

This is the original code I started with: http://dotnetconclave.blogspot.in/2013/03/c-code-to-split-merge-tiff-files.html

I think I still have your email, if so I'll send you the full code in my script.

Thanks again!!!

One other note - and possibly clue - after I've saved the package & reopened it, if I open up the script I get a message "The type or namespace name 'Drawing' does not exist in the namespace 'System"(are you missing an assembly reference?)"

So then I right-click on References and add it again, and the error message goes away...but maybe it's not actually being added???

There's also the super-secret configuration page that adds in libraries that are supposed to be there anyway. I don't remember if the configuration is when you have a script open to edit or from the project configuration, but I think it's script. And you should go to the super-other-secret configuration page in the project and set it to run as 32 bit, just in case. Anything that touches Access (the Jet engine) will blow up if you don't do that. Your library may also be 32 bit. The error you get from that one is something typically microsoft, like "your doorbell knocker has morphed."

In reply to Dr. Hess:

I did find that double-clicking on the Properties in the Script Task allowed me to update the .NET version from 2.0 to 3.5, and now I no longer get the error for System.Drawing every time I open the script. Unfortunately, it still crashes with the same error when I run it...

I'll search for the 32-bit setting and try that out. Thanks!

EDIT: Ok, I tried 32-bit mode, but still get the exact same error.

You might have to set your target framework to ".NET Framework 4.0" as shown here:

https://stackoverflow.com/questions/8553136/system-drawing-namespace-not-found-under-console-application

OK I looked at the code you sent me, nothing obviously wrong and it looks like you're using all standard libraries, although the error you're getting about System.Drawing could be related. I'll have to install a C# IDE later and give the image manipulation part a try.

A big part of my job is working with SSIS and C#... I'm a BI consultant.

What version of SSIS are you using? Do you just have BIDS/SSDT installed or full-blown Visual Studio?

I might just see if I can put together a sample package and tell you what I had to do, but I need to know what version.

In reply to GameboyRMH:

Thanks again...I'm going to need to make a trip down there some day just to buy you dinner & a round or 2 to make up for all your help!

In reply to scardeal:

Thank you!

Visual Studio 2008 version 9.0.30729 it's just BIDS, not the full VS. I can send you the exact script I used(or post it here, for that matter) if you want?

Hmm...one possibly relevant discovery: I set a Breakpoint on the script task that keeps crashing. I'm passing several variables into it, including an Object type variable containing one or more TIF filenames needing merged, which is assigned to an Array in the C# code.

When I review the Local values, this variable has a value of "System.__ComObject"...so now I'm wondering if the script isn't able to process the array for some reason?

Did I ever mention how much I hate SSIS packages?

In reply to petegossett:

Why are you sending the filenames as objects? Any reason it can't be a string?

scardeal wrote: In reply to petegossett: Why are you sending the filenames as objects? Any reason it can't be a string?

He's not really. The filename is included in the Dts.Variables object (which comes from a database row). It's immediately converted to a string and put into its own variable.

Now that I look closely at that, I find the use of that variable quite questionable. It gets declared like an array, then gets a value assigned to it like a normal string, then gets used as an array again later on. I don't see how that could work properly.

Edit: OK assuming the value coming from the database is a directory path, it should work as it will return an array rather than a string.

scardeal wrote: In reply to petegossett: Why are you sending the filenames as objects? Any reason it can't be a string?

This is my first experience with C#, and not knowing anything about it I copied the code from this site: http://dotnetconclave.blogspot.in/2013/03/c-code-to-split-merge-tiff-files.html

I believe that code uses an array so it can merge the individual TIF images into a single file? I don't pretend to begin to grasp how C# actually merges the files though - it does seem to me that the array is only holding the filename?

GameboyRMH wrote: Now that I look closely at that, I find the use of that variable quite questionable. It gets declared like an array, then gets a value assigned to it like a normal string, then gets used as an array again later on. I don't see how that could work properly.

Keep in mind I added the following code, mostly as a random WAG to get the error messages to go away, and definitely not having a clue what I'm doing:

string[] sourceFiles = null;
string str_DestinationPath = null;
string claim_number = null;

I think the "string[]" command denotes an array, correct?

This line(I believe) copies the TIF file to the destination, if the source directory only contains a single page "System.IO.File.Copy((string)sourceFiles[0], str_DestinationPath + "\" + claim_number, true);" ???

GameboyRMH wrote: Edit: OK assuming the value coming from the database is a directory path, it should work as it will return an array rather than a string.

The values returned are the individual filenames contained within a subfolder(the documents are grouped by subfolder). In my test directory I have 3 subfolders:

The first folder only contains one file: 20130618_5820130618007499.TIF
The 2nd folder has two files: 5820130822010110.0000.TIF and 5820130822010110.0001.TIF
The 3rd folder has 31 files, but I won't bother listing them all...

I verified these are the actual values returned FWIW.

Ok, another small update/question. I finally figured out how to get a Message Box to display in the C# code(I'd had it at the end of the script, but it never made it that far), and I have 3 message boxes setup - one for each of the 3 variables I'm passing into the Script Task.

For the message box returning the Object value, I have the following code: MessageBox.Show(String.Format("{0}",Dts.Variables["sourceFiles"].Value));

I think that should return a string representing the filename for the 1st TIF file in the array, correct? All I get in the message box is "System.___ComObject", so I wonder if the problem might be in passing the value(s) from SQL to C#?

petegossett wrote:

GameboyRMH wrote: Edit: OK assuming the value coming from the database is a directory path, it should work as it will return an array rather than a string.

The values returned are the individual filenames contained within a subfolder(the documents are grouped by subfolder). In my test directory I have 3 subfolders: The first folder only contains one file: 20130618_5820130618007499.TIF The 2nd folder has two files: 5820130822010110.0000.TIF and 5820130822010110.0001.TIF The 3rd folder has 31 files, but I won't bother listing them all... I verified these are the actual values returned FWIW.

This is the contents of the sourceFiles array after line 63 and not Dts.Variables["sourceFiles"] (which should be a directory path), right?