March 3, 2009 at 7:28 am
I am working on a project where we are receiving a large number of files in a compressed format. I have developed a set of tasks before and after the data flow that is going to be re-used over a hundred times. Currently I've grouped them into a sequence container. To create a new package I start with a template package that has this container. Then I change the flat file source, some private variables, two execute SQL tasks that creates the staging table and the PK, and the data flow task.
I'm thinking of creating a custom connection and custom data flow to encapsulate this functionality. The custom connection would be for compressed files so I would not have to unzip them to use the flat file connecter. Since we receive these all from a single source they are very consistent. The custom connection would unzip them automatically to a temp folder and inherit the flat file connection.
The custom data flow would have a couple extra properties for the sql and private variables. Then it would need to execute this on the connection. They are always the same connection as the data flow destination so that will simplify configuration in only having to change one destination.
I've used SSIS for a while but I'm no expert. I've never written and custom component. I'm a DBA and not a .NET developer however I have been a developer in the past (VB6). I'm writing this posting to get feedback on the complexity of doing something like this and to see if there might be an easier way to do it. I appreciate any help I can get on this.
March 3, 2009 at 7:43 am
It sounds like the correct approach.
Building a custom component is not that difficult. You will need to do some .Net development, but for what you are doing, it should not be that complex.
There are samples on the MSDN website, but for a couple of free projects that are complete, I would suggest you go to: www.sqlbi.com
March 3, 2009 at 9:04 am
I agree with Michael. Your approach seems good and creating custom components is not a difficult task if you get to the right resources.
Here is a link to a blog post about my experience creating a custom component which has links to resources I used to create the component:
http://wiseman-wiseguy.blogspot.com/2008/08/ssis-error-logging-custom-component.html
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
March 3, 2009 at 12:31 pm
Thank you both for your help and feedback. Especially for the resources. I have three different sources with different process flows. I will probably do this for each of them if it works out for the first.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply