October 28, 2009 at 5:50 pm
I'm about to start a new project, which will automate the import of data from lots of files in various formats (text, Excel, ...), scrub and normalise the data, and build a database. I'm familiar with using SQL, Perl and VB with Access and Oracle, but haven't used SQL Server 2008 Integration Services before, nor T-SQL for that matter.
Previously I'd have written a program in VB which would do something like: [font="Comic Sans MS"]
Load list of files
For each file
-- Read the header, get field names, lookup in hashtable to convert to standard field names
-- for each row ADD new record
---- for each field
------ Check and assign to the right field
---- Save record
-- Close file[/font]
With SS 2008, is it better to use SSIS rather than code it all in (say) VB? Or should I stick to what I know?
If I use a mixture of VB and SSIS code blocks, is it easy to call SSIS functions from VB?
The application will need to run unattended from the Task Scheduler, and be maintainable!
October 29, 2009 at 1:59 am
David Data (10/28/2009)
...With SS 2008, is it better to use SSIS rather than code it all in (say) VB? Or should I stick to what I know?
If I use a mixture of VB and SSIS code blocks, is it easy to call SSIS functions from VB?
The application will need to run unattended from the Task Scheduler, and be maintainable!
I would use as much SSIS Tasks as possible. You can insert Script Tasks, that can contain VB or C# Code.
Those Script Tasks give you statements that are commented out and show you how to access Variables or other stuff. There are also For Each Elements, that can easily enumerate through folders. Variables can be set for almost every property.
You can set up a job in the sql server agent to schedule your package.
Maintainability will be much better than pure code, as you can see what happens by looking on your control flow.
The most important things about SSIS can be learned in the tutorial that comes with the documentation.
October 29, 2009 at 7:31 am
I'd do SSIS. It is designed and optimized to do this. I think it will likely work better. Realize that there will be a learning curve.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 30, 2009 at 7:01 am
Thanks for the quick and helpful replies. So SSIS It is then!
David
October 30, 2009 at 1:21 pm
We probably have over a hundred SSIS packages running here, some daily, monthly, some on demand. I think it should work well for you, but as previously mentioned, there is a learning curve. My recommendations:
1) Get a book - Wrox Professional Integration Services is a good one.
2) Understand Package Configurations, Expressions and Variables. These go a long way towards flexibilty and maintainability.
3) You mentioned Task Scheduler for execution. I would suggest using the SQL Agent. I might be wrong, but I think that give syou a little better control and administration of the jobs.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply