July 8, 2015 at 3:47 pm
I am looking to see if anyone knows or has an approach to updating a recordset contained in an System.Object variable during runtime.
I am trying to execute multiple file actions (plus parsing those files into a set of staging tables) at separate locations in parallel. I know I can do this in C# but I have a business requirement to use SSIS for all ETL operations.
Any one site can have 0 to many of 1 to 3 files. I would like to run multiple sites at the same time, so when all files of all types are completed at that site then go on to the next site in the list. I know I can do a single site at a time in a foreach loop but if I can run lets say 3-5 sites concurrently then I should be able to save execution time.
My thought is to have a recordset of the sites, when any 1 of the 3 (or more) "control flows" is open, update the recordset to let it know that site being actioned, when that site is complete, update the recordset that the site is completed, and so on.
Or am I running in the wrong direction? Thoughts, ideas? Or is my description clear as mud?
Thanks!
July 9, 2015 at 5:22 am
logitestus (7/8/2015)
I am trying to execute multiple file actions (plus parsing those files into a set of staging tables) at separate locations in parallel. I know I can do this in C# but I have a business requirement to use SSIS for all ETL operations.
So why aren't you using a C#.net script task within SSIS to do this?
Any one site can have 0 to many of 1 to 3 files. I would like to run multiple sites at the same time, so when all files of all types are completed at that site then go on to the next site in the list. I know I can do a single site at a time in a foreach loop but if I can run lets say 3-5 sites concurrently then I should be able to save execution time.
My thought is to have a recordset of the sites, when any 1 of the 3 (or more) "control flows" is open, update the recordset to let it know that site being actioned, when that site is complete, update the recordset that the site is completed, and so on.
Or am I running in the wrong direction? Thoughts, ideas? Or is my description clear as mud?
I haven't done too much work with recordsets themselves. I think the last time I did, I basically used derived column transformations to fix it then went on with the rest of the workflow. But SSIS lends itself to all sorts of things, like creating temp tables (you'd have to make sure your package keeps its thread open for the duration) or using Script Tasks.
Again, since you already know the C# solution, I'd advise importing that solution into a SSIS Script Task and then you've made life easy for yourself.
July 9, 2015 at 10:03 am
Thanks for the suggestion Brandie. Oh how I wish I could do that. It would make my life much simpler but since I was SOB who created the business rule (I am trying to clean up the environment from a hodge-podge of undocumented, unversioned batch scripts calling vbscript or calling custom VB6 code to a documented versioned set of SSIS packages), it wouldn't fly past my "code review" process. 🙁
A couple ideas I've had:
1) a logging SQL table
2) using a queue task (I have never used this but it might be fun to learn something new)
3) just go with the standard boring old serial approach
July 9, 2015 at 10:59 am
So your own process prevents you from using the full suite of SSIS tools to accomplish your task?
That seems a little excessive to me. But yes, if you can't use native SSIS tools to do the easy bit, then throw the recordset into a SQL table and update it that way or use a derived column task to massage your data.
July 9, 2015 at 11:00 am
BTW, you do know how to use annotations to document an SSIS package, right?
July 9, 2015 at 11:48 am
On annotations, I haven't used them but its a great suggestion.
On the business rule, most of the current data transfer processes are using whatever tools the previous technical folks felt like using at the moment. I run into macro-enabled Excel spreadsheets, vbscript files converted into executables being run by Scheduled Tasks, SSRS reports to Access to Excel back to another database, and other such insanity. After I spent 2 weeks attempting to fix one of those MacGuyver-esque processes, I was able to convince my boss that we needed to replace these processes with something more sustainable. Since I suggested SSIS, I am being held to that, in an extreme sense (internal politics are so wonderful).
Regardless of my situation, thank you for your time and suggestions!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply