in

SQLServerCentral.com

The largest free SQL Server community.

Devin Knight

  • Answers To SSIS Interview Questions

    This is just a starting point for you to prepare for your interview so make sure not to limit yourself to what you find here. These answers could easily vary but generally someone who is interviewing is looking not only for a correct answer but confidence in your answer. If you have any questions about my answers or have something to add feel free to do so, but I did try to keep the answers short and sweet.  

    SSIS Interview Questions 

    1. What does a control flow do?

    A control flow manages the workflow of the package. Pretty self-explanatory it controls the flow of how the package is run. Sorry to use the words control flow in the definition, but this is a real easy one.

    2. Generically explain what happens inside a data flow task? 

    It moves data from nearly any source, performs different transforms to the data, then sends to nearly any destination.

    3. Explain what ETL is?

    Extract, Transform, and Load. Basically pulls the data from a location, change/transforms the data somehow, and then loads it in a new location.

    4. Which task would you use to copy, move or delete files?

    File System Task (This is just an example know all control flow tasks)

    5. Which transform would you use to split your data based on conditions you define?

    Conditional Split (This is just an example know all data flow transforms)

    6. Explain the pros and cons of deploying to a file system vs msdb?

    Functionality               Best in File System                Best in MSDB
    Security                                                                                X
    Backup and Recovery                 X
    Deployment                                X
    Troubleshooting                         X
    Execution Speed                         X                                       X
    Availability                                  X

    7. If you did not know the answer to a question what would be your next step to find the answer?

    Search online, read a book, or ask for help. I generally would suggest it in this order.

    Posted May 19 2008, 04:00 PM by Devin Knight with no comments

  • Answers To DBA Interview Questions

    Here are your answers to the questions from last week You should get some variation of what I have given you here. If you think I've made a mistake somewhere let me know and we can discuss it. This will be a short series of blogs on interview questions to help you learn what to expect when entering your first interviews. 

    Activities for Laptop

     1.      Using the AdventureWorks database and bring back all the columns from the HumanResources.Employee table where the title starts with Production, marital status is M and the hire date is between 1/1/2000 and getdate().  Hint:  Should get 12 rows back.

    SELECT *
    FROM HumanResources.Employee
    WHERE Maritalstatus = 'M' and hiredate between '1/1/2000' and getdate() and title like 'production%'

    2.      From the AdventureWorks database join the tables Production.Product and Production.TransactionHistory.  Bring back columns ProductID, Quantity, and ActualCost from Production.TransactionHistory.  Use Name and ProductNumber from the table Production.Product.  Also, add a where clause that only returns the rows having an actual cost greater than zero.

    SELECT h.productid, h.quantity, h.actualcost, p.name, p.productnumber
    FROM Production.Product AS p INNER JOIN Production.TransactionHistory AS h
    ON p.productid = h.productid
    WHERE actualcost > 0

    3.      Using the AdventureWorksDW database show a join between Dim_Customer and Dim_Geography that brings back the columns LastName and FirstName aggregated separated by a comma, AddressLine1, and AddressLine2 from Dim_Customer.  From Dim_Geography bring back the City, StateProvinceCode, and PostalCode columns.  Then order by LastName.

    SELECT c.lastname + ', ' + c.firstname AS fullname, c.addressline1, c.addressline2, g.city, g.stateprovincecode, g.postalcode
    FROM dbo.DimCustomer AS c INNER JOIN dbo.DimGeography AS g
    ON c.geographykey = g.geographykey
    ORDER BY lastname

    4.      This time using the same SQL statement from the last question replace the AddressLine1 and AddressLine2 columns with a case statement. The case statement should return only AddressLine1 if AddressLine2 is null and AddressLine1 and AddressLine2 separate by a comma if AddressLine2 does exists

    SELECT c.lastname + ', ' + c.firstname AS Fullname, g.city, g.stateprovincecode, g.postalcode,
    CASE
    when c.addressline2 is null then c.addressline1
    else c.addressline1 + ', ' + c.addressline2
    end AS Address
    FROM dbo.DimCustomer AS c INNER JOIN dbo.DimGeography AS g
    ON c.geographykey = g.geographykey
    where c.addressline2 is not null
    ORDER BY lastname

    5.      Using a left join on FactResellerSales and Dim_Employee from the same database show EmployeeKey, LastName, and FirstName from Dim_Employee.   From FactResellerSales show the SalesAmount column summed and then group by Dim_Employee columns.  Also, use having statement to bring back only summed sales amount that have a total greater than 1 million.  Lastly order by the summed sales amount in descending order.  Hint:  Should be only 15 rows.

    SELECT e.employeekey, e.lastname, e.firstname, sum(s.salesamount) AS totalsales
    FROM dbo.FactResellerSales AS s LEFT JOIN dbo.DimEmployee AS e
    ON s.employeekey = e.employeekey
    GROUP BY e.employeekey, e.lastname, e.firstname
    HAVING sum(s.salesamount) > 1000000
    ORDER BY sum(s.salesamount) desc

    Posted May 19 2008, 02:51 PM by Devin Knight with no comments

  • SSIS Interview Questions

    I have decided to make a short series of blogs about possible interview questions to help you prepare for an interview. Last time I wrote about basic DBA questions. Today I have given you some basic SSIS question to study. These basic SSIS questions will give you a starting point on how to prepare for an interview. If you have any other suggestions feel free to add to this list but keep in mind these questions are probably for a mid to junior level BI consultant. I would assume if you're a senior BI developer you know what to expect.

     

    SSIS Interview Questions 

    1. What does a control flow do?

    2. Generically explain what happens inside a data flow task? 

    3. Explain what ETL is?

    4. Which task would you use to copy, move or delete files?

    5. Which transform would you use to split your data based on conditions you define?

    6. Explain the pros and cons of deploying to a file system vs msdb?

    7. If you did not know the answer to a question what would be your next step to find the answer?

    Posted May 19 2008, 02:08 PM by Devin Knight with no comments
    Filed under:

  • Possible interview questions for Intern/Junior DBA or Developer

    I wrote these questions thinking about what some basic t-sql statements that I would want to see if I were to interview a intern or junior level DBA.  These cover some basics using both the adventure works transactional database and the adventure works data warehouse.  I'm interested in seeing how others test people they interview.  Let me know what suggestions you have for me to add to my questions. 

     

    Activities for Laptop

     1.      Using the AdventureWorks database and bring back all the columns from the HumanResources.Employee table where the title starts with Production, marital status is M and the hire date is between 1/1/2000 and getdate().  Hint:  Should get 12 rows back.

    2.      From the AdventureWorks database join the tables Production.Product and Production.TransactionHistory.  Bring back columns ProductID, Quantity, and ActualCost from Production.TransactionHistory.  Use Name and ProductNumber from the table Production.Product.  Also, add a where clause that only returns the rows having an actual cost greater than zero.

    3.      Using the AdventureWorksDW database show a join between Dim_Customer and Dim_Geography that brings back the columns LastName and FirstName aggregated separated by a comma, AddressLine1, and AddressLine2 from Dim_Customer.  From Dim_Geography bring back the City, StateProvinceCode, and PostalCode columns.  Then order by LastName.

    4.      This time using the same SQL statement from the last question replace the AddressLine1 and AddressLine2 columns with a case statement. The case statement should return only AddressLine1 if AddressLine2 is null and AddressLine1 and AddressLine2 separate by a comma if AddressLine2 does exists

    5.      Using a left join on FactResellerSales and Dim_Employee from the same database show EmployeeKey, LastName, and FirstName from Dim_Employee.   From FactResellerSales show the SalesAmount column summed and then group by Dim_Employee columns.  Also, use having statement to bring back only summed sales amount that have a total greater than 1 million.  Lastly order by the summed sales amount in descending order.  Hint:  Should be only 15 rows.

     

    Posted May 10 2008, 07:22 PM by Devin Knight with no comments
    Filed under:

  • SQL Saturday

    So if you missed out on SQL Saturday you missed a great event and I highly suggest making it to the next event in Orlando June 7th and 8th. Check www.sqlsaturday.com for registration and schedules. Hope to see you in Orlando!

    This weekend brought hundreds of people from all over the country to Jacksonville to network and improve their skills. Both my sessions went well and if you attended my Intro to SSIS session and would like the few PowerPoint slides email me at knight_devin@homail.com and I would be happy to supply you with them. I had many great questions in both sessions but if you have any further questions let me know and I’ll be happy to help.

    Posted May 05 2008, 08:58 AM by Devin Knight with no comments
    Filed under:

  • SQL Saturday - Jacksonville

    I am fairly new to the IT profession, so I think my blog will be great for others trying to get their break.  I have always used blogs as a great source to find answers to my numerous questions.  So, I hope my blog can be used similarly.  My theory is if you write about what you have learned then you are twice as likely to remember it.  So here it goes let’s prove myself right. 

     

    Here’s where you can find me:

     

    I am a member of the Jacksonville SQL Server User Group although unfortunately I travel during the week so miss the Wednesday meetings.  I will be speaking at SQL Saturday in two sessions on May 3 at the University of North Florida in Jacksonville.  The first session is more of a discussion and will be about what steps to take to get your break in the IT industry.  The second is an introduction to SSIS where I will show how to create a basic package.  Expect this session to fill up quickly because it is an intro session. 

     

    Posted Apr 22 2008, 04:53 PM by Devin Knight with no comments
    Filed under:
Copyright Red Gate Software
Powered by Community Server (Commercial Edition), by Telligent Systems