Q1 Explain architecture of SSIS?
SSIS architecture consists of four key parts:
a) Integration Services service: monitors running Integration Services packages and manages the storage of packages.
SSIS architecture consists of four key parts:
a) Integration Services service: monitors running Integration Services packages and manages the storage of packages.
b) Integration Services object model: includes managed
API for accessing Integration Services tools, command-line utilities, and
custom applications.
c) Integration Services runtime and run-time executables: it
saves the layout of packages, runs packages, and provides support for logging,
breakpoints, configuration, connections, and transactions. The Integration
Services run-time executables are the package, containers, tasks, and event
handlers that Integration Services includes, and custom tasks.
d) Data flow engine: provides the in-memory buffers
that move data from source to destination.
Q2 How would you do Logging in SSIS?
Logging Configuration provides an inbuilt feature which can log the detail of various events like onError, onWarning etc to the various options say a flat file, SqlServer table, XML or SQL Profiler.
Q3 How would you do Error Handling?
A SSIS package could mainly have two types of errors
a) Procedure Error: Can be handled in Control flow through the precedence control and redirecting the execution flow.
b) Data Error: is handled in DATA FLOW TASK buy redirecting the data flow using Error Output of a component.
Q4 How to pass property value at Run time? How do you implement Package Configuration?
A property value like connection string for a Connection Manager can be passed to the pkg using package configurations.Package Configuration provides different options like XML File, Environment Variables, SQL Server Table, Registry Value or Parent package variable.
Q5 How would you deploy a SSIS Package on production?
A) Through Manifest
1. Create deployment utility by setting its propery as true .
2. It will be created in the bin folder of the solution as soon as package is build.
3. Copy all the files in the utility and use manifest file to deply it on the Prod.
B) Using DtsExec.exe utility
C)Import Package directly in MSDB from SSMS by logging in Integration Services.
Q6 Difference between DTS and SSIS?
Every thing except both are product of Microsoft :-).
Q7 What are new features in SSIS 2008?
explained in other post
http://sqlserversolutions.blogspot.com/2009/01/new-improvementfeatures-in-ssis-2008.html
Q8 How would you pass a variable value to Child Package?
too big to fit here so had a write other post
http://sqlserversolutions.blogspot.com/2009/02/passing-variable-to-child-package-from.html
Q9 What is Execution Tree?
Execution trees demonstrate how package uses buffers and threads. At run time, the data flow engine breaks down Data Flow task operations into execution trees. These execution trees specify how buffers and threads are allocated in the package. Each tree creates a new buffer and may execute on a different thread. When a new buffer is created such as when a partially blocking or blocking transformation is added to the pipeline, additional memory is required to handle the data transformation and each new tree may also give you an additional worker thread.
Q10 What are the points to keep in mind for performance improvement of the package?
http://technet.microsoft.com/en-us/library/cc966529.aspx
Q11 You may get a question stating a scenario and then asking you how would you create a package for that e.g. 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?
Q13 Difference between Unionall and Merge Join?
a) Merge transformation can accept only two inputs whereas Union all can take more than two inputs
b) Data has to be sorted before Merge Transformation whereas Union all doesn't have any condition like that.
Q14 May get question regarding what X transformation do?Lookup, fuzzy lookup, fuzzy grouping transformation are my favorites.
For you.
Q15 How would you restart package from previous failure point?What are Checkpoints and how can we implement in SSIS?
When a package is configured to use checkpoints, information about package execution is written to a checkpoint file. When the failed package is rerun, the checkpoint file is used to restart the package from the point of failure. If the package runs successfully, the checkpoint file is deleted, and then re-created the next time that the package is run.
Q16 Where are SSIS package stored in the SQL Server?
MSDB.sysdtspackages90 stores the actual content and ssydtscategories, sysdtslog90, sysdtspackagefolders90, sysdtspackagelog, sysdtssteplog, and sysdtstasklog do the supporting roles.
Q17 How would you schedule a SSIS packages?
Using SQL Server Agent. Read about Scheduling a job on Sql server Agent
Q18 Difference between asynchronous and synchronous transformations?
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.
Q19 How to achieve parallelism in SSIS?
Q2 How would you do Logging in SSIS?
Logging Configuration provides an inbuilt feature which can log the detail of various events like onError, onWarning etc to the various options say a flat file, SqlServer table, XML or SQL Profiler.
Q3 How would you do Error Handling?
A SSIS package could mainly have two types of errors
a) Procedure Error: Can be handled in Control flow through the precedence control and redirecting the execution flow.
b) Data Error: is handled in DATA FLOW TASK buy redirecting the data flow using Error Output of a component.
Q4 How to pass property value at Run time? How do you implement Package Configuration?
A property value like connection string for a Connection Manager can be passed to the pkg using package configurations.Package Configuration provides different options like XML File, Environment Variables, SQL Server Table, Registry Value or Parent package variable.
Q5 How would you deploy a SSIS Package on production?
A) Through Manifest
1. Create deployment utility by setting its propery as true .
2. It will be created in the bin folder of the solution as soon as package is build.
3. Copy all the files in the utility and use manifest file to deply it on the Prod.
B) Using DtsExec.exe utility
C)Import Package directly in MSDB from SSMS by logging in Integration Services.
Q6 Difference between DTS and SSIS?
Every thing except both are product of Microsoft :-).
Q7 What are new features in SSIS 2008?
explained in other post
http://sqlserversolutions.blogspot.com/2009/01/new-improvementfeatures-in-ssis-2008.html
Q8 How would you pass a variable value to Child Package?
too big to fit here so had a write other post
http://sqlserversolutions.blogspot.com/2009/02/passing-variable-to-child-package-from.html
Q9 What is Execution Tree?
Execution trees demonstrate how package uses buffers and threads. At run time, the data flow engine breaks down Data Flow task operations into execution trees. These execution trees specify how buffers and threads are allocated in the package. Each tree creates a new buffer and may execute on a different thread. When a new buffer is created such as when a partially blocking or blocking transformation is added to the pipeline, additional memory is required to handle the data transformation and each new tree may also give you an additional worker thread.
Q10 What are the points to keep in mind for performance improvement of the package?
http://technet.microsoft.com/en-us/library/cc966529.aspx
Q11 You may get a question stating a scenario and then asking you how would you create a package for that e.g. 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?
Q13 Difference between Unionall and Merge Join?
a) Merge transformation can accept only two inputs whereas Union all can take more than two inputs
b) Data has to be sorted before Merge Transformation whereas Union all doesn't have any condition like that.
Q14 May get question regarding what X transformation do?Lookup, fuzzy lookup, fuzzy grouping transformation are my favorites.
For you.
Q15 How would you restart package from previous failure point?What are Checkpoints and how can we implement in SSIS?
When a package is configured to use checkpoints, information about package execution is written to a checkpoint file. When the failed package is rerun, the checkpoint file is used to restart the package from the point of failure. If the package runs successfully, the checkpoint file is deleted, and then re-created the next time that the package is run.
Q16 Where are SSIS package stored in the SQL Server?
MSDB.sysdtspackages90 stores the actual content and ssydtscategories, sysdtslog90, sysdtspackagefolders90, sysdtspackagelog, sysdtssteplog, and sysdtstasklog do the supporting roles.
Q17 How would you schedule a SSIS packages?
Using SQL Server Agent. Read about Scheduling a job on Sql server Agent
Q18 Difference between asynchronous and synchronous transformations?
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.
Q19 How to achieve parallelism in SSIS?
Parallelism
is achieved using MaxConcurrentExecutable property of the
package. Its default is -1 and is calculated as number of processors + 2.
-More questions added-Sept 2011
Q20 How do you do incremental load?
-More questions added-Sept 2011
Q20 How do you do incremental load?
Fastest
way to do incremental load is by using Timestamp column in source table and
then storing last ETL timestamp, In ETL process pick all the rows having
Timestamp greater than the stored Timestamp so as to pick only new and updated
records
Q21 How to handle Late Arriving Dimension or Early Arriving Facts.
Q21 How to handle Late Arriving Dimension or Early Arriving Facts.
Late arriving dimensions sometime get unavoidable 'coz delay or error in
Dimension ETL or may be due to logic of ETL. To handle Late Arriving facts, we
can create dummy Dimension with natural/business key and keep rest of the
attributes as null or default. And as soon as Actual dimension arrives,
the dummy dimension is updated with Type 1 change. These are also known as Inferred Dimensions.
Q1. WHAT is SQL Server Reporting Services(SSRS)?
SQL Server Reporting Services is a server-based reporting platform that you can use to create and manage tabular, matrix, graphical, and free-form reports that contain data from relational and multidimensional data sources. The reports that you create can be viewed and managed over a World Wide Web-based connection
Q2. Architecture of SSRS:
SQL Server Reporting Services is a server-based reporting platform that you can use to create and manage tabular, matrix, graphical, and free-form reports that contain data from relational and multidimensional data sources. The reports that you create can be viewed and managed over a World Wide Web-based connection
Q2. Architecture of SSRS:
-Admin
Q3. What are the three stages of Enterprise Reporting Life Cycle ?
a. Authoring
b. Management
c. Access and Delivery
Q4. What are the components included in SSRS?
1. A Complete set of Tools that can be used to create, manage and view reports
2. A Report Server component that hosts and processes reports in a variety of formats. Output formats include HTML, PDF, TIFF, Excel, CSV, and more.
3.An API that allows developers to integrate or extend data and report processing in custom applications, or create custom tools to build and manage reports.
Q5. What is the benefit of using embedded code in a report?
1. Reuseability of Code: function created in embedded code to perform a logic can be then used in multiple expressions
2. Centralized code: helps in better manageability of code.
Q6. Which programming language can be used to code embedded functions in SSRS?
Visual Basic .NET Code.
Q7. Important terms used in the reporting services?
1. Report definition: The blueprint for a report before the report is processed or rendered. A report definition contains information about the query and layout for the report.
2. Report snapshot: A report that contains data captured at a specific point in time. A report snapshot is actually a report definition that contains a dataset instead of query instructions.
3. Rendered report: A fully processed report that contains both data and layout information, in a format suitable for viewing (such as HTML).
4. Parameterized report: A published report that accepts input values through parameters.
5. Shared data source: A predefined, standalone item that contains data source connection information.
6. Shared schedule: A predefined, standalone item that contains schedule information.
7. Report-specific data source: Data source information that is defined within a report definition.
8. Report model: A semantic description of business data, used for ac hoc reports created in Report Builder.
9. Linked report: A report that derives its definition through a link to another report.
10. Report server administrator: This term is used in the documentation to describe a user with elevated privileges who can access all settings and content of a report server. If you are using the default roles, a report server administrator is typically a user who is assigned to both the Content Manager role and the System Administrator role. Local administrators can have elevated permission even if role assignments are not defined for them.
11. Folder hierarchy: A bounded namespace that uniquely identifies all reports, folders, report models, shared data source items, and resources that are stored in and managed by a report server.
12. Report Server: Describes the Report Server component, which provides data and report processing, and report delivery. The Report Server component includes several subcomponents that perform specific functions.
13. Report Manager: Describes the Web application tool used to access and manage the contents of a report server database.
14. Report Builder: Report authoring tool used to create ad hoc reports.
15. Report Designer: Report creation tool included with Reporting Services.
16. Model Designer: Report model creation tool used to build models for ad hoc reporting.
17. Report Server Command Prompt Utilities: Command line utilities that you can use to administer a report server.
a) RsConfig.exe, b) RsKeymgmt.exe, c) Rs.exe
Q8. what are the Command Line Utilities available In Reporting Services?
· Rsconfig Utility (Rsconfig.exe): encrypts and stores connection and account values in the RSReportServer.config file. Encrypted values include report server database connection information and account values used for unattended report processing
· RsKeymgmt Utility: Extracts, restores, creates, and deletes the symmetric key used to protect sensitive report server data against unauthorized access
· RS Utility: this utility is mainly used to automate report server deployment and administration tasks.Processes script you provide in an input file.
Q. How to know Report Execution History?
ExecutionLog table in ReportServer database store all the logs from last two months.
SELECT * FROM ReportServer.dbo.ExecutionLog
-Development
Q. What is difference between Tablular and Matrix report?
OR What are the different styles of reports?
Tablular report: A tabular report is the most basic type of report. Each column corresponds to a column selected from the database.
Matrix report: A matrix (cross-product) report is a cross-tabulation of four groups of data:
a. One group of data is displayed across the page.
b. One group of data is displayed down the page.
c. One group of data is the cross-product, which determines all possible locations where the across and down data relate and places a cell in those locations.
d. One group of data is displayed as the "filler" of the cells.
Martix reports can be considered more of a Pivot table.
Q. How to create Drill-through reports?
Using Navigation property of a cell and setting child report and its parameters in it.
Q. How to create Drill-Down reports?
To cut the story short:
- By grouping data on required fields
Q3. What are the three stages of Enterprise Reporting Life Cycle ?
a. Authoring
b. Management
c. Access and Delivery
Q4. What are the components included in SSRS?
1. A Complete set of Tools that can be used to create, manage and view reports
2. A Report Server component that hosts and processes reports in a variety of formats. Output formats include HTML, PDF, TIFF, Excel, CSV, and more.
3.An API that allows developers to integrate or extend data and report processing in custom applications, or create custom tools to build and manage reports.
Q5. What is the benefit of using embedded code in a report?
1. Reuseability of Code: function created in embedded code to perform a logic can be then used in multiple expressions
2. Centralized code: helps in better manageability of code.
Q6. Which programming language can be used to code embedded functions in SSRS?
Visual Basic .NET Code.
Q7. Important terms used in the reporting services?
1. Report definition: The blueprint for a report before the report is processed or rendered. A report definition contains information about the query and layout for the report.
2. Report snapshot: A report that contains data captured at a specific point in time. A report snapshot is actually a report definition that contains a dataset instead of query instructions.
3. Rendered report: A fully processed report that contains both data and layout information, in a format suitable for viewing (such as HTML).
4. Parameterized report: A published report that accepts input values through parameters.
5. Shared data source: A predefined, standalone item that contains data source connection information.
6. Shared schedule: A predefined, standalone item that contains schedule information.
7. Report-specific data source: Data source information that is defined within a report definition.
8. Report model: A semantic description of business data, used for ac hoc reports created in Report Builder.
9. Linked report: A report that derives its definition through a link to another report.
10. Report server administrator: This term is used in the documentation to describe a user with elevated privileges who can access all settings and content of a report server. If you are using the default roles, a report server administrator is typically a user who is assigned to both the Content Manager role and the System Administrator role. Local administrators can have elevated permission even if role assignments are not defined for them.
11. Folder hierarchy: A bounded namespace that uniquely identifies all reports, folders, report models, shared data source items, and resources that are stored in and managed by a report server.
12. Report Server: Describes the Report Server component, which provides data and report processing, and report delivery. The Report Server component includes several subcomponents that perform specific functions.
13. Report Manager: Describes the Web application tool used to access and manage the contents of a report server database.
14. Report Builder: Report authoring tool used to create ad hoc reports.
15. Report Designer: Report creation tool included with Reporting Services.
16. Model Designer: Report model creation tool used to build models for ad hoc reporting.
17. Report Server Command Prompt Utilities: Command line utilities that you can use to administer a report server.
a) RsConfig.exe, b) RsKeymgmt.exe, c) Rs.exe
Q8. what are the Command Line Utilities available In Reporting Services?
· Rsconfig Utility (Rsconfig.exe): encrypts and stores connection and account values in the RSReportServer.config file. Encrypted values include report server database connection information and account values used for unattended report processing
· RsKeymgmt Utility: Extracts, restores, creates, and deletes the symmetric key used to protect sensitive report server data against unauthorized access
· RS Utility: this utility is mainly used to automate report server deployment and administration tasks.Processes script you provide in an input file.
Q. How to know Report Execution History?
ExecutionLog table in ReportServer database store all the logs from last two months.
SELECT * FROM ReportServer.dbo.ExecutionLog
-Development
Q. What is difference between Tablular and Matrix report?
OR What are the different styles of reports?
Tablular report: A tabular report is the most basic type of report. Each column corresponds to a column selected from the database.
Matrix report: A matrix (cross-product) report is a cross-tabulation of four groups of data:
a. One group of data is displayed across the page.
b. One group of data is displayed down the page.
c. One group of data is the cross-product, which determines all possible locations where the across and down data relate and places a cell in those locations.
d. One group of data is displayed as the "filler" of the cells.
Martix reports can be considered more of a Pivot table.
Q. How to create Drill-through reports?
Using Navigation property of a cell and setting child report and its parameters in it.
Q. How to create Drill-Down reports?
To cut the story short:
- By grouping data on required fields
-Then toggle visibility based on the grouped filed
No comments:
Post a Comment