October 19, 2016 at 4:53 am
Hi Sir,Madam,
we are working on MS SSIS 2010 version and database version is MS SQL Server 2012. when we are saving SSIS integration services jobs taking very long time. could you please tell us how can we resolve this slowness issue.
and we are having windows 2012, 16 GB RAM, please ask me if i need to provide more info
thanks
October 19, 2016 at 6:03 am
ranala (10/19/2016)
Hi Sir,Madam,we are working on MS SSIS 2010 version and database version is MS SQL Server 2012. when we are saving SSIS integration services jobs taking very long time. could you please tell us how can we resolve this slowness issue.
and we are having windows 2012, 16 GB RAM, please ask me if i need to provide more info
thanks
Are you talking about creating a SQL Agent job which runs SSIS packages? If not, please describe what sort of 'job' you are saving.
October 20, 2016 at 4:13 am
May be my inputs are not clear yet.
we are loading data as like below. Please help asap.
1. reading data from MS Excel and CSV
2. applying filters to load data in proper way
3. finally loading into MS SQL Server studio (2012) version
here is our steps names
a. flat file source
b. conditional split
3. Derived column
4. sort
5. merge join
6. lookup
7. OLE DB source
8. OLE DB target
hope, some what i explained in better way.
October 20, 2016 at 6:10 am
ranala (10/20/2016)
May be my inputs are not clear yet.we are loading data as like below. Please help asap.
1. reading data from MS Excel and CSV
2. applying filters to load data in proper way
3. finally loading into MS SQL Server studio (2012) version
here is our steps names
a. flat file source
b. conditional split
3. Derived column
4. sort
5. merge join
6. lookup
7. OLE DB source
8. OLE DB target
hope, some what i explained in better way.
OK, this is helpful.
Sorts in SSIS are fully blocking transformations and should be avoided if at all possible. This is what is sucking up most of the execution time, I would guess.
Your merge join is partially blocking and also best avoided if possible.
If you can re-engineer your design such that these two transformations are handled by the SQL Server DB engine, rather than by SSIS, you will almost certainly see a huge performance improvement.
If you need any help with ideas on how to do that, you'll need to provide more details of your process.
October 20, 2016 at 6:39 am
I tend to do all my merging via T-SQL as it's much more efficient (certainly for what I want to do)
There's a very good tutorial here (with all 4 SCD types)
http://www.sqlservercentral.com/articles/MERGE/73805/
I'd recommend loading the Excel/csv data to some sort of staging SQL table
With the merge, make the source this staging table and the destination the final merged table
You can use an Execute SQL Task for the merge
- Damian
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply