December 19, 2013 at 11:57 am
Hi Everyone,
I had been asked in interview that what are the steps we should take if ssis pkg takes long time to execute?
Please text me in which scenarios the execution time exceeds and what are the steps to be taken.
Hope you people help me out.
Thanks in advance.
Regards,
Sharmi.
December 19, 2013 at 12:16 pm
There's no particular list. It's an open ended question to see how much you know about SSIS package execution and where all the pain points you've experienced are. It's also a technique to show your troubleshooting skills when dealing with problems.
There's no particular flowchart, it's an experience and general knowledge question.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 19, 2013 at 12:42 pm
Hi Craig,
Thanks for your quick response on this.I understood it was question to check how much I am strong in that.But honestly speaking I don't have experience in ssis but have theoretical knowledge
,so I thought I can get Idea from the people who are expertise in ssis pkgs.Thats why raised a question here.
Please share me your thoughts.
Thanks.
December 19, 2013 at 12:57 pm
Evil Kraig F (12/19/2013)
There's no particular list. It's an open ended question to see how much you know about SSIS package execution and where all the pain points you've experienced are. It's also a technique to show your troubleshooting skills when dealing with problems.There's no particular flowchart, it's an experience and general knowledge question.
I agree. I have limited knowledge in SSIS and SQL server, but I have experienced a few pain points and resolved them. I don't know if this is too simplistic, but one should look at the 3 stages of ETL, right ?
Consider an example where one is extracting data from a DB and then loading it to a CSV file.
Lets look at Extraction. When the package is running slowly, we could ask the following questions - Is my query running "fast enough" ? Do I need indexes to make it faster ? Do I need to optimize my query ?
I also suggest that the OP search google for SSIS package running slowly/not fast etc. You might get questions which will show you some pain points. Anyway, if you don't mind, what is your experience in SSIS ?
good luck.
December 19, 2013 at 1:00 pm
sharmili.net (12/19/2013)
Hi Craig,Thanks for your quick response on this.I understood it was question to check how much I am strong in that.But honestly speaking I don't have experience in ssis but have theoretical knowledge
,so I thought I can get Idea from the people who are expertise in ssis pkgs.Thats why raised a question here.
Please share me your thoughts.
Thanks.
As SSIS packages can vary almost infinitely in terms of scope, complexity and solution methodology, there is no specific answer.
A general answer would be along the lines of:
a) Make sure that you understand what the package is supposed to be doing.
b) Use some form of logging which allows you to isolate which component or components are taking an excessive amount of time.
c) Start digging into what those components are doing and how they are doing it.
d) Try some alternative ideas to see whether you can improve things. Fix bugs. Streamline logic. Do your research.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 19, 2013 at 1:11 pm
Hey Blasto,
Yup..I provided the same answer what you have provided here as I have enough 3 years of exp in T-SQL:).I blabbered some thing like improving performance by adding indexes,setting nocount on and avoid replication of procedures..But they corrected me and asked more specifically about execution time of ssis packages:(..
December 19, 2013 at 1:26 pm
Then they're worried about one of two things.
Pre-Compiler/validation times (check into the Execution Results and you'll see them) which you can lookup more information on a number of blogs.
Else, they want you to review the step logs of the job and review timing of components. Unlikely, but possible.
Your answer was a T-SQL optimization technique for slow running procedures. SSIS has a different troubleshooting skillset. My guess is they're getting beaten on by validation for metadata in large or complex packages because they don't use the tricks you need to setup the procedures for easy detection and they're not turning off the pre-validation options.
What you need to do, though, is get more than educational knowledge of SSIS. I'm not sure of your financial situation but student/developer copies of both VS and SQL Server are what you'll need to improve your knowledge. That'll run you ~$200 per machine. The basic rules of those versions of the software is that they're not for multi-user usage and they can't be used for production/public applications, only for development and training.
Then you need to actually build packages that do more than 'grab data from A, dump on B'. Transformations. Loops through folders to load multiple CSVs to a target. Data cleansing/Null cleanups. RAW files and why/when (rarely). Why would you use a Recordset Object in a package, and why you wouldn't, and how do you use them. Row Errors and how to shunt them to something for review while deciding if the error is a package stopper or something that should allow the package to complete without the bad (or malformed) data. Synchronous and non-synchronous transformation components.
That's a reasonable starting list. It gets more advanced as you go down it, but these are things you should at least be familiar with even if you're not an expert at their manipulation.
The problem with your request isn't the request for help or to know what to go understand, it's that you have to be able to read the interviewer by having enough knowledge to understand what they're trying to chase down. At the least, you have to be able to ask the right questions to know where they're trying to go. T-SQL skills are helpful there, but there's a ton of things in SSIS that are completely foreign to anything else. That's why it's so hard to answer your question without being either horribly generic, or incredibly detailed. The detail level would require a book though.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 19, 2013 at 3:32 pm
@Evil Kraig F - From my experience, it does not really help much if you get a fully-loaded, paid version of any ETL tool and some paid tutorials/books.
To be able to learn valuable stuff, you need to face real world problems. You need access to the problems/scenarios which are only found in the industry (not even text books or workshops). You also need the systems (VMs, Different DBs, Crappy database designs etc) along with all the complexities (security restrictions).
Only when you get all these things, you can learn something useful. No text book can give you this. I finished SSIS books in 10-15 days, doing nothing else. But, I learned WAY more through a real project as an unpaid intern.
Also, I doubt if those fancy, cursory 3 day workshops by Microsoft, SAP etc can be of much help either.
Its the classic chicken and egg problem - to get experience, you need a job and vice versa.
December 23, 2013 at 12:05 pm
I guess it would be better to google "ssis package running slowly" or something like that and read each post to learn reasons for slow execution. Some links I got -
These ones caught my attention -
http://sqlblog.com/blogs/rob_farley/archive/2011/02/17/the-ssis-tuning-tip-that-everyone-misses.aspx
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply