Interview process and questions for SSIS,SSRS
Hi I am a SQL SERVER Developer and mostly write scripts/procedures so on i.e. developer functionality. I got an interview for SSIS/SSRS job and i dont know in which way does a developer relate to that job, I am having an 1:1 interview in a few days and i am really nervous as to what to expect in the interview what kind of questions would i have to face can i prepare in 4 days for that interview. I assume that they want someone to create SSIS projects to handle the terabytes of data that they keep receiving and so in SSIS project Data flow(Sources,Transformation,Destination)/Control Flow(container,tasks,precedence constraints) (foreach loop( tasks(underlying procedures ? ? ? ? output) which call the web service )-- output to master tables) might be the daily job scenario, create log entries so on Or It could be that they require a Sql Developer to write scripts which will be used in SSIS tasks. In which case i should be going through SQL SERVER concepts and not SSIS concepts.I am confused because my CV clearly states that i am a Oracle,SQL Server developer and didnt mention SSIS,SSRS anywhere. I dont know what is being expected i am currently reading about SSIS questions what is control flow/data flow, how do u log events in ssis,how do u handle errors, how do u schedule ssis packages to run on the fly, how do u run stored procedures and get data. so on Should i be instead going through SQL Server concepts Any advice from Sql Developers, SSIS interview attended people, interview questions, links would be great. Thank you.
August 22nd, 2009 10:39am

Definitely you should go through SQL Server concepts.As far as SSIS is concerned you have mentioned logging,error handling, scheduling but you missed one one of the most important part ie. deployment of SSIS packages. You must go through Package Configurations and how to deploy a package. What are the various modes of deployment?Also, you should have some information about SSIS Package tunningGo through following linkhttp://www.sql-server-performance.com/articles/dba/package_configuration_2005_p1.aspxYou can find various other links also using "every ones friend" Google.Nitesh Rai- Please mark the post as answered if it answers your question
Free Windows Admin Tool Kit Click here and download it now
August 22nd, 2009 12:33pm

Hi Rob, You need to know ur SQL Concepts. Nitesh has taken care of almost all theoretical part. You also need to know about the check point concept. You need to know all the shapes available in control flow & DFT. Apart from these things u should be able to think outside the box to provide solution to everyday issues. One of the most used concepts Expressions should be given extra care. difference between DFT & Control Flow, Lookup & Merge Join, For & For Each loop, OLEDB Destination & SQL SERVER DEstination. These are few to mention and 4 days is enough to learn the theory, but if the interviewer knows SSIS well he will be able to judge exactly how much you have worked on it. Best of luck though and dont try to speak what u dont know.Hope this helps !! Please close the threads once answered - Sudeep
August 22nd, 2009 1:36pm

Hi Nitesh, Sudeep Thanks a lot for the guidance, I went through Sql concepts yesterday like Indexes-Clustured-1/table,Non Clustured-249/table,Best Practices,Execution Plan,Sql Index tuning wizard View-Views,Materialized Views Normalization-laws of normalization,effective usage In built Functions-Aggregate,validate,bit wise operators, @@identity, scope_identity(),Iden_current() Transactions-xact_abort Triggers-SqlServer 6.5 -3 triggers/sction,sql server 7-multiple triggers, no control over order of triggers,sp_settriggerorder not included in sqlsrv 7.Constraints are faster and better than triggers which are used for referential integrity checks. Sql Server 2000 Pre triggers introduced Error Handling-RAISERROR,TRY CATCH,case select into #ERROR Table Stored Procedures-Adv over normal procedures Packages,Global Variables Synonyms-Act as an alias to the DB objects being referred in current scene, adv underlying changes would not need code changes. DDL-Data definition Language,CREATE,ALTER,DROP DML-Data Modification Language SELECT,INSERT,UPDATE,DELETE It was mentioned that the candidate has to know/the job role involves creating DTS packages,SSIS,SSRS so on. I am under the assumption that even though i may not be an expert in SSIS, SSRS . DTS is a fairly old tool which shouldnt be so hard to understand, learn than SSIS,SSRS. I never created DTS packages, they are Extract Transform Load tools in Sql Server to Design,Transform,Consolidate data meaning to import the data from different sources such as textfiles,flat files,web service,another RDBMS transform means to transform the data which is imported into the required format, SQL Server,Oracle it depends on the buisness needs and the necessary transformations would be applied before loading data. Here a doubt does transofrmations here refer to cleaning the data,slicing data by searching for ';' in the strings,removing bad words. Consolidate Data: Tranfer Indexes,Login,Views,Triggers and user defined data Also i started going through DTS packages how to create them,the various steps,deployement, triggers I am going through DTS first rather than SSIS,SSRS is because DTS is the basics i assume and if i get a hang of it then i would be in a better position to understand SSIS I havent yet started going through SSIS, i fear that questions will be asked from SSIS, as you said how can you create/design a SSIS package,What are Control Flow and Data Flow,How do u perform error handling in the package,what are the various error handling process that you will be using in the package and how will you go on logging them.How would you schedule a SSIS package to run every night this would require create jobs(this is what i assume would be the requirement create SSIS package-->contacts Web Server everynight to get latest data, perform the necessary checks on it, load the data into SQL Server, Generate Reports everyday in Crystal Reports). I am done for :), this is tense man, i can understand all this this but didnt really create or handled the process, i mean i understand these things call the web service, sometimes they fail and therefore it needs to be logged the cause and source and re run the load script. But i just know only this in theory not in practice. I mean even in theory i have an overall idea only. But it could be that they might be looking for a starter in SSIS rather than pro or they might be looking for someone who can create DTS packages for a general scenario as described above. My main concern is i cant really practice doing this, i wouldnt even know where to beginm i got Visual Studio 2008,SQL Server 2008 Buisness Intelligence Tools, but this would need a source to work on, or perhaps i could create a local DB and create a Service which would call it take some action and load it onto another DB, but without any sort of examples, tutorials 1)i would miss a lot of steps like error handling and it would be difficult. I would be searching the net for any Tutorials/Videos but if any one knows please post the links. Or do u think doing this at this point is a waste of time and i should rather improve the theory part, i feel that if i get a practical basic atleast trial and error experience then i would understand a lot of theory concepts easily. All feedback would be greatly appreciated. Thank you.
Free Windows Admin Tool Kit Click here and download it now
August 23rd, 2009 8:07am

Hi Sudeep,Nitesh great support you guys have been, as said i am thoroughly practicing the tutorials in BOL.They are really helping to get the basics like what happens in control flow, the different containers foreach,for and how does the control flow interact with data flow, with the connection manager acting as a bridge without botehring data flow for any changes being made to the control flow.I will be going through all the excercises and they answer a lot of questions for basic level.I was wondering as to how should i face the interview, if they asked questions like how often do you create SSIS packages and give us an instance of your daily work with SSIS, should i explain them a basic scenario like in the current work place we use SSIS to contact the web service situated in a different country and after the contact is established. the package retrieves each model details in the foreach container andbuildsthe rest of the vehicle specification i.e once the model is obtained, it gets the body type, engine so on to build a complete vehicle and then once the vehicle is built it comes out of the container to get a financial quote for the user preferences of the vehicle.finally it loads those details into the main individual tables, then the control goes back to the loop i.e. foreach container to get the next model requested by a customer. so onI am just describing an overall scenario i am not sure about the length of detail that goes in there.Because that would be a very important question that will be asked, describe how any when you use SSIS in your workThey may also ask do you use SSRS to generate reports based on the data recievedNo i dont but should i give a answer saying i would not have a problem getting up to speed once i work on them on a daily basis.Even with the SSIS if i described the scenario half bakedthen it might be as if i am lying, i could add saying i was part of a team which designed the package and my part was to write underlying stored procedure to handle the package.I am intrested in getting into SSIS/SSRS but you know big companies dont have time or patience to take and put time into new people.Please advise.Thank you.
August 24th, 2009 6:23pm

The most important part IMHO is to know when to use SSIS and when not to waste precious time bending and plumming. Tell them the bad things about SSIS and wait for them to ask for the good things. Don't try to sell them things like "SSIS is the most wonderful tool....", "My day is so much..." or "I couldn' have done this without...." If you truly are a SQL SERVER Developer then tell them how to solve things instead of applying as an administrator clicking some buttons :)
Free Windows Admin Tool Kit Click here and download it now
August 24th, 2009 6:39pm

HI Robin , I just read ur message and I am in same problem as yours . I have interview lined up in few days and interviewer is going to bombard me with questions on SSIS and SSRS . Can i have ur emal id to discuss any doubts i might have . Thanks
February 16th, 2010 6:07pm

Robin I 100% agree with Michael on this one,. When I hire for BI / analyst type roles - I focus on 3 things 1) Can the candidate communicate? 2) Does the candidate have a clear approach to solving problems. 3) Do they have an apptitude or interest in the work (not necessarily experience in the exact area). But if I sense the candidate is not being 100% honest about his experience or skills, I will reject the candidate immediately. Hope this helps. Steve <abbr class="affil" />
Free Windows Admin Tool Kit Click here and download it now
February 16th, 2010 10:04pm

Hi,All you need is to understand the ETL process and already you worked as sql developer so I hope only you need an etl tools(SSIS) knowledge and read chapter 4, 5 and 6 form this book The MicrosoftData Warehouse Toolkit: With SQL Server2005 and the MicrosoftBusiness Intelligence Toolset - Joy Mundy (Author) will really helpful.see this post from same forum: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/24d6967c-a11a-488a-9811-bd50a1a98322Regards,Umasankar R
February 17th, 2010 12:16am

Here are some SSIS Interview questions and SQL Server Interview questions that contain SSIS and SQL Interview questions and SSRS Interview questions
Free Windows Admin Tool Kit Click here and download it now
July 26th, 2011 6:46am

hope this link would help you: SQL Server Reporting Services (SSRS) Interview Questions and Answers Regards, Akaas Developer
May 14th, 2012 8:01am

thanx
Free Windows Admin Tool Kit Click here and download it now
November 7th, 2012 10:06am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics