November 4, 2008 at 4:39 am
Hi all.
This I suspect is quite a easy one for you, but with limited SSIS experience I not sure how to approach.
we have 1 data source table, from which I will need to extract:
- container type
- voyage type
- sizes
The predicterment is that the data source is via a 3rd party ODBC driver, and as such, it's basically very slow, as it reads all the records into memory before executing the SQL in the OLE Source adapter.
Something that should really take a second or two, takes about 3 minutes. Of course if I have to do this 3 times it's 9 minutes of processing instead of < 10 seconds.
my idea is to read the source once and then use a multicast task to split into three paths:
- container type
- voyage type
- sizes
basically I need to find the distinct values for each of these paths before updating the relevant dimensions. Now I appreciate, I may need to use staging tables prior to doing the SCD process, but my question is this:
I want to split the source into three paths then on each perform a select distinct statement, before passing these to either a SCD task or a staging table. Can this be done? Do I need to create some sort of in memory array for each?
ps: I know this is a proprietary system, hence the types are not in seperate tables as a good database design would have. They are simply held against each record.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
November 4, 2008 at 5:14 am
A SORT component has the option to only return distinct values. Sort by and include just the one column you want. There is a checkbox for removing the duplicated in the editor.
I would look carefully into tweaking the ODBC connection settings to try to get the performance to improve. You may want to contact the vendor to assistance.
November 4, 2008 at 5:35 am
Thanks Michael - I didn't realise the sort component could do that, it's just what I need.
I'll look into the ODBC stuff, but I'm not sure if it can be improved or not, it's accessing a C-ISAM database. It seems to be slower reading the ODBC source table in SSIS than using when you run a select statement in SSMS. I suspect maybe it's validating meta data or something.
Thanks again though.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply