petegossett wrote:
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#?
If I understand correctly, this should not work, since it's a string and not an array (the output of Directory.GetFiles would be an array, like on line 63). This:
MessageBox.Show(Dts.Variables["sourceFiles"].Value);
Should show a directory where the TIFF files are.
GameboyRMH wrote:
This is the contents of the sourceFiles array after line 63 and not Dts.Variables["sourceFiles"] (which should be a directory path), right?
I think they should be one and the same?
If my Foreach Loop container grabs the subfolder that contains these 2 files - 5820130822010110.0000.TIF and 5820130822010110.0001.TIF - then those files would be passed into the Object variable sourceFiles in the Execute SQL task immediately preceding the Script Task.
What I think I need is a way to pass any/all values from the Object variable into the local C# variable sourceFiles...or at least that's what I thought this code was supposed to do: sourceFiles = Directory.GetFiles(Dts.Variables["sourceFiles"].Value.ToString());
petegossett wrote:
What I think I need is a way to pass any/all values from the Object variable into the local C# variable sourceFiles...or at least that's what I thought this code was supposed to do: sourceFiles = Directory.GetFiles(Dts.Variables["sourceFiles"].Value.ToString());
Right, that will work assuming the database stores directories. If the database stores individual filenames, you need to do something different.
In reply to GameboyRMH:
I can pass whatever values I need into this table, since it's just one I built for this task. So do I need it to contain the full UNC path including filename and extension, for each file in that directory?
EDIT:
I'm feeling a bit confused, so maybe I should give a better overview of what I'm trying to achieve.
1.) I have a source directory with multiple subfolders. Each subfolder represents a single document, and contains one or more TIF images.
2.) I need to create a destination directory(if it does not exist) that will contain an existing portion of the pathname(contained in the str_DestinationPath variable) concatenated with '\' + the value in the claim_number variable.
3.) The TIF files of each source subfolder will be merged into one TIF - presuming the subfolder contains multiple images - and moved to the destination directory as described in #2 above, or if the subfolder only contains a single TIF it will be moved to the destination described in #2.
4.) It is likely each destination directory will end up with multiple merged documents.
My Foreach Loop cycles through subfolder-by-subfolder, only running the Script Task once for each source subfolder.
The sourceFiles array should contain full paths including filename and extension (which the GetFiles method produces). With this code, Dts.Variables["sourceFiles"] should contain a path to a directory full of TIFF files.
GameboyRMH wrote:
The sourceFiles array should contain full paths including filename and extension (which the GetFiles method produces). With this code, Dts.Variables["sourceFiles"] should contain a path to a directory full of TIFF files.
Ok thanks, that makes sense.
I did update the SQL task before the script, and I'm now passing the full UNC path + filename and extension into the sourceFiles Object variable. Unfortunately it didn't change the error message returned. 
Ok, another update. I've dissected the C# script, and determined this line is triggering the error:
sourceFiles = Directory.GetFiles(Dts.Variables["sourceFiles"].Value.ToString());
Hmm...
Okay, if you're using 2008, I'll have to set up an environment to test that.
In reply to petegossett:
Okay, I think based on this conversation, I think I can probably resolve your issue, but I might need to look at the whole package. Are you passing a dataset (the result of a query) into the C# script? If so, you're going to have to do something like this:
http://beyondrelational.com/modules/2/blogs/106/posts/11133/ssis-reading-object-variable-in-script-task.aspx
And then in the loop, you have to assign the field's value to the array, THEN you can pass that array to the function that takes the array of TIF filenames.
In reply to scardeal:
Correct - I have an Object variable that is returning the full UNC name for all TIF files in a given subfolder.
I've looked at the link you posted a couple times now, and was wondering if that might work. Having no prior experience with C# I was hesitant to stray that far from the initial source code I'd found. However, I also understand that because I'm running this in SSIS, not strictly as a C# script, I may need to modify things - I just have no idea what the might be!
Thanks again for the help! I certainly can send you the entire SSIS package if you want to take a look at it. I'm muddling my way through the example you provided and trying to adapt it into the script I'm working with, but I'm not there yet...
Plus, if you're just trying to get all the files in a particular directory, just pass the directory as a string and use this to get your string[] of filenames:
https://msdn.microsoft.com/en-us/library/07wt70x2(v=vs.90).aspx
It should make it a lot less complicated.
In reply to scardeal:
The files in a common directory when I receive them. I need to get the first TIF file(if there is more than 1), then merge any other TIF files into it(creating a multipage TIF) - which is what the whole premise of the C# code I initially found should do.
So I need to pull any/all TIF filenames into the Data Table(or array, whichever can be made to work). If only 1 TIF file exists in the dataset, I can copy it directly to the destination directory. However, if the dataset contains multiple TIF files, they need to be merged together sequentially, based on filename, into a single multipage TIF(the resultant filename isn't particularly important, I was planning on keeping the 1st filename). Then copy it into the destination directory.
Does that explain the goal better?
I think I understood the idea:
1. Point it to the directory of choice.
2. Get a list of files in the directory.
3. stitch together the tif files into a single tif file, if there's more than one.
4. Put stitched tif file into new location.
I've got 1, 2 and 4 working properly, but part 3 seems a little wonky right now. Everything is actually executing, but with 2 files, it seems to stitch together the first file twice and not hit file number 2.
I'll work some more on it tomorrow if I can, or I can send you what I've got.
Got it working right. The original code had a few bugs.
Control Flow/Variable notes:
Create a variable with the source folder path.
Another with the destination TIFF path (including filename). <-- you can modify this to just use the 1st file in series.
Create a script object and link those strings as read-only variables.
References:
Add the System.Drawing reference.
The code as I executed it is available at: dropbox link
In reply to scardeal:
Awesome!!! I'll dig into it now and let you know if I have any questions - thank you SO MUCH!!!! 
In reply to scardeal:
I keep receiving "The type or namespace name 'SSISScriptTaskEntryPointAttributeAttribute' does not exist in the namespace 'Microsoft.SqlServer.Dts.Tasks.ScriptTask'"
I do not have a reference for System.Collections. Could it be because I only have .NET framework 3.5?
Try making the script task and pasting in the namespace region, the Main() and mergeTIFFPage() methods, rather than replacing the entire text.
In reply to scardeal:
Ok, if I'm understanding correctly I need to retain this section of code from the script task I create?
namespace ST_3022c54be0b74700877b586949d80fc2.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
If I retain that code, I can get it to compile and run, but then I get the error: "The script returned a failure result". I presume this error is a result of the IF statement (mergeTiffPages(destination, sf))?
You need to retain the namespace part, the public partial class part and the scriptresults part. The Main() and mergeTiffPages() should be in the public partial class.
I'd suggest that you set a breakpoint in main() and step through it to see where it's breaking. This might well be a .NET version issue, but I haven't yet had a chance to try get it going in .NET 3.5. (and I don't really expect to have lots of time this week).
In reply to scardeal:
No worries, it's a short week for me so I won't have time to work on it much.
I do have one question though - I don't understand the syntax of this line: if (mergeTiffPages(destination, sf))
Is "mergeTiffPages" a variable you're attempting to merge the destination string with the source file/path into, to then test whether the condition is true? I've verified this is where the script is failing.
My version of the mergeTiffPages() function returns a Boolean result (true or false). It returns true if it succeeds, and false if it doesn't. Unless it's borked, in which case it outright fails. So, that line says, if the function mergeTiffPages() returns true, do the block immediately following (a block is defined by {}), but if it returns false, do the else{} block. Think of it like this:
boolean TF = mergeTiffPages();
if (TF == true) {
...TF == true block...
} else {
...TF == false block...
}
If you're stepping through the code in debug mode, at that point, try the "step into" function as opposed to the "step over" function. (I think it's F11 instead of F10.) Then you can trace it through the mergeTiffPages() function and see where that bit of code is breaking.
In reply to scardeal:
Stepping through the script with F11 identified this error: "A generic error occurred in GDI+." I'm digging into it now...
In reply to scardeal:
Update: Success!
I finally stumbled upon the issue - I didn't realize in C# when you copy a file the destination path needs to include the full filename. 
I've verified it works on documents from 1 to 30 pages, and added a few more features to work with my specific task at hand.
Thanks again for all the help!!!
Great job! I'm glad you managed to work it out. I think I might have had to look at the whole package to isolate that error anyway.
