January 17, 2010 at 9:34 pm
No one's replied on this and I'm now at the point where I've built a procedure that generates INSERT statements and it seems to be working properly, but processing those INSERT statements is really slow! I'm only pumping data at a rate of about 1 MB every 2 minutes. This is not going to work for the amount of data I need to pump.
The old way was automated 90% except for the actual Data Flow task because I could not parameterize the source and destination.
Can someone please suggest another solution? I am thinking at this point the only way I will be able to do this is to write the package in C# and abandon the GUI altogether. This would give me the flexibility of being able to dynamically generate my sources and destinations for the data flow but it will be a lot more cost in development time.
Has anyone had experience in doing this? Even if I write the SSIS package in C# I will still need to dynamically create the data flow source / destination / and fields because the schema for the exported views may change at any time. We are using some control tables to indicate which views the data should come from.
Thanks,
John
January 18, 2010 at 12:24 am
Hi Johnny,
we quite regularly run across problems like this. In our company, it mostly because our customers have maintained their databases themselves for a long time and are used to work with ad-hoc queries, views and so on. Now the responsability shifts to the IT department, but the same flexibility is demanded (although not always wanted from the IT people :-).
I would opt for a programmed solution. SSIS seems wuite fit when it comes to throughput, but processing unnknown data is not the core competence of SSIS.
I would set up a .NET application that parses the config database, creates the corresponding select statements, performs the transformations in code and the writers the data back using whatever optimized access is required.
An alternative way we have gon is to have a "super-table" consisting of any thinkable row in any of the source databases and importing the data there. While it's in the suprt-table, you can handle it with SSIS, but im- and exporting it to the external DBs is subject to .NET code (on our part) as well.
just my 2 cents
Guenter
January 18, 2010 at 6:47 am
Thanks for the reply. So I went online and found this section in the MSDN http://msdn.microsoft.com/en-us/library/ms345167.aspx. I think this is pretty much what I need to get started. I only really need to program the data flow but I think if I am going to go that far I might as well go and program the whole control flow as well.
Basically we're pulling info from 30+ views right now whose definition may change (it does maybe 2-3 times / year) and we may be adding a few more views in the not so distant future. Each customer database has the same views. So basically this data gets sucked out and imported to a corresponding access DB which is then compressed and encrypted with a password.
So if what I'm understanding is correct, just go with the C# application.
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply