none
Interview process and questions for SSIS,SSRS

    Question

  • 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.
    Saturday, August 22, 2009 2:39 PM

Answers

  • SSIS is entirely different from DTS, so you can go ahead and start working directly with SSIS. (I directly started with SSIS).
    You can search for some videos/webcasts on SSIS. They will give you good idea of developing SSIS packages.
    One such link is
    http://www.jumpstarttv.com/channel.aspx?cat=c871236d-8554-42e3-8683-4d422356c0bd
     
    I know, you dont have much time to cretae and design the packages so it will be nice if you go through concepts:
    Control Flow,DataFlow,Logging,Error Handling,Package Tuning,Package Deployment,Package Scheduling


    Nitesh Rai- Please mark the post as answered if it answers your question
    • Marked as answer by robin_blue Wednesday, August 26, 2009 7:16 PM
    Sunday, August 23, 2009 12:26 PM
  • Hey Rob,
    All I would advise u in such a short time is to create  a simple SSIS package.

    Have a SQL Task in the control flow.
    One script task.
    Have a Data flow task in the control flow which gets the data from a flat file source & dumps the data to OLEDB Location.

    Once this is done, Play a bit around Configurations & Logging.

    That will provide u with the confidence u need before the interview.

    For configuration check the link:
    http://msdn.microsoft.com/en-us/library/cc895212.aspx

    For Other SSIS Control Flow & Data Flow elements check:
    http://msdn.microsoft.com/hi-in/library/ms137681%28en-us,SQL.90%29.aspx
    http://msdn.microsoft.com/hi-in/library/ms137681%28en-us,SQL.90%29.aspx


    On these links check the left hand side tree it has all the documentation required for a starter.

    Go thorugh what ever you can and DO BUILD A BASIC PACKAGE AS I SAID.


    All the Best once again.

    Hope this helps !! Please close the threads once answered - Sudeep
    • Marked as answer by robin_blue Wednesday, August 26, 2009 7:15 PM
    Sunday, August 23, 2009 6:38 PM
  • Actually, it's pretty simple.  Just take a deep breath and relax.  Trying to "cram for an exam" while being nervous and stressed out isn't going to get you very far.  Concentrate on your experience.  Being able to take a task, break it down into steps, and then building a solution is a skill that you acquire through experience.  Anyone can look up syntax in Books Online or come out to a forum and ask specific questions.  The tools and code you are writing today aren't going to be the same 2 years from now, but the process of breaking down a problem into chunks that can be built is the same today as it was 2000 years ago as it will be a million years from now.  If you don't know the answer to a question, don't be afraid to tell the interviewer that you don't know, but that you do know where to go to find the answer to the question.  I've been interviewing for database and IT positions for decades.  I've never used written exams and I've never asked syntax questions.  I always ask open ended questions and then leave it up to the candidate to talk themselves into or out of the position I'm interviewing for.  I'm listening as much to what is said as how it is said.  I'm trying to find someone who knows what they are doing, will constantly learn, will fit in with the team, and isn't afraid to admit when they don't know something.

    Several years ago, I was interviewing for a DBA position at a bank that I was working with.  The interview that I had with one lady was either 2 or 3 questions and lasted all of about 5 - 10 minutes.  We hired her as the DBA team lead.  She asked me about that on her first day, because she thought that she had completely bombed the interview because I didn't have very many questions and the interview didn't last very long.  I told her that the interview didn't last very long simply because I didn't feel it was necessary to ask any more questions.  The answers that she had to the first couple of questions were so extensive and immediately recognized that I had left out a lot of really important information in the question that I asked that it told me she wasn't trying to BS through an interview and had all the experience that we needed.  It told me she knew how to take a large, complex problem and break it down into manageable pieces that could be implemented.  She's still the lead DBA there and I can guarantee that she'll tell you that she still spends a lot of time looking up syntax, asking questions, and reading through Books Online along with other sources.  But when it comes to doing the job of a lead DBA, she was one of the most perfect fits I've ever had the pleasure of interviewing and working with.

    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    • Marked as answer by robin_blue Wednesday, August 26, 2009 7:15 PM
    Tuesday, August 25, 2009 12:46 AM
    Moderator

All replies

  • 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 tunning
    Go through following link
    http://www.sql-server-performance.com/articles/dba/package_configuration_2005_p1.aspx

    You can find various other links also using "every ones friend" Google.
    Nitesh Rai- Please mark the post as answered if it answers your question
    Saturday, August 22, 2009 4:33 PM
  • 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
    Saturday, August 22, 2009 5:36 PM
  • 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.
    Sunday, August 23, 2009 12:07 PM
  • SSIS is entirely different from DTS, so you can go ahead and start working directly with SSIS. (I directly started with SSIS).
    You can search for some videos/webcasts on SSIS. They will give you good idea of developing SSIS packages.
    One such link is
    http://www.jumpstarttv.com/channel.aspx?cat=c871236d-8554-42e3-8683-4d422356c0bd
     
    I know, you dont have much time to cretae and design the packages so it will be nice if you go through concepts:
    Control Flow,DataFlow,Logging,Error Handling,Package Tuning,Package Deployment,Package Scheduling


    Nitesh Rai- Please mark the post as answered if it answers your question
    • Marked as answer by robin_blue Wednesday, August 26, 2009 7:16 PM
    Sunday, August 23, 2009 12:26 PM
  • Hey Rob,
    All I would advise u in such a short time is to create  a simple SSIS package.

    Have a SQL Task in the control flow.
    One script task.
    Have a Data flow task in the control flow which gets the data from a flat file source & dumps the data to OLEDB Location.

    Once this is done, Play a bit around Configurations & Logging.

    That will provide u with the confidence u need before the interview.

    For configuration check the link:
    http://msdn.microsoft.com/en-us/library/cc895212.aspx

    For Other SSIS Control Flow & Data Flow elements check:
    http://msdn.microsoft.com/hi-in/library/ms137681%28en-us,SQL.90%29.aspx
    http://msdn.microsoft.com/hi-in/library/ms137681%28en-us,SQL.90%29.aspx


    On these links check the left hand side tree it has all the documentation required for a starter.

    Go thorugh what ever you can and DO BUILD A BASIC PACKAGE AS I SAID.


    All the Best once again.

    Hope this helps !! Please close the threads once answered - Sudeep
    • Marked as answer by robin_blue Wednesday, August 26, 2009 7:15 PM
    Sunday, August 23, 2009 6:38 PM
  • 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 and builds the 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 on

    I 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 work

    They may also ask do you use SSRS to generate reports based on the data recieved

    No 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 baked then 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.
    Monday, August 24, 2009 10:23 PM
  • 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 :)


    Monday, August 24, 2009 10:39 PM
  • Actually, it's pretty simple.  Just take a deep breath and relax.  Trying to "cram for an exam" while being nervous and stressed out isn't going to get you very far.  Concentrate on your experience.  Being able to take a task, break it down into steps, and then building a solution is a skill that you acquire through experience.  Anyone can look up syntax in Books Online or come out to a forum and ask specific questions.  The tools and code you are writing today aren't going to be the same 2 years from now, but the process of breaking down a problem into chunks that can be built is the same today as it was 2000 years ago as it will be a million years from now.  If you don't know the answer to a question, don't be afraid to tell the interviewer that you don't know, but that you do know where to go to find the answer to the question.  I've been interviewing for database and IT positions for decades.  I've never used written exams and I've never asked syntax questions.  I always ask open ended questions and then leave it up to the candidate to talk themselves into or out of the position I'm interviewing for.  I'm listening as much to what is said as how it is said.  I'm trying to find someone who knows what they are doing, will constantly learn, will fit in with the team, and isn't afraid to admit when they don't know something.

    Several years ago, I was interviewing for a DBA position at a bank that I was working with.  The interview that I had with one lady was either 2 or 3 questions and lasted all of about 5 - 10 minutes.  We hired her as the DBA team lead.  She asked me about that on her first day, because she thought that she had completely bombed the interview because I didn't have very many questions and the interview didn't last very long.  I told her that the interview didn't last very long simply because I didn't feel it was necessary to ask any more questions.  The answers that she had to the first couple of questions were so extensive and immediately recognized that I had left out a lot of really important information in the question that I asked that it told me she wasn't trying to BS through an interview and had all the experience that we needed.  It told me she knew how to take a large, complex problem and break it down into manageable pieces that could be implemented.  She's still the lead DBA there and I can guarantee that she'll tell you that she still spends a lot of time looking up syntax, asking questions, and reading through Books Online along with other sources.  But when it comes to doing the job of a lead DBA, she was one of the most perfect fits I've ever had the pleasure of interviewing and working with.

    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    • Marked as answer by robin_blue Wednesday, August 26, 2009 7:15 PM
    Tuesday, August 25, 2009 12:46 AM
    Moderator
  • 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

    Tuesday, February 16, 2010 11:07 PM
  • 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" />
    Wednesday, February 17, 2010 3:04 AM
  • 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-bd50a1a98322


    Regards,
    Umasankar R
    Wednesday, February 17, 2010 5:16 AM
  • hope this link would help you:
    SQL Server Reporting Services (SSRS) Interview Questions and Answers

    Regards,
    Akaas Developer

    Monday, May 14, 2012 12:01 PM
  • thanx

    Wednesday, November 07, 2012 3:06 PM