Interview Questions faced in Accenture

1. tell me briefly about ur current project

2. how do u get data..  in what source type

3. what is a text qualifier

4. how to eliminate unwanted text from a text file source

5. inorder to speedup the loading of millions of records from source file what should i do.?

6. difference between OLEDB destination and SQL Server destination.
ans : OLEDB destination loads the records in batches, where as SQL Server destination loads all the records at one go..

7. what is delay validation.

8. how do u use logging in ur projects

9. how many types of loggings are there

10. how can u send a particular log detail to some one.. how can u do that.

11. there are millions of records uploading into server. if any problem occurs while loading data how can u handle that without reloading whole data again and again.

12. why do u go for event handlers.. why to use which event handler.. can u give practical    example.

13. have u used sort transformation? why u used that? (or) why u did not used that?

14. how many types of tranformations? what are they?

15. difference between mergejoin and lookup? which is better? why not merge join.

16. what is cascading parameters.

17. what is drill through report.

18. how do you deploy reports in ur project.

19. diff between union and unionall

20. what is coalesce in sql server.

21. what is the use of isnull in sqlserver.

Export Data Starting From Special Cell in Excell File - SSIS

Sometimes you have a formated Excel file, which you want to use this file as Destination, so you may need to import data start from special Cell inside the excel file.
By default SSIS import data from Column1 and Row1 in excel file. But you can change it.

Solution:

Suppose this excel format:

in this sample data should import start from column 4 and row 5 ( right under the "firstname" word)

so in the Excel Destination:
set Data access mode as SQL command,
and write this statement under SQL command text :

select f4,f5 from [Sheet2$] where index='r4'



this means that you want to import data in f4 and f5 ( these are column headers in our formatted excel file ), into Sheet2, and after row with index='r4' ( r4 is row header in our formatted excel file ).

and this will be result:

Update / Insert ( UPSERT ) MySQL Table From SQL Server Table - SSIS

There are lots of links which described how to UPSERT( Update / Insert ) between two SQL Server tables. which mostly used Lookup

Transform with OLEDB Command, like this:
http://www.insideit.fr/post/2010/06/17/Lookup-simple-sur-SSIS

But when you work with MySQL there is a limitation, you can not use OLEDB Connections, you can use ODBC or ADO.NET Connectors from

.NET to MySQL Database, as described briefly here:
http://blogs.msdn.com/b/mattm/archive/2008/03/03/connecting-to-mysql-from-ssis.aspx

So, How you can UPSERT with MySQL Tables? You will face problem with Lookup transform because you have no OLEDB Connection to mysql,

and you can not use OLEDB Command on the other side.

This is a solution for Upsert ( Update/Insert) with MySQL table:
(Note that in this solution Cache connection manager used which is only available in SSIS 2008)

 Assume Structure of SQL Server table is :

CREATE TABLE [dbo].[SourceSqlServerTable](
    [ID] [int] NOT NULL,
    [FirstName] [varchar](255) NULL,
    [LastName] [varchar](255) NULL)

and Structure of MySQL table is:

CREATE TABLE mysqltable (
  ID int(11) DEFAULT NULL,
  FirstName varchar(255) DEFAULT NULL,
  LastName varchar(255) DEFAULT NULL
)

Data in SQL Server Table:

Data in MySQL Table:

Solution:

There are three steps as follows,
First Step:

Add a data flow task to control flow, name it as "Fill Cache Component"
Add an ADO.NET Source which pointing to the MySQL Table.
For finding how to create connection from SSIS to MySQL look at this article:
http://blogs.msdn.com/b/mattm/archive/2008/03/03/connecting-to-mysql-from-ssis.aspx

 

Then add a Cache Transform and connect data path ( green arrow ) from ado.net source to this cache transform.

Double click on connection manager,
Click on New to create new cache connection.
In cache connection manager editor, check the "Use file cache" checkbox.
And browse a path to save cache file there,

In the columns tab, set PK of mysql table as index ( set index position as 1 ),then OK.

Map columns.

The first step fills Cache file with data from MySQL Table ( Which is lookup table ).

Second Step:

Go back to control flow.
Create new Variable of OBJECT data type in package scope and name it as UpdatedRows.
Add another data flow task, name this one as "Lookup".
Connect precedence constraint ( green arrow ) from first data flow task to second data flow task.
Then double click on second data flow task.
Add an OLEDB Source ( setup a new oledb connection to sql server database ) and point it to SQL Server table.

Then add a Lookup transform, connect green arrow from oledb source to lookup.
Then double click on lookup, and in lookup transformation editor,
Set Connection Type as "Cache Connection manager".
And set "specify how to handle rows with no match entries" with "redirect no match rows to no match output".

Then in connection tab, select cache connection manager from the drop down list.

And in columns tab, map joining fields

Add an ado.net Destination pointing to the MySQL database, and map columns.

This will do INSERT part of UPSERT.
Add a RecordSet Destination, and double click on it, in Component properties tab, set VariableName with User::UpdatedRows.

In the second step you INSERT new rows in MySQL table, and fills UpdatedRows to an object datatype variable.
This is whole schema of this second Data flow task:

Third Step:

Go back to control flow,
Create these Variables:

Name        Scope        DataType      Value
----------------------------------------------
ID          Package      Int32         0
FirstName   Package      String
LastName    Package      String

Add a Foreach Loop Container, and connect precedence constraint from "lookup" data flow to this container.
In Foreach Loop Editor, Set Enumerator as ADO Enumerator.
And set Ado object source variable with User::UpdatedRows.

Then in Variable mappings tab, do these mappings:

Then add an execute sql task inside foreach loop container,
And set connection to MySQL database there,
Write update command in SQLStatement property,as below:

Update MySQLTable
Set FirstName=@FirstName
,LastName=@LastName
where ID=@ID

 

Then in Parameter mappings tab, do these mappings:

That's all, this is the whole schema of package:

Now run the package, and result in MySQL table after Upsert will be:

The End.