none
SSIS interview questions.

    Question

  • Could some of the expert(s) list interview questions that might come up when applying for ETL/SSIS developer I or II positions.

    Thanks
    Monday, November 27, 2006 6:42 PM

Answers

  • In re: to your original question... Do you know SSIS well yourself?  I've seen some questions on web pages for SSIS (maybe even some posted by Jamie) that focused on some specifics that maybe you can ask in a phone screen but they will not really get to the heart if the person applying is a good SSIS developer.  If you know SSIS yourself I would make up some situations and ask the person applying for the job to give an outline etc of how they would design a package to overcome the problem. 

    On the basic level you can tell right away if they understand how Control Flow works vs Data Flow etc... as you dig deeper I'd look to see if the candidate had an understanding of when its best to use script vs the built in components.  Another good item is difference between synchronous and asynchronous data flow transformations etc.  Questions like that are open enough that they give the person interviewing a chance to show what they know rather than some books online type answer.  Focus on the big picture in your evaluation of the candidate and if they have the expertise to make elegant SSIS solutions.. I don't think little questions about a particular property etc are important (you are looking for full time I assume).  For example you might ask about package configurations and how they work but not ask for step by step how to use them.  Not everyone uses every part of SSIS.  Perhaps some others here can come up with some good questions on the script components - there are a lot of things you could ask there to see if they have an idea of the classes and methods used in SSIS.

    Tuesday, November 28, 2006 1:27 AM
  • Ahhh...so you're applying for the position, and want to know what kind of questions they may ask you?

    If you get lucky they may just ask you stock questions like...

    1. What's the difference between Control Flow and Data Flow?
    2. What is the Multicast Shape used for?
    3. What shape would you use to concatenate two input fields into a single output field?
    4. etc...

    If I interviewed you I wouldn't ask questions like those above, but rather ask about your direct experience.

    Good luck!

    You may want to mark this thread as answered as well.

    Tuesday, November 28, 2006 4:58 PM

