Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Why Use SSIS? Expand / Collapse
Author
Message
Posted Friday, May 16, 2008 10:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 2:21 PM
Points: 136, Visits: 1,240
Basically the issue is-

If i run t-sql srcipts, to pull data from the staging area, remove duplicates, validate , data and perform lookups i can do it for each of the 6 channels that i pull data from in one day.

However, replicating the same using SSIS packages takes about a day(at best!!) for Each channel.

I used these tools/packages to basically automate all my ETL/Data cleansing tasks, it works perfectly fine, but takes agonizingly long.

What can i do to improve the performance? i mean i have done all the basic things like setting the cache levels when i do a lookup etc. Or should i just use my T-SQL scripts.

Post #502237
Posted Friday, May 16, 2008 11:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, June 26, 2014 6:11 AM
Points: 1,330, Visits: 815
If you have TSQL scripts that will do what you need, I would put them into a job and let it do its thing. SSIS does have its place, specifically when moving data to/from different formats but I wouldn't bother with having it run scripts that can run simply inside of a job.
Post #502247
Posted Monday, May 19, 2008 10:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 2:21 PM
Points: 136, Visits: 1,240
Thanks, however, now that i built it, i am looking work/experiment with it for a few days.

I am now facing the following situation-(am not sure if i should create separate topic for this)--

In one package

1] Import data(source)
2] Multicast to 3 Lookups
3] I then do insert and update accordingly

There are over 8000 rows in the source table, when the package executes, it caches 4,300 rows (since i set it to full cache mode), then does a lookup of 2,100, 4300 and 2161 rows.

It then does an update or insert accordingly, but the problem is when it does this forst 'batch' , the flow 'pauses'. I am not sure at this point what is going on.

Has the package finishes executing, has it run into an error(nothing is visible since it stays in the "yellow" ) mode.

It inserts the "error" fields also correctly, so the settings looks correct, but im not sure why it seems to hang.

IF anyone has any idea please do let me know...!

thanks
Post #502935
Posted Tuesday, May 20, 2008 4:17 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 10, 2012 9:49 AM
Points: 800, Visits: 1,759
I noticed the indefinite yellow state when destination table was locked. maybe you could run table operations in sequence?
Do you use fast load destination for inserts?


Piotr


...and your only reply is slàinte mhath
Post #503433
Posted Tuesday, May 20, 2008 2:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 2:21 PM
Points: 136, Visits: 1,240
Yes i use the fast load option when finally inserting into an OLE DB destination, what is the difference between the various options?
Post #504040
Posted Tuesday, May 20, 2008 6:13 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 10, 2012 9:49 AM
Points: 800, Visits: 1,759
Well the options are documented in help, one of them allows for locking the table during insert. This in my opinion can cause blocking. These amounts of rows are not very dramatic, What is performance of data sources and data destinations? Do you call sql statement for each of the rows? Can you indicate task or component that takes longest time to turn green apart from the blocking ones?

Piotr


...and your only reply is slàinte mhath
Post #504143
Posted Wednesday, May 21, 2008 11:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 25, 2010 10:10 AM
Points: 297, Visits: 267
Underdog,

Have these packages been deployed to a production or are you just running them locally? Just a hunch but that may be why you're seeing such shotty performance...

Also, you said that your T-SQL scripts run from a staging area. This implies there is already some ETL processing that occurs before your T-SQL scripts are executed. Do you have direct access to the source files? If so you may look at taking the source data and performing all the transforms in memory (new feature SSIS brings to the table) which could add some value towards your performance goals overall...



Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...
Post #504696
Posted Monday, January 7, 2013 6:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 7, 2013 6:31 AM
Points: 3, Visits: 14
I am making Lakhs of WCF calls from SSIS. What is the best way to do it? Performance is key concern here.
Post #1403571
Posted Monday, January 7, 2013 6:49 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 23,294, Visits: 32,029
Gurinder-356063 (1/7/2013)
I am making Lakhs of WCF calls from SSIS. What is the best way to do it? Performance is key concern here.


This is a 4+ year old thread. You really should post new questions in their own thread.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1403577
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse