Microsoft Technologies: SQL Server Integration Services Interview Questions and Answers

1. How to provide security for the configuration file (xml package configuration file)?

2. Different approaches of deployment of package in ssis?

3. Three tasks are running in your package and 2 tasks are successfully executed and third task is failed, in this situation I need to rollback 2nd and 3rd tasks, so what can u do in SSIS package and sql server?

4. There are 10 records in a flat file source, among them 9 will be executed successfully and 10^th record is failed, in this scenario I need to get all 10 records source level to target level, in target level then 10^th record failure error will be displayed, what I need to do?

5. Explain the dynamic behavior of your project?

6. Explain the validations of a package at runtime?

7. What are isolations in SSIS, and where u can use this?

8. What is optimizing packages?

9. Tell me one complex packages in your project?(which task mostly we are used)

10. What is linked server?

11. I created one package with some file. I have diff servers having that package with diff configuration file. Is possible to execute that package in servers simultaneously?

15. What is incremental loading and decremental loading?

16. Microsoft office 2007 excel sheet supported by sql server 2005 or not?

17. What is the difference between file system and sql server ( at the time of deployment)?

I have one package in d(d drive) folder I want move that package in to e(e drive) folder how to move the package?

18. I have table like this

Sno	Sname
1,2	Sreenivas
3,4	Reddy
5,6	Raja
7,8	Reddy

I want like the following table

Sno	Sname
1	Sreenivas
2	Sreenivas
3	Reddy
4	Reddy
5	Raja
6	Raja
7	Reddy
8	reddy

19.I have one package that package scheduled by daily 6 am but the job is failed at Saturday then what I need to do?(where we go how to resolve)

21. What is parallel execution in ssis?

22. What type errors occurred commonly in your project and what are those names?

24. I have one package and that package is already scheduled is it possible to apply the transaction for that package?

25.suppose I have one folder with 5 file text files by using for each file enumerator we store the files in to one folder but suddenly tomorrow one file add to that folder how to store the file into same destination?

26. in source table data having like this

Eno	Ename	Esloc	Deptno
1	Sreenu	Hyd	10,20,30,40,50

I want like this

Eno	Ename	Esloc	Deptno
1	Sreenu	Hyd	10
1	Sreenu	Hyd	20
1	Sreenu	Hyd	30
1	Sreenu	Hyd	40
1	Sreenu	Hyd	50

How to do this?

27. Suppose I have one destination table with some data suddenly 2 excel files data want to insert into the destination table but how to know this data is already inserted into the destination and only new data is inserted into the destination?

28. Why we are using xml file configuration file?

29. How to access and execute the packages clients?

30. In ssis package I created a data ware house by using slowly change dimension.

Cname	Cadd	Status
Sreenu	Bangalore	True
Sreenu	Hyderabad	False
Sreenu	Kadapa	False
Sreenu	Badvel	False
Sreenu	Pml	false

From the above how to know second row?

31. What is smtp server and what is the main purpose?

32. In my source table having 1000 records from that I want move 10 to 990 rows then what I need to do?

33. I have two sources with two tables and one table is having data and another table having conditions how to use the conditions in the table?

34. I have one ssis package. How to know the how much time take for executing this package and after improving the performance how to see the time?

35. I developed one package how to know the whether the package having data or not?

36, I have one parent, child package in case the errors found child package how to handle that errors?

36.in my sql server one package is there how to move that package into some other server?

37. How to Concat row data through ssis?

Source:

Ename	EmpNo
Stev	100
Methew	100
John	101
Tom	101

Target:

Ename	EmpNo
Stev methew	100
John tom	101

38. How to send Unique (Distinct) records into One target and duplicates into another tatget?

Source:

Ename	EmpNo
Stev	100
Stev	100
John	101
Mathew	102

Output:

Target_1:

Ename	EmpNo
Stev	100
John	101
Mathew	102

Target_2:

Ename	EmpNo
Stev	100

38. How do u populate 1^st record to 1^st target , 2^nd record to 2^nd target ,3^rd record to 3^rd target and 4^th record to 1^st target through ssis?

39. We have a target source table containing 3 columns :

Col1, Col2 and Col3. There is only 1 row in the table as follows:

Col1Col2Col3
-----------------
a b c
There is target table containg only 1 column Col. Design a mapping so that the target table contains 3 rows as follows:

Col
-----
a
b
c

40. There is a source table that contains duplicate rows.Design a mapping to load all the unique rows in 1 target while all the duplicate rows (only 1 occurence) in another target.
41.

There is a source table containing 2 columns Col1 and Col2 with data as follows:

Col1   Col2
a          l
b         p
a         m
a         n
b         q
x          y

Design a mapping to load a target table with following values from the above mentioned source:

Col1    Col2
a        l,m,n
b       p,q
x        y

Design an ssis package to load first half records to 1 target while other half records to a separate target.

SSRS - SQL Server Reporting Services

Q: What is SSRS?
SQL Server Reporting Service is one of the server-based software systems that generate reports developed by Microsoft. It is used for preparing and delivering interactive and variety of printed reports. It is administered through an interface that is web based. Reporting services utilizes a web service interface for supporting and developing of customized reporting applicatons. It can be competed with Crystal Reports and other business intelligent tools.

Q: Explain SSRS Architecture?
Reporting services architecture is comprises of integrated components. It is multi-tiered, included with application, server and data layers. This architecture is scalable and modular. A single installation can be used across multiple computers. It includes the following components: - Report Manager, Reporting Designer, Browser Types Supported by Reporting services, Report server, Report server command line utilities, Report Server Database, Reporting Services Extensibility, Data sources that is supported by Reporting Services.

Q: Explain Reporting Life Cycye?
The Reporting Lifecycle includes - Report designing – The designing is done in Visual Studio Report Designer. It generates a class which embodies the Report Definition. - Report processing – The processing includes binging the report definition with data from the report data source. It performs on all grouping, sorting and filtering calculations. The expressions are evaluated except the page header, footer and section items. Later it fires the Binding event and Bound event. As a result of the processing, it produces Report Instance. Report instance may be persisted and stored which can be rendered at a later point of time. - Report Rendering: Report rendering starts by passing the Report Instance to a specific rendering extension (HTML or PDF formats). The instance of reports is paged if paging supported by output format. The expressions of items are evaluated in the page header and footer sections for every page. As a final step, the report is rendered to the specific output document.

Q: How to finetune Reports?
To tune-up the Reporting Services, follow the below mentioned ways: - Expand the Server or utilizing the reporting services of another database server. For better embedding of report contents, report application’s logic and characteristics can have a duplicate copy of data. - Replication of data continuously. Using nolock, the issues of locking can well be resolved and the performance of the query can be improved. This can be done by using dirty read at the time of duplicating the data is unavailable.

Q: What are Data Driven Subscriptions?
Reporting Services provides data-driven subscriptions so that you can customize the distribution of a report based on dynamic subscriber data. Data-driven subscriptions are intended for the following kinds of scenarios: Distributing reports to a large recipient pool whose membership may change from one distribution to the next. For example distribute a monthly report to all current customers. Distributing reports to a specific group of recipients based on predefined criteria. For example send a sales performance report to the top ten sales managers in an organization.

Q: What is Linked Report?

Q: What are different types of roles provided by SSRS?

Q: Difference between Logical Page an Physical Page in SSRS.
Logical page breaks are page breaks that you insert before or after report items or groups. Page breaks help to determine how the content is fitted to a report page for optimal viewing when rendering or exporting the report. The following rules apply when rendering logical page breaks: Logical page breaks are ignored for report items that are constantly hidden and for report items where the visibility is controlled by clicking another report item. Logical page breaks are applied on conditionally visible items if they are currently visible at the time the report is rendered. Space is preserved between the report item with the logical page break and its peer report items. Logical page breaks that are inserted before a report item push the report item down to the next page. The report item is rendered at the top of the next page. Logical page breaks defined on items in table or matrix cells are not kept. This does not apply to items in lists.

Q: User want only to display only pdf as export option in report Manager. How to acheive this?

Q: Name and Describe few console utilities for SSRS?

Q: Name few Endpoints exposed by SSRS 2012?

Q: Describe different Processing Modes offered by SSRS?

Q: When to Use Null Data driven Subscription?
Create a data-driven subscription that uses the Null Delivery Provider. When you specify the Null Delivery Provider as the method of delivery in the subscription, the report server targets the report server database as the delivery destination and uses a specialized rendering extension called the null rendering extension. In contrast with other delivery extensions, the Null Delivery Provider does not have delivery settings that you can configure through a subscription definition.

Q: How can you monitor the report Usage?

Q: How does the report manager work in SSRS?
Report manager is a web application. In SSRS it is accessed by a URL. The interface of this Report manager depends on the permissions of the user. This means to access any functionality or perform any task, the user must be assigned a role. A user with a role of full permissions can entire all the features and menus of the report. To configure the report manager, a URL needs to be defined.

Q: What are the Reporting Services components?
Reporting services components assist in development. These processing components include some tools that are used to create, manage and view reports. A report designer is used to create the reports. a report sever is used to execute and distribute reports. a report manager is used to manage the report server.

Q:SQL Server Reporting Services vs Crystal Reports.
Crystal reports are processed by IIS while SSRS have a report server. Caching in Crystal reports is available through cache server. On the other hand, caching in SSRS is available for Report history snapshots. Crystal reports have standards and user defined field labels. SSRS allows only user defined field labels.

Q: What is Report Builder?
Report Builder is a business-user, ad-hoc report design client that allows users to design reports based on the business terms (Report Builder model) they are familiar with, but without needing to understand database schemas or how to write SQL or MDX queries. Report Builder works with both SQL Server and Analysis Services data sources.

Q: How does Report Builder support Analysis Services cubes?
Report Builder supports relational SQL and Analysis Services data sources in SQL Server 2005. To create a model for Analysis Services cube, go to Report Manager or Management Studio, create a data source for your Analysis Services database, and then select the Generate Model option to create the model.

Q: How do users use Report Builder with SQL Server data sources?
While models that provide access to SQL Server Analysis Services are automatically generated on the report server, the Report Builder Model Designer can be used to generate or modify the models that are built on top of SQL Server relational databases. These model-building projects are a new type of project within a Visual Studio–based development shell.

Q: How do I get Report Builder to generate a parameter that can be set by users viewing the report?
In the filter dialog box, click the name of the criteria that you would like to prompt the user for when viewing the report. For example, for the criteria Order Year=2000, click Order Year. Select the Prompt option in the drop-down list.

Q: What new data source types were added in SSRS 2012?
In addition to the data source types available in SSRS 2005 (SQL Server, Oracle, ODBC, OLE DB), the following have been added in SSRS 2008: SQL Server 2005 Analysis Services SQL Server 2005 Integration Services SQL Server 2005 Report Builder Models XML (through URL and Web services) SAP

Q: How can I add Reporting Services reports to my application?
Visual Studio 2005 (Standard and Enterprise editions) contains a set of freely redistributable Report Viewer controls that make it easy to embed Reporting Services functionality into custom applications. Two versions of the Report Viewer exist, one for rich Windows client applications and one for ASP.NET applications.

Q: Do I need a report server to run reports in my application?
In addition to publishing reports to a report server, you can build reports using the Report Designer that is directly integrated with Visual Studio language projects. You can embed reports directly in any Windows Forms or ASP.NET Web application without access to a report server. The data access in embedded reports is a natural extension of the Visual Studio data facilities. Not only can you use traditional databases as a source of data for your reports, you can use object collections as well.

Q: Can you import Microsoft Excel data to SSRS?
Reporting Services does not import data. It only queries data in whatever format it is stored in their native storage system. I will assume that you're asking whether you can create reports and use Excel spreadsheets as data sources. The answer is Yes, Reporting Services supports a wide variety of data sources, including Excel files. You'll get the best performance with the built-in native .NET providers but you should be able to connect to any ODBC or OLE-DB data source, whether it comes from Microsoft or a third-party company.

Q: Can we deploy SSRS reports on our personal website?
Your reports can only be deployed on a reporting services site. Your only option for viewing them from other sites is an HTTP link. Some tools, like SharePoint offer controls allowing you to view reports in the context of the other websites, but the report is still deployed to and hosted from reporting services.

Q: Can we use datagrids for our report in SSRS?
I've got an ASP.NET project that populates a datagrid. I'd like to use the datagrid as my datasource for my report using SQL Server Reporting Services. Is this possible? The simple answer is no. However, nothing's ever simple. A set of reporting controls was added in Visual Studio 2010 allowing you to report in a dataset, on data that was supplied by you. So, if you retrieved your data into a dataset, bound the datagrid to the dataset so it had data to display, you could then use that dataset as the datasource for the reporting controls. These are then client-side reports, not server reports though.

Q: What are the drawbacks of reporting in SSRS?
For many years, Microsoft had no direct solution for reporting with the SQL Server besides Crystal Reports. Now, they have SQL Server Reporting Services, but it does have several drawbacks. It is still complex to understand the complete functionality and structure of this new component, and many users are still relying on the reporting application they are more familiar with, which is Crystal Reports. Also, components in SSRS like Report Builder and Report Designer are meant for different users for different aspects of the report process, yet complete understanding and exposure to both is important to utilize both functions fully and extensively. There are also issues when exporting very large reports to Microsoft Excel, as it can lead to a loss of data.

Q: Will running SSRS on Windows XP limit the number of users?
Yes, but not because of SSRS. The Internet Information Services (IIS) component of Windows XP only allows a small number of users to connect to the website at once. As SSRS runs via IIS, this would prevent more than a few people from using SSRS at once. Also, the only edition of SSRS that will install on Windows XP is the Developer Edition. This edition can not be used for production use. You need Standard or Enterprise Edition for production use, which requires a Server OS to install on (Windows 2003 Standard, Windows 2008 Standard, etc).

Q: Are there issues when exporting SSRS reports into Microsoft Excel?
When my users are trying to export a SSRS report into Microsoft Excel, one or two columns in the report appear to merge together. Why might this be? Exporting from SSRS is not always perfect, even if you stay within the Microsoft range of products. If you have extra resources, you could splurge for an add-on that offers much better control over exporting to Excel, such as OfficeWriter. From my experience, though, it is usually headers or footers that cause exporting issues. If any of these headers or footers overlap with data columns in your report, you will find that the exported version of the report has merged cells. Also, check columns next to each other to make sure that there is no overlap, as well.

Q: How to send a SSRS report from SSIS?
Often there is a requirement to be able to send a SSRS report in Excel, PDF or another format to different users from a SSIS package one it has finished performing a data load. In order to do this, first you need to create a subscription to the report. You can create a SSRS report subscription from Report Manager. At the report subscription you can mention the report format and the email address of the recipient. When you create a schedule for the SSRS report, a SQL Server Agent Job will be created. From the SSIS, by using sp_start_job and passing the relevant job name you can execute the SSRS report subscription.

SSIS Interview Questions and Answers

1. How to Generate an Auto Incremental Number in a SSIS Package?

A script component can be used for the designated task. The steps are as follows:

1. Drag and drop the Script Document to the Data flow and select the Script Component Type as Transformation.

2. Double click the Script Component.

3. Select the column which is to pass through the script component, in the Input Columns tab.

4. Add a column with an integer data type, in the Inputs and Outputs tab.

2. Will trigger fire when inserting data through SSIS package?
1.In the data flow task, go to the Advanced Editor of OLEDB Destination, and there should be a property "FastLoadOptions". Specify FIRE_TRIGGERS as an additional option.

2. SQL Destination Editor:

3. How to deploy packages from one server to another server

1.To copy the deployment bundle
Locate the deployment bundle on your computer.
If you used the default location, the deployment bundle is the Bin\Deployment folder within the Deployment Tutorial folder.
Right-click the Deployment folder and click Copy.
Locate the public share to which you want to copy the folder on the target computer and click Paste.
2: Running the Package Installation Wizard
1. On the destination computer, locate the deployment bundle.
2.If you used the default value—bin\Deployment—as the location of the deployment utility, the deployment bundle is the Deployment folder in the
project1.
3. In the Deployment folder, double-click the manifest file, Project1.SSISDeploymentManifest.
4. On the Welcome page of the Package Installation Wizard, click Next.
5. On the Deploy SSIS Packages page, select the SQL Server deployment option, select the Validate packages after installation check box, and then
click Next.
6. On the Specify Target SQL Server page, specify (local), in the Server name box.
7. If the instance of SQL Server supports Windows Authentication, select Use Windows Authentication; otherwise, select Use SQL Server Authentication and provide a user name and a password.
8. Verify that the Rely on server storage for encryption check box is cleared.
Click Next.
9. On the Select Installation Folder page, click Browse.
10. In the Browse For Folder dialog box, expand My Computer and then click Local Disk (C:).
11. Click Make New Folder and replace the default name of the new folder, New Folder, with Project1Install.
12. On the Select Installation Folder page, verify that the Folder box contains C:\ProjectInstall and then click Next.
13. On the Confirm Installation page, click Next.
14. The wizard installs the packages. After installation is completed, the Configure Packages page opens.

4. What are the different types of Transaction Options

Required: If a transaction already exists at the upper level, the current executable will join the transaction. If No transaction at the upper level, a new transaction is created automatically.

Supported:In any executable, if there is a transaction at upper level, the executable join the transaction else do not create a new transaction.

Not Supported:The executable of the package do not honour any transaction ie do not join other transaction nor creates new one.

5. Delay Validation, Forced Execution

Delay Validation: Validation take place during the package execution.
Early Validation: Validation take place just before the package execution.

6. How to debug a package

For debugging a package, we have 3 options:
-Setting breakpoints in a package, container or task, Script Task
-Using Data Viewer in the data flow task
-Setting Error redirection in the Data Flow task

7. If the package fails, what steps u take

8. Incremental Load in SSIS

Using Slowly Changing Dimension
Using Lookup and Cache Transformation

9. How to migrate Sql server 2005 Package to 2008 version

1. In BIDS, by right click on the "SSIS Packages" folder of an SSIS project and selecting "Upgrade All Packages".
2. Running "ssisupgrade.exe" from the command line (default physical location C:\Program Files\Microsoft SQL Server\100\DTS\Bin folder).
3. If you open a SSIS 2005 project in BIDS 2008, it will automatically launch the SSIS package upgrade wizard.

10. Difference between FileSystem and Sql server Deployment

File System Deployment:
We can save the package on a physical location on hard drive or any shared folder with this option, and we should provide a full qualified path to stored package in the FileSystem option.
Sql Server Deployment:
SSIS packages will be stored in the msdb database, in the sysssispackages table.

11. Difference between Full Cache and Partial Cache

Partial Cache: The lookup cache starts off empty at the beginning of the data flow. When a new row comes in, the lookup transform checks its cache for the matching values. If no match is found, it queries the database. If the match is found at the database, the values are cached so they can be used the next time a matching row comes in.

Full Cache: The default cache mode for lookup is Full cache. The database is queried once during the pre-execute phase of the data flow. The entire reference set is pulled into memory. This approach uses most of the memory. Caching takes place before any rows are read from the data flow source. Lookup operations will be very fast during execution.

12. Cache Transformation

Cache Transformation: to cache the data used in the Lookup transform.
When to use Full cache mode:
When you're accessing a large portion of your reference set
When you have a small reference table
When your database is remote or under heavy load, and you want to reduce the number of queries sent to the server
When to use Partial cache mode:
When you're processing a small number of rows and it's not worth the time to charge the full cache.
When you have a large reference table.
When your data flow is adding new rows to your reference table.
When you want to limit the size of your reference table by modifying query with parameters from the data flow.

13. Explain Slowly Changing Dimesnion

Type 1 - update the columns in the dimension row without preserving any change history.
Type 2 - preserve the change history in the dimension table and create a new row when there are changes.
Type 3 - some combination of Type 1 and Type 2, usually maintaining multiple instances of a column in the dimension row; e.g. a current value and one or more previous values.

14. Different types of File Enumerators

Foreach ADO:
The ADO Enumerator enumerates rows in a table. For example, we can get the rows in the ADO records.The variable must be of Object data type.
Foreach ADO.NET Schema Rowset:
The ADO.Net Enumerator enumerates the schema information. For example, we can get the table from the database.
Foreach File:
The File Enumerator enumerates files in a folder. For example, we can get all the files which have the *.txt extension in a windows folder and its sub folders.
Foreach From Variable:
The Variable Enumerator enumerates objects that specified variables contain. Here enumerator objects are nothing but an array or data table.
Foreach Item:
The Item Enumerator enumerates the collections. For example, we can enumerate the names of executables and working directories that an “Execute Process” task uses.
Foreach Nodelist:
The Node List Enumerator enumerates the result of an XPath expression.
Foreach SMO:
The SMO Enumerator enumerates SQL Server Management Objects (SMO). For example, we can get the list of functions or views in a SQL Server database.

16. How to schedule a package (Role of Sql Server Agent)
In order for the job to run successfully, the SQL Server agent should be running on the target machine.

We can start the SQL Server Agent Services in numerous ways like:-

· Starting SQL Server Agent Service from Command Line

· Starting SQL Server Agent Service from Services.MSC console

· Starting SQL Server Agent Service using SQL Server Configuration Manager

· Starting SQL Server Agent Service using SQL Server Management Studio (SSMS)

17. What are containers? (For loop, Sequence Container)

SSIS Containers are controls that provide structure to SSIS packages. Containers support repeating control flows in packages and they group tasks and containers into meaningful units of work. Containers can include other containers in addition to tasks.

Container Type	Container Description	Purpose of SSIS Container
Foreach Loop Container	This container runs a Control Flow repeatedly using an enumerator.	To repeat tasks for each element in a collection, for example retrieve files from a folder, running T-SQL statements that reside in multiple files, or running a command for multiple objects.
For Loop Container	This container runs a Control Flow repeatedly by checking conditional expression (same as For Loop in programming language).	To repeat tasks until a specified expression evaluates to false. For example, a package can send a different e-mail message seven times, one time for every day of the week.
Sequence Container	Groups tasks as well as containers into Control Flows that are subsets of the package Control Flow.	This container group tasks and containers that must succeed or fail as a unit. For example, a package can group tasks that delete and add rows in a database table, and then commit or roll back all the tasks when one fails.

18. What are precedence constraints

A task will only execute if the condition that is set by the precedence constraint preceding the task is met. By using these constraints,it will choose different execution paths depending on the success or failure of other tasks.

19. Performance Optimization in SSIS

1. Avoid Asynchronous Transformation (Sort T/F) wherever possible. Sort T/F required all the incoming rows to be arrivaed before start processing. Instead of using Sort T/F, we get sorted rows from datasource using ORDER By clause.

2. Pulling High Volumes of Data

Drop all Non-Clustered Indexes and Clustered Index if exists, then Transfer and load the data into Destination Table. Create Clustered Index and Non-clustered indexes.

3. Avoid SELECT *

DataFlow Task uses buffer oriented architecture for data transfer and transformation. When data transfer from Source to Destination, the data first comes into the buffer, required transformations are done in the buffer itself and then written to Destination.

The size of buffer depends on the estimated row size. The estimated row size is equal to the maximum size of all columns in the row. So the more columns in a row means less number of rows in a buffer. Hence select only those columns which are required at the destination.

Even if we need all the columns from source, we should use the column name specifically in the SELECT statement, otherwise it takes another round for the source to gather meta-data about the columns when u are using SELECT *.

4. Effect of OLEDB Destination Settings

There are couple of settings with OLEDB destination which can impact the performance of data transfer as listed below.

Data Access Mode – This setting provides the 'fast load' option which internally uses a BULK INSERT statement for uploading data into the destination table instead of a simple INSERT statement (for each single row) as in the case for other options. So unless you have a reason for changing it, don't change this default value of fast load. If you select the 'fast load' option, there are also a couple of other settings which you can use as discussed below.

Keep Identity – By default this setting is unchecked which means the destination table (if it has an identity column) will create identity values on its own. If you check this setting, the dataflow engine will ensure that the source identity values are preserved and same value is inserted into the destination table.

Keep Nulls – Again by default this setting is unchecked which means default value will be inserted (if the default constraint is defined on the target column) during insert into the destination table if NULL value is coming from the source for that particular column. If you check this option then default constraint on the destination table's column will be ignored and preserved NULL of the source column will be inserted into the destination.

Table Lock – By default this setting is checked and the recommendation is to let it be checked unless the same table is being used by some other process at same time. It specifies a table lock will be acquired on the destination table instead of acquiring multiple row level locks, which could turn into lock escalation problems.

Check Constraints – Again by default this setting is checked and recommendation is to un-check it if you are sure that the incoming data is not going to violate constraints of the destination table. This setting specifies that the dataflow pipeline engine will validate the incoming data against the constraints of target table. If you un-check this option it will improve the performance of the data load.

#5 - Effect of Rows Per Batch and Maximum Insert Commit Size Settings

Rows per batch:

the default value for this setting is -1 which specifies all incoming rows will be treated as a single batch. You can change this default behavior and break all incoming rows into multiple batches. The allowed value is only positive integer which specifies the maximum number of rows in a batch.

Maximum insert commit size:

The default value for this setting is '2147483647' (largest value for 4 byte integer type) which specifies all incoming rows will be committed once on successful completion. You can specify a positive value for this setting to indicate that commit will be done for those number of records. Changing the default value for this setting will put overhead on the dataflow engine to commit several times. Yes that is true, but at the same time it will release the pressure on the transaction log and tempdb to grow specifically during high volume data transfers.

The above two settings are very important to understand to improve the performance of tempdb and the transaction log. For example if you leave 'Max insert commit size' to its default, the transaction log and tempdb will keep on growing during the extraction process and if you are transferring a high volume of data the tempdb will soon run out of memory as a result of this your extraction will fail. So it is recommended to set these values to an optimum value based on your environment.

#7 - DefaultBufferMaxSize and DefaultBufferMaxRows :

The execution tree creates buffers for storing incoming rows and performing transformations.

The number of buffer created is dependent on how many rows fit into a buffer and how many rows fit into a buffer dependent on few other factors. The first consideration is the estimated row size, which is the sum of the maximum sizes of all the columns from the incoming records. The second consideration is the DefaultBufferMaxSize property of the data flow task. This property specifies the default maximum size of a buffer. The default value is 10 MB and its upper and lower boundaries are constrained by two internal properties of SSIS which are MaxBufferSize (100MB) and MinBufferSize (64 KB). It means the size of a buffer can be as small as 64 KB and as large as 100 MB. The third factor is, DefaultBufferMaxRows which is again a property of data flow task which specifies the default number of rows in a buffer. Its default value is 10000.

If the size exceeds the DefaultBufferMaxSize then it reduces the rows in the buffer. For better buffer performance you can do two things.First you can remove unwanted columns from the source and set data type in each column appropriately, especially if your source is flat file. This will enable you to accommodate as many rows as possible in the buffer.

Second, if your system has sufficient memory available, you can tune these properties to have a small number of large buffers, which could improve performance. Beware if you change the values of these properties to a point where page spooling (see Best Practices #8) begins, it adversely impacts performance. So before you set a value for these properties, first thoroughly testing in your environment and set the values appropriately.

#8 - How DelayValidation property can help you

SSIS uses two types of validation.

First is package validation (early validation) which validates the package and all its components before starting the execution of the package.

Second SSIS uses component validation (late validation), which validates the components of the package once started.

Let's consider a scenario where the first component of the package creates an object i.e. a temporary table, which is being referenced by the second component of the package. During package validation, the first component has not yet executed, so no object has been created causing a package validation failure when validating the second component. SSIS will throw a validation exception and will not start the package execution. So how will you get this package running in this common scenario?

To help you in this scenario, every component has a DelayValidation (default=FALSE) property. If you set it to TRUE, early validation will be skipped and the component will be validated only at the component level (late validation) which is during package execution

9. Better performance with parallel execution
10. When to use events logging and when to avoid.
11. Monitoring the SSIS Performance with Performance Counters
12. Checkpoint features helps in package restarting

20. Upgrade DTS package to SSIS

1. In BIDS, from the Project Menu, select 'Migrate DTS 2000 Package'

2. In the Package Migration Wizard, choose the Source, Sql Server 2000 Server Name, Destination folder.

3. Select the List of packages that needs to be upgraded to SSIS

4. Specifty the Log file for Package Migration.

21. Difference between Control Flow and Data Flow

Control flow consists of one or more tasks and containers that execute when the package runs. We use precedence constraints to connect the tasks and containers in a package. SSIS provides three different types of control flow elements: Containers that provide structures in packages, Tasks that provide functionality, and Precedence Constraints that connect the executables, containers, and tasks into an ordered control flow.

A data flow consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations. The Data Flow task is the executable within the SSIS package that creates, orders, and runs the data flow. Data Sources, Transformations, and Data Destinations are the three important categories in the Data Flow.

22. How to execute a Stored Procedure from SSIS

using Execute SQL Task

23. what are the possible locations to save SSIS package?

File System: We can save the package on a physical location on hard drive or any shared folder with this option, and we should provide a full qualified path to stored package in the FileSystem option.

Sql Server: SSIS packages will be stored in the msdb database, in the sysssispackages table.

Package Store

24. How to unzip a File in SSIS?

Use Execute Process Task in the Control Flow Task.

- From BIDS, drag and drop an Execute Process Task to the control flow and configure.

In the Execute Process, perform the following configurations:

-Executable: The path of the application that is being used.

-Arguments: Need to supply the arguments to extract the zipped files.
-Working Directory: The current directory for all process.

25. How to provide security to packages?

We can provide security to packages in 2 ways

1. Package encryption

2. Password protection

1. DonotSaveSensitive: any sensitive information is simply not written out to the package XML file when you save the package.
2. EncryptSensitiveWithUserKey: encrypts sensitive information based on the credentials of the user who created the package. It is the default value for the ProtectionLevel property.
3. EncryptSensitiveWithPassword: requires to specify a password in the package, and this password will be used to encrypt and decrypt the sensitive information in the package.
4. EncryptAllWithPassword: allows to encrypt the entire contents of the SSIS package with your specified password.
5. EncryptAllWithUserKey: allows to encrypt the entire contents of the SSIS package by using the user key.
6. Server Storage: allows the package to retain all sensitive information when you are saving the package to SQL Server. SSIS packages are saved to MSDB database of SQL Server.
You can change the Protection Level of deployed packages by using the DTUTIL utility.
26. How to track a variable in ssis?

OnVariableValueChanged: This event gets raised when value of the variable is changed.

1.Set the "EvaluateasExpression" property of the variable as True.

2.Set the "RaiseChangedEvent" property of the variable as True.

3.Create an event handler for the "OnVariableValueChanged" event for the container in which the variable is scoped.

27. FTP Task:

The FTP task downloads and uploads data files and manages directories on servers. For example, a package can download data files from a remote server.

use the FTP task for the following purposes:
1. Copying directories and data files from one directory to another, before or after moving data, and applying transformations to the data.
2. Downloading files from an FTP location and applying transformations to column data before loading the data into a database.

At run time, the FTP task connects to a server by using an FTP connection manager. The FTP connection manager includes the server settings, the credentials for accessing the FTP server, and options such as the time-out and the number of retries for connecting to the server.

The FTP connection manager supports only anonymous authentication and basic authentication. It does not support Windows Authentication.
Predefined FTP Operations:
Send Files, Receive File,
Create Local directory, Remove Local Directory,
Create Remote Directory, Remove Remote Directory
Delete Local Files, Delete Remote File
Customer Log Entries available on FTP Task:
FTPConnectingToServer
FTPOperation

28. New features in SSIS 2012
1. GUI Improvements - -Sort packages by name -Package visualization -Zoom -Data flow source/destination wizard -Grouping in dataflow

2. CDC (Change Data Capture) Task and Components - -CDC is nothing but Incremental load loads all rows that have changed since the last load -CDC needs to keep track of which changes have already been processed. -CDC task does this by storing LSNs in a tracking table -CDC source component reads from the CDC table function, based on the LSN it for from the CDC task. -CDC transformation splits records into new rows, updated rows and deleted rows.

3. Flat File Connection Manager Changes - -The Flat File connection manager now supports parsing files with embedded qualifiers. The connection manager also by default always checks for row delimiters to enable the correct parsing of files with rows that are missing column fields. The Flat File Source now supports a varying number of columns, and embedded qualifiers.

4. Offline Connection Managers - -Integration Services now validates all connections before validating all of the data flow components when a package is opened and sets any connections that are slow or unavailable to work offline. This helps to reduce the delay in validating the package data flow. After a package is opened, you can also turn off a connection by right-clicking the connection manager in the Connection Managers area and then clicking Work Offline. This can speed up operations in the SSIS Designer.

5. New Functions/Expressions in SSIS 2012- LEFT:

You now can easily return the leftmost portion of a string rather than use the SUBSTRING function. Left syntax is the same as we know in T-SQL: LEFT(character_expression,number)

REPLACENULL: You can use this function to replace NULL values in the first argument with the expression specified in the second argument. This is equivalent to ISNULL in T-SQL: REPLACENULL(expression, expression)

TOKEN: This function allows you to return a substring by using delimiters to separate a string into tokens and then specifying which occurrence to return: TOKEN(character_expression, delimiter_string, occurrence)

TOKENCOUNT: This function uses delimiters to separate a string into tokens and then returns the count of tokens found within the string: TOKENCOUNT(character_expression, delimiter_string)

6. Easy Column Remapping in Data Flow (Mapping Data Flow Columns) -When modifying a data flow, column remapping is sometimes needed -SSIS 2012 maps columns on name instead of id -It also has an improved remapping dialog

7. Shared Connection Managers - To create connection managers at the project level that can shared by multiple packages in the project. The connection manager you create at the project level is automatically visible in the Connection Managers tab of the SSIS Designer window for all packages. -When converting shared connection managers back to regular (package) connection managers, they disappear in all other packages.

8. Scripting Enhancements- -Now Script task and Script Component support for 4.0. - Breakpoints are supported in Script Component

9. ODBC Source and Destination - -ODBC was not natively supported in 2008 -SSIS 2012 has ODBC source & destination -SSIS 2008 could access ODBC via ADO.NET

10. Reduced Memory Usage by the Merge and Merge Join Transformations – The old SSIS Merge and Merge Join transformations, although helpful, used a lot of system resources and could be a memory hog. In 2012 these tasks are much more robust and reliable. Most importantly, they will not consume excessive memory when the multiple inputs produce data at uneven rates.

11. Undo/Redo - One thing that annoys users in SSIS before 2012 is lack of support of Undo and Redo. Once you performed an operation, you can’t undo that. Now in SSIS 2012, we can see the support of undo/redo.

29. Difference between Script Task and Script Component in SSIS.

	Script Task	Script Component
Control Flow/Date Flow	The Script task is configured on the Control Flow tab of the designer and runs outside the data flow of the package.	The Script component is configured on the Data Flow page of the designer and represents a source, transformation, or destination in the Data Flow task.
Purpose	A Script task can accomplish almost any general-purpose task.	You must specify whether you want to create a source, transformation, or destination with the Script component.
Raising Results	The Script task uses both the TaskResult property and the optional ExecutionValue property of the Dts object to notify the runtime of its results.	The Script component runs as a part of the Data Flow task and does not report results using either of these properties.
Raising Events	The Script task uses the Events property of the Dts object to raise events. For example: Dts.Events.FireError(0, "Event Snippet", ex.Message & ControlChars.CrLf & ex.StackTrace	The Script component raises errors, warnings, and informational messages by using the methods of the IDTSComponentMetaData100 interface returned by the ComponentMetaData property. For example: Dim myMetadata as IDTSComponentMetaData100 myMetaData = Me.ComponentMetaData myMetaData.FireError(...)
Execution	A Script task runs custom code at some point in the package workflow. Unless you put it in a loop container or an event handler, it only runs once.	A Script component also runs once, but typically it runs its main processing routine once for each row of data in the data flow.
Editor	The Script Task Editor has three pages: General, Script, and Expressions. Only the ReadOnlyVariables and ReadWriteVariables, and ScriptLanguage properties directly affect the code that you can write.	The Script Transformation Editor has up to four pages: Input Columns, Inputs and Outputs, Script, and Connection Managers. The metadata and properties that you configure on each of these pages determines the members of the base classes that are autogenerated for your use in coding.
Interaction with the Package	In the code written for a Script task, you use the Dts property to access other features of the package. The Dts property is a member of the ScriptMain class.	In Script component code, you use typed accessor properties to access certain package features such as variables and connection managers. The PreExecute method can access only read-only variables. The PostExecute method can access both read-only and read/write variables.
Using Variables	The Script task uses the Variables property of the Dts object to access variables that are available through the task’s ReadOnlyVariables and ReadWriteVariables properties. For example: string myVar; myVar = Dts.Variables["MyStringVariable"].Value.ToString();	The Script component uses typed accessor properties of the autogenerated based class, created from the component’s ReadOnlyVariables and ReadWriteVariables properties. For example: string myVar; myVar = this.Variables.MyStringVariable;
Using Connections	The Script task uses the Connections property of the Dts object to access connection managers defined in the package. For example: string myFlatFileConnection; myFlatFileConnection = (Dts.Connections["Test Flat File Connection"].AcquireConnection(Dts.Transaction) as String);	The Script component uses typed accessor properties of the autogenerated base class, created from the list of connection managers entered by the user on the Connection Managers page of the editor. For example: IDTSConnectionManager100 connMgr;connMgr = this.Connections.MyADONETConnection;

SSRS Interview Questions and Answers

1. How to pass parameter from Report Viewer Control to sub report?

2. How to open another report in a new window from existing report?

Use a little javascript with a customized URL in the "Jump to URL" option of the Navigation tab.

Non-parameterized Solution

To get started, let's pop up a simple non parameterized report. Follow these instructions:

Instead of using the "Jump to Report" option on the Navigation tab, use the "Jump to URL" option.
Open the expression screen (Fx button).
Enter the following:
="javascript:void(window.open('http://servername?%2freportserver%2fpathto%2freport&rs:Command=Render'))"
Click OK twice, then save and deploy the report.

Parameterized Solution

Assume you have a field called ProductCode. Normally, you might hard code that like this:

http://servername/reportserver?%2fpathto%2freport&rs:Command=Render&ProductCode=123

In this case, you want to pass variables dynamically, using an available value from the source dataset. You can think of it like this:

http://servername/reportserver?%2fpathto%2freport&rs:Command=Render&ProductCode=Fields!ProductCode.Value

The exact syntax in the "Jump to URL" (Fx) expression window will be:

="javascript:void(window.open('http://servername/reportserver?%2fpathto%2freport&rs:Command=Render&ProductCode="+Fields!ProductCode.Value+"'))"

3. How to pass parameter from chart to Table in same report?

4. How to apply custom Colors of chart report?

STEP1:

Create your custome color palette in the report using Custom Code in your report. To do so, click Report => Report Properties => Code and copy below code:

Private colorPalette As String() = { "Blue", "Red", "Teal", "Gold", "Green","#A59D93", "#B8341B", "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}

Private count As Integer = 0

Private mapping As New System.Collections.Hashtable()

Public Function GetColor(ByVal groupingValue As String) As String

If mapping.ContainsKey(groupingValue) Then

Return mapping(groupingValue)

End If

Dim c As String = colorPalette(count Mod colorPalette.Length)

count = count + 1

mapping.Add(groupingValue, c)

Return c

End Function

STEP2:

In the Pie Chart, select Series Properties and select the Fill option from left side.

Now write following expression in the Color expression:

=code.GetColor(Fields!Year.Value)

Here Fields!Year.Value is a field of dataset which is used as Chart Category fields.

5. Can we have Table within a Table in SSRS report?

6. How to apply stylesheet to SSRS Reports

7. Dynamic sorting, Dynamic Grouping in SSRS

Can be done using expressions.

8. Different types of Filters

The 2 types of filters in SSRS are:

Dataset Filter: Filtering within the source query. When you implement a filter within the data set, less data is sent from the source database server to the Report Server - usually a good thing.

Report Filter: This includes filtering after the source query has come back – on a data region (like the Tablix), or a data grouping. When you implement a filter within the report, when the report is re-executed again with different parameter choices, the Report Server uses cached data rather than returning to the database server.

Using a Dataset Filter is the most efficient method.

9. Difference between Filter and Parameter? Which one is better?

In case of Filters, first the data will be fetched from the database, then the Filters are applied on the fetched data. Filters are applied at run time first on the dataset, and then on the data region, and then on the group, in top-down order for group hierarchies.

To add a filter, we must specify a filter equation (expression). The data type of filtered data and value must match.

Parameters are applied at the database level. The Data will be fetched based on parameters at the database level using WHERE condition in the query.

Parameters are better than Filters in performance.

10. Optimization of Report

Report can be optimized through Caching a report, Snapshot and subscriptions.

11. I have 'State' column in report, display the States in bold, whose State name starts with letter 'A' (eg: Andhra pradesh, Assam should be in bold)

12. In which scenario you used Matrix Report

13. Crosstabs in SSRS

14. Role of Report Manager

Deploying the reports onto the web server.

Delivering the reports through E-mail or File Share using the subscriptions.

Creating the Cached and Snapshot Reports.

Providing the Security to the reports.

15. How to upload a report to report server

In the Report Manager, we have upload option to upload the reports.

16. What is a Shared Dataset

Shared datasets retrieve data from shared data sources that connect to external data sources. A shared dataset contains a query to provide a consistent set of data for multiple reports. The dataset query can include dataset parameters.

Shared datasets use only shared data sources, not embedded data sources.

To create a shared dataset, you must use an application that creates a shared dataset definition file (.rsd). You can use one of the following applications to create a shared dataset:

1. Report Builder: Use shared dataset design mode and save the shared dataset to a report server or SharePoint site.

2. Report Designer in BIDS: Create shared datasets under the Shared Dataset folder in Solution Explorer. To publish a shared dataset, deploy it to a report server or SharePoint site.

Upload a shared dataset definition (.rsd) file. You can upload a file to the report server or SharePoint site. On a SharePoint site, an uploaded file is not validated against the schema until the shared dataset is cached or used in a report.

The shared dataset definition includes a query, dataset parameters including default values, data options such as case sensitivity, and dataset filters.

17. How do u display the partial text in bold format in textbox in Report? (eg: FirstName LastName, where "FirstName" should in bold fornt and "LastName" should be in normal font.)

Use PlaceHolder

18. place the table in report header and how to repeat on each page

19. parent grouping, child grouping in SSRS

20. A main report contain subreport also. Can we export both main report and subreport to Excel?

Yes. The exported report contains both the mail report and sub report.

21. how to convert PDF report from Portrait to Landscape format?

In Report Properties -->

Set the width of the report to the landscape size of your A4 paper: 29.7 cm

Set the height of the report to 21 cm.

To avoid extra blank pages during export, the size of the body should be less or equal to the size of the report - margins.

Set the width of the body to 26.7 cm (29.7 -1.5 - 1.5)

Set the height of the body to 18 cm (21 - 1.5 -1.5)

22. Error handling in Report

23. Have u worked on any 3rd party Report Tools

There are few third party Report Tools like Nevron, izenda.

24. Different ways of Deploying reports
1. We can deploy the reports using rs.exe tool
2. In the Solution Explorer,

2.1.Right-click the report project, and then click Properties.
2.2.In the Property Pages dialog box for the project, select a configuration to edit from the Configuration list. Common configurations are DebugLocal, Debug, and Release.
2.3.In StartItem, select a report to display in the preview window or in a browser window when the report project is run.
2.4.In the OverwriteDataSources list, select True to overwrite the shared data source on the server each time shared data sources are published, or select False to keep the data source on the server.
2.5.In the TargetDataSourceFolder text box, type the folder on the report server in which to place the published shared data sources. The default value for TargetDataSourceFolder is Data Sources. If you leave this value blank, the data sources will be published to the location specified in TargetReportFolder.
2.6. In the TargetReportFolder text box, type the folder on the report server in which to place the published reports. The default value for TargetReportFolder is the name of the report project.
2.7. In the TargetServerURL text box, type the URL of the target report server. Before you publish a report, you must set this property to a valid report server URL.

3. There are 2 options for deploying the reports that you create with Report Builder 3.0:
1. Report Manager
2. SharePoint document library

25. Difference between Cached Report and Snapshot Report

Cached Report is a saved copy of processed report.
The first time a user clicks the link for a report configured to cache, the report execution process is similar to the on-demand process. The intermediate format is cached and stored in ReportServerTempDB Database until the cache expiry time.

If a user request a different set of parameter values for a cached report, then the report processor treats the requests as a new report executing on demand, but flags it as a second cached instance.

Report snapshot contains the Query and Layout information retrieved at specific point of time. It executes the query and produces the intermediate format. The intermediate format of the report has no expiration time like a cached instance, and is stored in ReportServer Database.

26. Different life cycles of Report

1.Report authoring:

This stage involves creation of reports that are published using the Report Definition language. RDL is an XML based industry standard for defining reports.

Report Designer is a full-featured report authoring tool that runs in Business Intelligence Development Studio and Report Builder.

2. Report management:

This involves managing the published reports as a part of the webservice. The reports are cached for consistency and performance. They can be executed whenever demanded or can be scheduled and executed.

In short Report Management includes:

- Organizing reports and data sources,

- Scheduling report execution and delivery

- Tracking reporting history.

3. Report delivery:

Reports can be delivered to the consumers either on their demand or based on an event. Then they can view them is a web-based format.

–Web based delivery via Report Manager web site

–Subscriptions allow for automated report delivery

–URL Access, Web Services and Report Viewer control

4.Report security:

It is important to protect reports as well as the report resources. Therefore, Reporting Services implement a flexible, role-based security model.

27. Explain the Report Model Steps.
1. Create the report model project
select "Report Model Project" in the Templates list
A report model project contains the definition of the data source (.ds file), the definition of a data source view (.dsv file), and the report model (.smdl file).
2. Define a data source for the report model
3. Define a data source view for the report model
A data source view is a logical data model based on one or more data sources.
SQL Reporting Services generates the report model from the data source view.
4. Define a report model
5. Publish a report model to report server.

28. Difference between RDL and RDLC?
RDL files are created for Sql Server Reporting Services and .RDLC files are for Visual Studio Report Viewer Component.

The <Query> element of RDL contains query or command and is used by the Report Server to connect to the datasources of the report.
The <Query> element is optional in RDLC file. This element is ignored by Report Viewer control because Report Viewer control does not perform any data processing in Local processing mode, but used data that the host application supplies.

29. Difference between Sorting and Interactive Sorting?

30. Report Server Configuration Files
1.RSReportServer.config:
Stores configuration settings for feature areas of the Report Server service: Report Manager, the Report Server Web service, and background processing.
2. RSSrvPolicy.config
Stores the code access security policies for the server extensions.
3. RSMgrPolicy.config
Stores the code access security policies for Report Manager.
4. Web.config for the Report Server Web service
Includes only those settings that are required for ASP.NET.
5. ReportingServicesService.exe.config
6. Registry settings
7. Web.config for Report Manager
Includes only those settings that are required for ASP.NET
8. RSReportDesigner.config
9. RSPreviewPolicy.config

31. How do u secure a Report
1. Authorization is provided through a role-based security model that is specific to Reporting Services.
Different Types of Roles provided by SSRS :
- Browsers
- Content Manager
- My Reports
- Publishers
- Report Builder
2. IIS security controls access to the report server virtual directory and Report Manager.

32.How to Combine Datasets in SSRS (1 Dataset gets data from Oracle and other dataset from Sql Server)

Using LookUP function, we can combine 2 datasets in SSRS.
In the following example, assume that a table is bound to a dataset that includes a field for the product identifier ProductID. A separate dataset called "Product" contains the corresponding product identifier ID and the product name Name.
=Lookup(Fields!ProductID.Value, Fields!ID.Value, Fields!Name.Value, "Product")
In the above expression, Lookup compares the value of ProductID to ID in each row of the dataset called "Product" and, when a match is found, returns the value of the Name field for that row.

SSIS - SQL Server Intigration Services

1. How to provide security for the configuration file (xml package configuration file)?

2. Different approaches of deployment of package in ssis?

5. Explain the dynamic behavior of your project?

6. Explain the validations of a package at runtime?

7. What are isolations in SSIS, and where u can use this?

8. What is optimizing packages?

9. Tell me one complex packages in your project?(which task mostly we are used)

10. What is linked server?

11. I created one package with some file. I have diff servers having that package with diff configuration file. Is possible to execute that package in servers simultaneously?

15. What is incremental loading and decremental loading?

16. Microsoft office 2007 excel sheet supported by sql server 2005 or not?

17. What is the difference between file system and sql server ( at the time of deployment)?

18. I have one package in d(d drive) folder I want move that package in to e(e drive) folder how to move the package?

19. I have table like this

Sno	Sname
1,2	Sreenivas
3,4	Reddy
5,6	Raja
7,8	Reddy

I want like the following table

Sno	Sname
1	Sreenivas
2	Sreenivas
3	Reddy
4	Reddy
5	Raja
6	Raja
7	Reddy
8	reddy

20. I have one package that package scheduled by daily 6 am but the job is failed at Saturday

then what I need to do?(where we go how to resolve)

21. What is parallel execution in ssis?

22. What type errors occurred commonly in your project and what are those names?

24. I have one package and that package is already scheduled is it possible to apply the

transaction for that package?

25. Suppose I have one folder with 5 file text files by using for each file enumerator we store the

files in to one folder but suddenly tomorrow one file add to that folder how to store the file

into same destination?

26. in source table data having like this

Eno	Ename	Esloc	Deptno
1	Sreenu	Hyd	10,20,30,40,50

I want like this

Eno	Ename	Esloc	Deptno
1	Sreenu	Hyd	10
1	Sreenu	Hyd	20
1	Sreenu	Hyd	30
1	Sreenu	Hyd	40
1	Sreenu	Hyd	50

How to do this?

28. Why we are using xml file configuration file?

29. How to access and execute the packages clients?

30. In ssis package I created a data ware house by using slowly change dimension.

Cname	Cadd	Status
Sreenu	Bangalore	True
Sreenu	Hyderabad	False
Sreenu	Kadapa	False
Sreenu	Badvel	False
Sreenu	Pml	false

From the above how to know second row?

31. What is smtp server and what is the main purpose?

32. In my source table having 1000 records from that I want move 10 to 990 rows then what I need to do?

33. I have two sources with two tables and one table is having data and another table having conditions how to use the conditions in the table?

34. I have one ssis package. How to know the how much time take for executing this package and after improving the performance how to see the time?

35. I developed one package how to know the whether the package having data or not?

36, I have one parent, child package in case the errors found child package how to handle that errors?

36.in my sql server one package is there how to move that package into some other server?

37. How to Concat row data through ssis?

Source:

Ename	EmpNo
stev	100
methew	100
john	101
tom	101

Target:

Ename	EmpNo
Stev methew	100
John tom	101

38. How to send Unique (Distinct) records into One target and duplicates into another tatget?

Source:

Ename	EmpNo
stev	100
Stev	100
john	101
Mathew	102

Output:

Target_1:

Ename	EmpNo
Stev	100
John	101
Mathew	102

Target_2:

Ename	EmpNo
Stev	100

38. How do u populate 1^st record to 1^st target , 2^nd record to 2^nd target ,3^rd record to 3^rd target and 4^th record to 1^st target through ssis?

39. We have a target source table containing 3 columns :

40. There is a source table that contains duplicate rows.Design a mapping to load all the unique rows in 1 target while all the duplicate rows (only 1 occurence) in another target.
41.

Design an ssis package to load first half records to 1 target while other half records to a separate target.

1. Use Of Event Handlers Based on event (On error, on Progress, On Pre/Post Validate..),

If you want to perform some action like send email, insert log info into table etc..

we can go for Event Handler Tab.

2. Diff b/w script task & script component and its advantages.

Script task is control flow level item where as script component is data flow level item, both of the

functionalities are same.

This 2 are very powerful items in SSIS.

Normally we can implement custom code by using these components.

By using this two items we can perform any action like DML operations on the data from DB, file

and we can do any operation

using c# or vb.net code we can use it as Source, destination as well. In single sentence i can

conclude the usage of this items

we can use to perform any ETL operation if you are good at .net.

3. What is a Master package.

4. Have you worked with data source view in ssis.

- You can simply say ‘i never used so far’.

5. If there are 100 Packages, do you create 100 configuration files or you create how many.

- The answer is based on the requirement we will create 100 config files for 100

packages.If i go for master package and if i am using same kind of connections for

the child packages like source and destinations connections then we will create less config files,

because Master Package will pass all the connections using master- child relation using the

variables. Creation of config files is dependence of the requirement.

6. I have excel sheet I want to send good data one table and bad data to another table can you

give me answer?

7. what is magic table in sqlserver

8. what is pivot and unpivot in sqlserver

9. How to trace errors in ssis

10. Name some Control flow tools in SSIS

12. What is difference between For Loop and For Each Loop Container?

13. What is FTP Task and how to configure it?

14. What is the use of Transfer SQL Server Objects Task?

15. Explain Lookup Transform

16. What is the difference b/w Pivot and Unpivot Transformations?

17. Difference b/w Merge and Merge Join Transformations?

18. Fuzzy Lookup

19. Fuzzy Grouping

20. What are Package Configurations and why do we need it.

21. Explain how to deploy SSIS Packages

22. What are Connection Manager and how to configure dynamic connections?

23. What is the life cycle of ETL Process?

24. What is the life cycle of ETL Project Development?

25. What is Control flow?

26. What is precedence constraint? Difference Conditions in precedence constraint?

27. What is a connection manager and types?

28. Types of containers and explain?

29. Difference between Merge and Merge Join?

30. Difference between Merger and union all?

31. Data profiling task?

32. Different ways of deployment in ssis?

Microsoft Technologies

Pages

Friday, August 21, 2015

SQL Server Integration Services Interview Questions and Answers - Part4

SSIS Interview Questions and Answers

About Me