All replies

  •  sqlster wrote:
    Could some of the expert(s) list interview questions that might come up when applying for ETL/SSIS developer I or II positions.

    Thanks

    Q. Have you ever used DTS?

    If they answer yes, show them the door

     

     

    Monday, November 27, 2006 10:28 PM
    Moderator
  • Jamie,
    Why is that? Is it because they would not know vb.net or misuse variables in terms of scope or you are just kidding...
    :-)

    Thanks

    Monday, November 27, 2006 11:06 PM
  • LOL... its a joke...  There is truth that its likely harder to come from being a pro in DTS and "adjusting" to SSIS than just learning SSIS from scratch without knowledge of DTS.
    Tuesday, November 28, 2006 1:12 AM
  • In re: to your original question... Do you know SSIS well yourself?  I've seen some questions on web pages for SSIS (maybe even some posted by Jamie) that focused on some specifics that maybe you can ask in a phone screen but they will not really get to the heart if the person applying is a good SSIS developer.  If you know SSIS yourself I would make up some situations and ask the person applying for the job to give an outline etc of how they would design a package to overcome the problem. 

    On the basic level you can tell right away if they understand how Control Flow works vs Data Flow etc... as you dig deeper I'd look to see if the candidate had an understanding of when its best to use script vs the built in components.  Another good item is difference between synchronous and asynchronous data flow transformations etc.  Questions like that are open enough that they give the person interviewing a chance to show what they know rather than some books online type answer.  Focus on the big picture in your evaluation of the candidate and if they have the expertise to make elegant SSIS solutions.. I don't think little questions about a particular property etc are important (you are looking for full time I assume).  For example you might ask about package configurations and how they work but not ask for step by step how to use them.  Not everyone uses every part of SSIS.  Perhaps some others here can come up with some good questions on the script components - there are a lot of things you could ask there to see if they have an idea of the classes and methods used in SSIS.

    Tuesday, November 28, 2006 1:27 AM
  •  sqlster wrote:
    Jamie,
    Why is that? Is it because they would not know vb.net or misuse variables in terms of scope or you are just kidding...
    :-)

    Thanks

     

    I was kinda kidding - but there's many a true word spoken in jest.

     

    I tend to think that having a DTS background is a hinderance because newcomers expect SSIS to behave similarly to DTS and it is very very different. Many many posts on this forum are from people thinking with their DTS head on. Thats not their fault - but it is a problem.

    Just my opinion.

     

    Tuesday, November 28, 2006 4:37 AM
    Moderator
  •  Chris Honcoop wrote:

    In re: to your original question... Do you know SSIS well yourself?  I've seen some questions on web pages for SSIS (maybe even some posted by Jamie) that focused on some specifics that maybe you can ask in a phone screen but they will not really get to the heart if the person applying is a good SSIS developer.  If you know SSIS yourself I would make up some situations and ask the person applying for the job to give an outline etc of how they would design a package to overcome the problem. 

    On the basic level you can tell right away if they understand how Control Flow works vs Data Flow etc... as you dig deeper I'd look to see if the candidate had an understanding of when its best to use script vs the built in components.  Another good item is difference between synchronous and asynchronous data flow transformations etc.  Questions like that are open enough that they give the person interviewing a chance to show what they know rather than some books online type answer.  Focus on the big picture in your evaluation of the candidate and if they have the expertise to make elegant SSIS solutions.. I don't think little questions about a particular property etc are important (you are looking for full time I assume).  For example you might ask about package configurations and how they work but not ask for step by step how to use them.  Not everyone uses every part of SSIS.  Perhaps some others here can come up with some good questions on the script components - there are a lot of things you could ask there to see if they have an idea of the classes and methods used in SSIS.

     

    I'd agree with all of that. Focus on what parts of SSIS are important to you.

    I have done alot of interviews for SSIS and its damned hard to find people of sufficient calibre. One thing I would do if I had the time to put it together is give them a problem that I want them to solve in SSIS. Or perhaps give them an erroring package and ask them to fix it. Stick a PC in front of them and watch them work. You learn alot more from watching people work than asking them questions.

    I'm beig a bit hypocritical because I haven't actually done that yet.

     

    -Jamie

     

    Tuesday, November 28, 2006 4:41 AM
    Moderator
  •  Chris Honcoop wrote:
    LOL... its a joke...  There is truth that its likely harder to come from being a pro in DTS and "adjusting" to SSIS than just learning SSIS from scratch without knowledge of DTS.

    10-4 on that! Jamie & Chris are dead-on with this sentiment. The biggest hurdle I've encountered at my job is convincing the DBAs (only a few of whom have even looked at SSIS) that SSIS is fundamentally different (and better) than DTS. These guys are 100% T-SQL oriented, and can't get their heads around the idea of import packages combining code, .NET data objects, .NET base classes, "connections", etc. to drive an import process. Just the idea of using connections to get at data is alien to them, and they keep falling back on their tried-and-true methods of getting the job done. They try to fit every SSIS concept into the DTS framework, and it doesn't work, and they turn hostile towards SSIS very quickly.

    They don't understand that SSIS is truly a paradigm shift from a SQL-syntax orientation to an environment where VIsual Studio is used to construct a hybrid system, leveraging any number of different tools to build a package.

    Tuesday, November 28, 2006 3:37 PM
  • I find that the best way to interview someone is to ask about their project experience. Have they been successful in the real world? In this example you would want to ask them about their last SSIS project, what their role was, and have them walk you through the project from beginning to end.

    You'll find out rather quick if they can do what they say.

    Also, if you're not really familiar with SSIS then I suggest finding someone who is to do the interview for you.

    Tuesday, November 28, 2006 3:43 PM
  • Thank you all for your input. So to prepare for etl dev I or II position, here is what I plan to do.

    1. Go through the 2 ssis books on the market (wrox and kirk's book)
    2. Do as many hands on practice problems as possible.
    3. Understand as many topics as much possible from the ssis books.
    I was hoping for some sample questions that you guys would ask the candidate.

    Thanks again..
    Tuesday, November 28, 2006 4:24 PM
  • Ahhh...so you're applying for the position, and want to know what kind of questions they may ask you?

    If you get lucky they may just ask you stock questions like...

    1. What's the difference between Control Flow and Data Flow?
    2. What is the Multicast Shape used for?
    3. What shape would you use to concatenate two input fields into a single output field?
    4. etc...

    If I interviewed you I wouldn't ask questions like those above, but rather ask about your direct experience.

    Good luck!

    You may want to mark this thread as answered as well.

    Tuesday, November 28, 2006 4:58 PM
  • These are the questions that i might ask:
    1) What is the control flow
    2) what is a data flow
    3) how do you do error handling in SSIS
    4) how do you do logging in ssis
    5) how do you deploy ssis packages.
    6) how do you schedule ssis packages to run on the fly
    7) how do you run stored procedure and get data
    8) give a scenario: Want to insert a tect file into database table, but during the upload want to change a column called as months - January, Feb, etc to a code, - 1,2,3.. .This code can be read from another database table called months. After the conversion of the data , upload the file. If there are any errors, write to error table. Then for all errors, read errors from database, create a file, and mail it to the supervisor.
    How would you accomplish this task in SSIS?

    That should be sufficient to test a person.
    Also, what are variables and what is variable scope.

    Friday, July 27, 2007 1:59 AM
  •  vjammy wrote:

    These are the questions that i might ask:
    1) What is the control flow
    2) what is a data flow
    3) how do you do error handling in SSIS
    4) how do you do logging in ssis
    5) how do you deploy ssis packages.
    6) how do you schedule ssis packages to run on the fly
    7) how do you run stored procedure and get data
    8) give a scenario: Want to insert a tect file into database table, but during the upload want to change a column called as months - January, Feb, etc to a code, - 1,2,3.. .This code can be read from another database table called months. After the conversion of the data , upload the file. If there are any errors, write to error table. Then for all errors, read errors from database, create a file, and mail it to the supervisor.
    How would you accomplish this task in SSIS?

    That should be sufficient to test a person.
    Also, what are variables and what is variable scope.

     

    If they get any answer wrong for questions 1-7 then don't give them the job. Seriously. Those are SSIS fundamentals and if you want to ge a competent developer those are the MINIMUM that they need to know..

     

    Just my opinion.

     

    -Jamie

     

    Friday, July 27, 2007 2:54 AM
    Moderator
  • I think some experience that may stand out from the rest may include (A bit more advanced):

     

    Debugging Packages: Setting break points, using dataviews, running SQL profiler

    Creating custom configurations: XML and DB

    Deployment Strategies

    Custom Logging

    Running control flow and data flow in Transactions

     

    I would like to comment on the DTS statement.  I think people with experience in DTS would be good candidates ...  I am of the strong feeling that tools are just that, tools. The values that seperate out the great from the ok, are their thought processes: problem solving, aptitude fior learning new technologies, experience, and business communication skills. 

    Friday, November 16, 2007 3:09 PM
  • Hey Sean Schade

    In SSIS we dont call as shapes we will called as the Transformations

    up to my knowledge i am giving the answers

    For questions 3

    Pivot transfermation

    For question 2:

    The Multicast transformation distributes its input to one or more outputs. This transformation is similar to the Conditional Split transformation. Both transformations direct an input to multiple outputs. The difference between the two is that the Multicast transformation directs every row to every output, and the Conditional Split directs a row to a single output

    for question 1

    Control Flow:

    1. Process Oriented
    2. Doesn’t manage or pass data between components.
    3. It functions as a task coordinator
    4. In control flow tasks requires completion (Success.,failure or completion)
    5. Synchronous in nature, this means, task requires completion before moving to next task. If the tasks are not connected with each other but still they are synchronous in nature.
    6. Tasks can be executed both parallel and serially
    7. Three types of control flow elements in SSIS 2005
      1. Containers
        • Provides structures in the packages
      2. Tasks
        • Provides functionality in the packages
      3. Precedence Constraints
        • Connects containers, executables and and tasks into an ordered control flow.
        • We can control the sequence execution for tasks and also specify the conditions that tasks and containers run.
    8. It is possible to include nested containers as SSIS Architecture supports nesting of the containers. Control flow can include multiple levels of nested containers.

    Data Flow

    1. Streaming in nature
    2. Information oriented
    3. Passes data between other components
    4. Transformations work together to manage and process data. This means first set of data from the source may be in the final destination step while at the same time other set of data is still flowing. All the transformations are doing work at the same time.
    5. Three types of Data Flow components
      1. Sources
        • Extracts data from the various sources (Database, Text Files etc)
      2. Transformations
        • Cleans, modify, merge and summarizes the data
      3. Destination
        • Loads data into destinations like database, files or in memory datasets
    • Proposed as answer by Adil H Friday, June 08, 2012 7:16 AM
    Tuesday, July 01, 2008 9:42 AM
  • Hi,
    In my opinion, the working in SSIS is peace of cake for any experienced developer, and does not require any prior knowledge of SSIS. If the person knows SQL Server and TSQL and knows some VB Script and has the ability to use his mind for quick problem solving, then this should be enough. Screening for SSIS is nothing hard as compared to screening for C++ or .Net Developement (Core Programming).

    Just think, if a person do not know what transformation task is used for user define transformation of a column, All he needs is just to look that up on Google, take 5 minutes for searching and 10 minutes for implementation.

    So i think the relevant questions should have emphases on:

    1. If the person knows about Data Flow and Control Flow and what are the debugging problems/ ways we can debug a control flow/ Data Flow.

    2. The Person knows how to add/ configure a transformation. (Believe me a person who is cramming the name of transformations is not a developer at all)

    3. The Person Know Enough VB Script/ any other programming language so that he can make use of the expertise in "Derived Column Task", "Script Task". (Usage of Script Task is not required at all its available on th einternet and cramming the ways the task is behaving is of no use)

    4. The Person must be intermediate/ good in SQL Server/ TSQL, so he may utilize his expertise in developement of stored procedures and there usage in control flow / data flow. (AGAIN: IF A PERSON CRAMS the way to call a stored procedure, is just wastage of some of his grey matter, it available on Internet and can be found in one click only. ).

    Regards,
    Rashid Tanweer




    Thursday, October 09, 2008 3:32 AM
  • anyone who doesn't know any .net programming shouldn't be interviewed.

     

    here's a question that i think only experts can answer fully:  can the system.web namespace be used in ssis?

     

     

    Thursday, October 09, 2008 6:00 AM
    Moderator
  • Duane,

    I'm not a .net programmer, just a SSIS developer but I think i know the answer for your question. Yes, system.webnamespace can be used in SSIS. I think its used if the package needs to get information from webpage. Like if we need to check the exchange rate on a website, we could pass the currency as parameter, webpage link (i think we need to save the webpage / modify couple of things in xml). I think there should be materials available of how SSIS is used for webservices. I remember seeing a webcast from Microsoft.

    So is the answer correct?  :)

    Thanks
    Raj
    Friday, December 11, 2009 12:34 PM
  • anyone who doesn't know any .net programming shouldn't be interviewed.

     

    here's a question that i think only experts can answer fully:  can the system.web namespace be used in ssis?

     

     


    I would disagree with this statement.  The vast majority of ETL problems can be solved with the built in transformations and possibly some of the good custom components that are out there.  Could you give an example where you absolutely needed .net programming knowledge to solve a data integration problem that wasn't possible with 'stock' SSIS components/transformations?
    Anthony Martin | www.emc.com/mspractice
    Friday, December 11, 2009 2:28 PM
  • WHAT IS THE DIFFERENCE BETWEEN OLTP AND OLAP?

     

    WHAT IS THE DIFFERENCE BETWEEN DELETE AND TRUNCATE?

    • Delete table is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow.
    • Truncate table also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the de-allocation of the data pages of the table, which makes it faster. Of course, truncate table cannot be rolled back.
    • Truncate table is functionally identical to delete statement with no “where clause” both remove all rows in the table. But truncate table is faster and uses fewer system and transaction log resources than delete.
    • Truncate table removes all rows from a table, but the table structure and its columns, constraints, indexes etc., remains as it is.
    • In truncate table the counter used by an identity column for new rows is reset to the seed for the column.
    • If you want to retain the identity counter, use delete statement instead.
    • If you want to remove table definition and its data, use the drop table statement.
    • You cannot use truncate table on a table referenced by a foreign key constraint; instead, use delete statement without a where clause. Because truncate table is not logged, it cannot activate a trigger.
    • Truncate table may not be used on tables participating in an indexed view.

    WHAT IS NORMALIZATION? EXPLAIN FIRST THREE NORMAL FORMS?

    Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

    The Normal Forms

    The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF). In practical applications, you'll often see 1NF, 2NF, and 3NF along with the occasional 4NF. Fifth normal form is very rarely seen and won't be discussed in this article.

    First Normal Form (1NF) sets the very basic rules for an organized database:

    • Eliminate duplicative columns from the same table.
    • Create separate tables for each group of related data and identify each row with a unique column (the primary key).

    What do these rules mean when contemplating the practical design of a database? It's actually quite simple.

    The first rule dictates that we must not duplicate data within the same row of a table. Within the database community, this concept is referred to as the atomicity of a table. Tables that comply with this rule are said to be atomic. Let's explore this principle with a classic example - a table within a human resources database that stores the manager-subordinate relationship. For the purposes of our example, we'll impose the business rule that each manager may have one or more subordinates while each subordinate may have only one manager.

    Intuitively, when creating a list or spreadsheet to track this information, we might create a table with the following fields:

    • Manager
    • Subordinate1
    • Subordinate2
    • Subordinate3
    • Subordinate4

    However, recall the first rule imposed by 1NF: eliminate duplicative columns from the same table. Clearly, the Subordinate1-Subordinate4 columns are duplicative. Take a moment and ponder the problems raised by this scenario. If a manager only has one subordinate - the Subordinate2-Subordinate4 columns are simply wasted storage space (a precious database commodity). Furthermore, imagine the case where a manager already has 4 subordinates - what happens if she takes on another employee? The whole table structure would require modification.

    At this point, a second bright idea usually occurs to database novices: We don't want to have more than one column and we want to allow for a flexible amount of data storage. Let's try something like this:

    • Manager
    • Subordinates

    Where the Subordinates field contains multiple entries in the form "Mary, Bill, Joe"

    This solution is closer, but it also falls short of the mark. The subordinates column is still duplicative and non-atomic. What happens when we need to add or remove a subordinate? We need to read and write the entire contents of the table. That's not a big deal in this situation, but what if one manager had one hundred employees? Also, it complicates the process of selecting data from the database in future queries.

    Here's a table that satisfies the first rule of 1NF:

    • Manager
    • Subordinate

    In this case, each subordinate has a single entry, but managers may have multiple entries.

    Now, what about the second rule: identify each row with a unique column or set of columns (the primary key)? You might take a look at the table above and suggest the use of the subordinate column as a primary key. In fact, the subordinate column is a good candidate for a primary key due to the fact that our business rules specified that each subordinate may have only one manager. However, the data that we've chosen to store in our table makes this a less than ideal solution. What happens if we hire another employee named Jim? How do we store his manager-subordinate relationship in the database?

    It's best to use a truly unique identifier (such as an employee ID) as a primary key. Our final table would look like this:

    • Manager ID
    • Subordinate ID

    2<sup>ND</sup> Normal Form

    Over the past month, we've looked at several aspects of normalizing a database table. First, we discussed the basic principles of database normalization. Last time, we explored the basic requirements laid down by the first normal form (1NF). Now, let's continue our journey and cover the principles of second normal form (2NF).

    Recall the general requirements of 2NF:

    • Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
    • Create relationships between these new tables and their predecessors through the use of foreign keys.

    These rules can be summarized in a simple statement: 2NF attempts to reduce the amount of redundant data in a table by extracting it, placing it in new table(s) and creating relationships between those tables.

    Let's look at an example. Imagine an online store that maintains customer information in a database. They might have a single table called Customers with the following elements:

    • CustNum
    • FirstName
    • LastName
    • Address
    • City
    • State
    • ZIP

    A brief look at this table reveals a small amount of redundant data. We're storing the "Sea Cliff, NY 11579" and "Miami, FL 33157" entries twice each. Now, that might not seem like too much added storage in our simple example, but imagine the wasted space if we had thousands of rows in our table. Additionally, if the ZIP code for Sea Cliff were to change, we'd need to make that change in many places throughout the database.

    In a 2NF-compliant database structure, this redundant information is extracted and stored in a separate table. Our new table (let's call it ZIPs) might have the following fields:

    • ZIP
    • City
    • State

    If we want to be super-efficient, we can even fill this table in advance -- the post office provides a directory of all valid ZIP codes and their city/state relationships. Surely, you've encountered a situation where this type of database was utilized. Someone taking an order might have asked you for your ZIP code first and then knew the city and state you were calling from. This type of arrangement reduces operator error and increases efficiency.

    Now that we've removed the duplicative data from the Customers table, we've satisfied the first rule of second normal form. We still need to use a foreign key to tie the two tables together. We'll use the ZIP code (the primary key from the ZIPs table) to create that relationship. Here's our new Customers table:

    • CustNum
    • FirstName
    • LastName
    • Address
    • ZIP

    We've now minimized the amount of redundant information stored within the database and our structure is in second normal form!

    3<sup>RD</sup> Normal Form

    There are two basic requirements for a database to be in third normal form:

    • Already meet the requirements of both 1NF and 2NF
    • Remove columns that are not fully dependent upon the primary key.

    Imagine that we have a table of widget orders that contains the following attributes:

    • Order Number
    • Customer Number
    • Unit Price
    • Quantity
    • Total

    Remember, our first requirement is that the table must satisfy the requirements of 1NF and 2NF. Are there any duplicative columns? No. Do we have a primary key? Yes, the order number. Therefore, we satisfy the requirements of 1NF. Are there any subsets of data that apply to multiple rows? No, so we also satisfy the requirements of 2NF.

    Now, are all of the columns fully dependent upon the primary key? The customer number varies with the order number and it doesn't appear to depend upon any of the other fields. What about the unit price? This field could be dependent upon the customer number in a situation where we charged each customer a set price. However, looking at the data above, it appears we sometimes charge the same customer different prices. Therefore, the unit price is fully dependent upon the order number. The quantity of items also varies from order to order, so we're OK there.

    What about the total? It looks like we might be in trouble here. The total can be derived by multiplying the unit price by the quantity, therefore it's not fully dependent upon the primary key. We must remove it from the table to comply with the third normal form. Perhaps we use the following attributes:

    • Order Number
    • Customer Number
    • Unit Price
    • Quantity

    Now our table is in 3NF. But, you might ask, what about the total? This is a derived field and it's best not to store it in the database at all. We can simply compute it "on the fly" when performing database queries. For example, we might have previously used this query to retrieve order numbers and totals:

    SELECT OrderNumber, Total
    FROM WidgetOrders

    We can now use the following query:

    SELECT OrderNumber, UnitPrice * Quantity AS Total
    FROM WidgetOrders

    to achieve the same results without violating normalization rules.

    Before we begin our discussion of the normal forms, it's important to point out that they are guidelines and guidelines only. Occasionally, it becomes necessary to stray from them to meet practical business requirements. However, when variations take place, it's extremely important to evaluate any possible ramifications they could have on your system and account for possible inconsistencies

    DIFFERENCE BETWEEN CLUSTERED AND NON-CLUSTERED INDEXES?

    There are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
    A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf nodes of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
    Consider using a clustered index for:
    o Columns that contain a large number of distinct values.
    o Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=.
    o Columns that are accessed sequentially.
    o Queries that return large result sets.
    Non-clustered indexes have the same B-tree structure as clustered indexes, with two significant differences:
    o The data rows are not sorted and stored in order based on their non-clustered keys.
    o The leaf layer of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Each index row contains the non-clustered key value and one or more row locators that point to the data row (or rows if the index is not unique) having the key value.
    o Per table only 249 non clustered indexes.

     

    WHAT'S THE DIFFERENCE BETWEEN CONTROL FLOW AND DATA FLOW?

    Control Flow:

    1.       Process Oriented

    2.       Doesn’t manage or pass data between components.

    3.       It functions as a task coordinator

    4.       In control flow tasks requires completion (Success, failure or completion)

    5.       Synchronous in nature, this means, task requires completion before moving to next task. If the tasks are not connected with each other but still they are synchronous in nature.

    6.       Tasks can be executed both parallel and serially

    7.       Three types of control flow elements in SSIS 2005

    ·         Containers: Provides structures in the packages

    ·         Tasks: Provides functionality in the packages

    ·         Precedence Constraints: Connects containers, executables and tasks into an ordered control flow.

    8.       We can control the sequence execution for tasks and also specify the conditions that tasks and containers run.

    9.       It is possible to include nested containers as SSIS Architecture supports nesting of the containers. Control flow can include multiple levels of nested containers.

    Data Flow

    •  
      1. Streaming in nature
      2. Information oriented
      3. Passes data between other components
      4. Transformations work together to manage and process data. This means first set of data from the source may be in the final destination step while at the same time other set of data is still flowing. All the transformations are doing work at the same time.
      5. Three types of Data Flow components

    ·         Sources: Extracts data from the various sources (Database, Text Files etc)

    ·         Transformations: Cleans, modify, merge and summarizes the data

    ·         Destination: Loads data into destinations like database, files or in memory datasets

    WHAT IS THE MULTICAST SHAPE USED FOR?

     

    The Multicast transformation distributes its input to one or more outputs. This transformation is similar to the Conditional Split transformation. Both transformations direct an input to multiple outputs. The difference between the two is that the Multicast transformation directs every row to every output, and the Conditional Split directs a row to a single output

    WHAT SHAPE WOULD YOU USE TO CONCATENATE TWO INPUT FIELDS INTO A SINGLE OUTPUT FIELD?

                    Derived Column shape\Task can be used to concatenate columns

    Link
    http://msdn.microsoft.com/en-us/library/ms141703.aspx

    Thursday, February 04, 2010 12:55 PM
  • When I interview SSIS and SQL Developers, I setup a project before hand with all of the fundamental aspects that I think they should have already on the topic.  After my normal questions and if they still have my attention, I show them the project.  We walk through the requirement (typically very simple task) and then walk through each step I've setup.

    1) I do not add any error handling or logging and I ask then to work with me to do so.
    2) I ask how they would have changed it.  I purposely use methods that are mid-level performing
    3) I ask really basic things that help everyone in the team work better such as, what would you name this task or this component to it is meaningful at first glance
    4) I ask them to run it.  Yes, if they can't run it and show me the results from all sides it will weed them out
    5) I start up a basic conversation with them about the project we just worked on while trying to go deeper into their knowledge of how it affects the complete landscape and each object that is part in it.

    My interviews are typically long.  I tell them to plan for awhile because I need to a. see if they can work with people and b. make sure they didn't just read the brochure on msdn of features and abilities of SSIS beforehand.  If they are still there ;-) I may strike up conversations about threading and buffers to see if they really took the initiative in their career to find out how things really work behind the scenes.  Of course depending on the version I expect them to work on, I ask direct version related questions.  That is more relevant with 2008 and some major changes

    I'm adding another question to my normal process though, "Q. Have you ever used DTS?"

    LOL that was too funny Jamie!

    I have to respectfully disagree with, "anyone who doesn't know any .net programming shouldn't be interviewed".  Yes, script tasks and the ability to develop in .net is a major aspect to a high level SSIS developer but it is not a requirement.  Find out if the person can build that from the team you have more than disqualify them simply because they use SSIS native tools to get the job done over long winded script tasks.


    Ted Krueger Blog on lessthandot.com @onpnt on twitter
    Thursday, February 04, 2010 1:17 PM
    Moderator
  • WHAT IS THE DIFFERENCE BETWEEN DELETE AND TRUNCATE?

    • Truncate table also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the de-allocation of the data pages of the table, which makes it faster. Of course, truncate table cannot be rolled back.


    Sigh...  TRUNCATES *may* be able to be rolled back.  It depends on the situation, but you could do this:

    BEGIN TRAN
    TRUNCATE TABLE YourTable
    ROLLBACK TRAN

    ... and have all of your data back.
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Thursday, February 04, 2010 3:11 PM
    Moderator
  • Truncate  deletes the records permanently  from the table , but table structure is available and we cannot Rollback. bec It is permanently deleted from log file.

    Where as Delete command deletes the records permanently from the table , and we can Roll back.

    Tuesday, June 08, 2010 1:14 PM
  • Please research your rebuttals before posting that someone (particularly a moderator) is wrong.  See Paul Randal's DBA Myth a Day: Truncate Table is Non-Logged and corresponding When are pages from a truncated table reused? articles.
    Please mark answered posts. Thanks for your time.
    Tuesday, June 08, 2010 1:22 PM
    Moderator
  • TRUNCATE can be roll back the data.

    For this try this below senario 

    BEGIN TRAN
    TRUNCATE TABLE YourTable
    ROLLBACK

    Truncate will not take cost for delete data,it need time only for scan

    DELETE will take time to delete and scan data

    So, truncate is less cost and best performance 

    Sunday, September 12, 2010 8:54 AM
  • Is there any way, to delete with out log?

    Satyananda reddy


    Satyanand
    Tuesday, October 26, 2010 11:15 AM
  • Yes, use AUTO COMMIT TRANSACTIONS to delete data by TRUNCATE command.

    The above senario is EXPLICIT TRANSACTION.

    Try this,

    make a IDENTITY column for TABLE 1 and make copy of same table into TABLE 2

    do DELETE data by using DELETE in TABLE 1

    do DELETE data by using TRUNCATE in TABLE 2

    and DO INSERT a new row into both tables

    and check the identity column values.

     

     


    SATEESH REDDY KATTA
    Thursday, November 04, 2010 12:40 PM
  • I don't work well when people watching over me. 

    Sincerely Thank You !
    Thursday, November 18, 2010 10:19 PM
  • In Truncate case identity value is set to starting position, in case of delete it is not.
    Tuesday, June 28, 2011 12:41 PM
  • what if you have requirement to convert existing DTS package to SSIS. Won't that experience be handy :)

    Thursday, March 15, 2012 9:15 AM
  • Good one Chris !

    Have a question for script ..

    1. how to process data on some url in xml format using SSIS?

    2. How to handle upload and downloads from SFTP?

    3. How to manage excel files with numeric data which may be required for further calculations( as data is not stored in numeric value by default .. it needs seed value)

    Thursday, March 15, 2012 9:29 AM
  • One more problem with SSIS developer of DBA background

    They want to SSIS for uploading data and alerts while all other things like transformations is done on database side.

    If they ever lead a team, you know the outcome(both project and team).

    Thursday, March 15, 2012 9:34 AM
  • @Anthony : How to manage SFTP without script?
    Thursday, March 15, 2012 9:42 AM
  • @Raj : for processing xml you need System.Xml but  why System.Web ??
    Thursday, March 15, 2012 9:49 AM
  • Thanks Phil, this is something new that i heard ..

    have even tested it in SSIS using sequence container with transaction option : required

    had SQL Execute task inside with a truncate command for  a table and had forced execution result to failure

    and it really rolled back the data :)


    • Edited by rambo_s Thursday, March 15, 2012 10:14 AM
    Thursday, March 15, 2012 10:14 AM
  • I am sure you are joking right? 

    DTS existed before SSIS.

    Wednesday, July 11, 2012 9:09 PM