<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Integration Services
»
Why Use SSIS?
Why Use SSIS?
Rate Topic
Display Mode
Topic Options
Author
Message
Pac123
Pac123
Posted Friday, May 16, 2008 10:48 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, May 17, 2013 4:23 PM
Points: 131,
Visits: 1,026
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
jim.powers
jim.powers
Posted Friday, May 16, 2008 11:13 AM
Ten Centuries
Group: General Forum Members
Last Login: Monday, February 04, 2013 10:00 AM
Points: 1,329,
Visits: 803
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
Pac123
Pac123
Posted Monday, May 19, 2008 10:09 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, May 17, 2013 4:23 PM
Points: 131,
Visits: 1,026
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
Piotr.Rodak
Piotr.Rodak
Posted Tuesday, May 20, 2008 4:17 AM
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
Pac123
Pac123
Posted Tuesday, May 20, 2008 2:18 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, May 17, 2013 4:23 PM
Points: 131,
Visits: 1,026
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
Piotr.Rodak
Piotr.Rodak
Posted Tuesday, May 20, 2008 6:13 PM
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
Ben Sullins-437405
Ben Sullins-437405
Posted Wednesday, May 21, 2008 11:10 AM
SSC 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
Gurinder-356063
Gurinder-356063
Posted Monday, January 07, 2013 6:32 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, January 07, 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
Lynn Pettis
Lynn Pettis
Posted Monday, January 07, 2013 6:49 AM
SSC-Insane
Group: General Forum Members
Last Login: Yesterday @ 11:17 PM
Points: 21,617,
Visits: 27,450
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 »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
