November 22, 2009 at 10:43 pm
I am working on converting some .vbs scripts I have to SSIS. I have one piece which I can't seem to come up with a solution for and could use some guidance.
Some background
The purpose of this particular .vbs script that I am converting is to export data out from a series of customer databases to an MS-Access database. Now there are hundreds of customer databases. A select few are participating in this export process (with the potential for more to be added on at any given time) and not all customers get the same data exported (there is a series of about 30 views that are used and any number of these views can be "on" or "off"). The customers that participate, their export database config, and the export views for that customer database are all stored in control tables in one central DB.
I have most of it working: I can gather the control information, loop on it, create the access DB and schema... but the problem I am stuck on is actually dynamically pumping the data out from the views. This requires use of a dynamic source and destination (determined in the control tables) and also dynamic views since I don't know which ones will be used until run time. I was thinking I have 3 options....
1) Build a source, a transform, and a destination through some VB Script Task. This, if possible, seems like it would be the best option but I haven't found many good sources on how to do this. If someone could help me find information on doing this and whether or not this is the best way to go I would really appreciate it.
2) Build a procedure that dynamically generates insert statements which will get processed into the MS-Access database individually. I don't like this solution but it seems very flexible even though it's ugly and probably very slow.
3) Abandon the control tables and generate a separate package for each client. I do not want to do this because it means that any change to the export configuration would require an update to each package.
Please chime in, suggestions & ideas are greatly appreciated.
Thanks,
John
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 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply