SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Why Use SSIS?


Why Use SSIS?

Author
Message
Pac123
Pac123
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 1407
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.
jim.powers
jim.powers
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2712 Visits: 871
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.
Pac123
Pac123
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 1407
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
Piotr.Rodak
Piotr.Rodak
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2660 Visits: 1761
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
Pac123
Pac123
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 1407
Yes i use the fast load option when finally inserting into an OLE DB destination, what is the difference between the various options?
Piotr.Rodak
Piotr.Rodak
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2660 Visits: 1761
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
Ben Sullins-437405
Ben Sullins-437405
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1113 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...
Gurinder-356063
Gurinder-356063
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 14
I am making Lakhs of WCF calls from SSIS. What is the best way to do it? Performance is key concern here.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89700 Visits: 38934
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.

Cool
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search