Double-Hop

After really spending two hours of time learned that impersonation will work only within same server and will not work across the servers.

Here is the guy neatly explained the scenario I went thru.

Virtualization Jump Start

Virtualization is the future.  The complete Azure platform is based on Virtualization.  Already Microsoft is making impact on this space.

Here is the Jump Start guide for Microsoft Virtualization

http://technet.microsoft.com/en-us/edge/Video/hh124559

SSIS Interview Questions

Entry Level Questions

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? How do you do Custom Logging?
5. How do you deploy SSIS packages? And explain different deployment strategy?
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 text 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.
9. What are variables and what is variable scope?
10. How do you debug a Packages
11. Explain Package configuration and how do you create custom configurations: XML and DB
12. How do you control flow and data flow in Transactions
13. Explain Multicast transformation –
14. Identify True or False - Using a checkpoint file in SSIS is just like issuing the CHECKPOINT
    command against the relational engine. It commits all of the data to the database.
15. How do you do set of operations in a Transaction?
16. Can you explain the Import\Export tool wizard?
17. What are the command line tools to execute SQL Server Integration Services
    packages?
18. Can you name some of the core SSIS components in the Business Intelligence
    Development Studio you work with on a regular basis when building an SSIS package?

For experienced candidate

1. True or False: SSIS has a default means to log all records updated, deleted or inserted on a per table basis.
2. What is a breakpoint in SSIS? How is it setup? How do you disable it?
3. Can you name 5 or more of the native SSIS connection managers?
4. How do you eliminate quotes from being uploaded from a flat file to SQL Server?
5. Can you name 5 or more of the main SSIS tool box widgets and their functionality?
6. Explain the pros and cons of deploying to a file system vs MSDB?
7. Explain architecture of SSIS?
8. Difference between Control Flow and Data Flow?
9. How to pass property value at Run time? How do you implement Package Configuration?
10. What are new features in SSIS 2008?
11. How would you pass a variable value to Child Package?
12. What is Execution Tree?
13. What are the points to keep in mind for performance improvement of the package?
14. How would you configure a data flow task so that it can transfer data to different table based on the
    city name in a source table column?
15. Difference between Union All and Merge Join?
16. Explain Lookup, fuzzy lookup, fuzzy grouping transformation?
17. How would you restart package from previous failure point? What are Checkpoints and how can we implement in SSIS?
18. Difference between asynchronous and synchronous transformations?
19. Asynchronous transformation have different Input and Output buffers and it is up to the component designer
    in an Async component to provide a column structure to the output buffer and hook up the data from the
    input.
20. How to achieve multiple threading in SSIS?
21. What is the default string data type of Excel source?
22. How to copy and rename a file to a dynamic location using SSIS?

SSIS Script Component

In SQL Server Integration services, we have Script Component, which can be used in Data Flow Task as Source, Destination or Transformation.  This one task needs little more understanding than the other components we have in SSIS.

1. How you want to use the Script Component?

Script Component as Source

If you selected Source, configure the Column of the source in the Script Component Editor.  You can have one more by using OUT OUTPUT buffer.  For each output buffer add COLUMN and configure the data types of columns.  Click on EDIT SCRIPT to provide details.

    public override void PreExecute()
    {
        base.PreExecute();
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }

    public override void CreateNewOutputRows()
    {
        for (int i = 1; i <= 100; i++)
        {
            Output0Buffer.AddRow();
            Output0Buffer.RollNum = i;
            Output0Buffer.Name = "Ram " + i.ToString();
        }
    }

In the generated code (main.cs) you will see three place holder methods. 

• PreExecute – Execute once at the starting of the component.  Useful to set variable values.



• PostExecute – Executes once at the end of component execution.



• CreateNewOutputRows – This is the method you can generate the new rows.  In the above code I am using a loop to generate 100 rows of dummy data.

Based on the name you provided in in “Input and Output Column” setting of Script Component Editor.  The name is chosen.  In the above example it was Output0Buffer, which is default.

Script Component as Transformation

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        Row.CustomerName = Row.CustomerName + " - " + Row.CustomerCode;
    }

You can mark the columns ReadWrite and edit the value using the ProcessInputRow method placeholder.  You can set ExclusionGroup property to non-zero value to create more OUTPUT buffers.

Script Component as Destination

It is very similar to SC as Transformation, except that you can not have more output buffers as it is irrelevant.  You can process the data row by row using ProcessInputRow method.

Refer Script task as Source