Informatica Data Director (IDD) & Master data management(MDM)
Wednesday 20 August 2014
SSIS Interview Questions with ans
If you want to work in the database management industry, you’ll need to know SSIS. SQL Server Integration Services (SSIS) involves schedule, maintaining and transferring data on your Microsoft SQL database servers. SSIS integration replaces the old data transformation services (DTS) that would take data from one location and import it into your database.
SSIS can do much more than the old DTS application. SSIS is also faster and less buggy. DTS used older technology while SSIS is the latest in Microsoft’s data scheduling software. At a basic level, SSIS is a way for database administrators to import data or run data maintenance tasks against a database without performing these tasks manually. You don’t have to be an expert in SSIS to work with Microsoft SQL Server, but there is a very good chance that your interviewer will ask you some questions on the system for any database related job.
1) What is SSIS?
SSIS was first introduced with SQL Server 2005, which was the next generation of SQL Server software after SQL Server 2000. SSIS is a form of ETL (extraction, transformation and load), which is a database term that integrates data scheduling and tasks. The SSIS engine automation many data maintenance tasks, so you can update data without manually firing procedures and imports.
2) How is SSIS different from DTS?
Older versions of SQL Server used DTS, which was similar to SSIS. DTS let you create steps that you would then assign a priority order. With SSIS, you can separate data from work flow, and SSIS has significantly better performance than older DTS packages. While performance isn’t always an issue when running jobs during off-peak hours, it’s a problem when you must run jobs during normal business hours. You can run SSIS during business hours without too much performance degradation.
3) What is SSIS’ control flow integration?
When you create a package, you usually need some tasks to complete before you can move on to the next task. SSIS lets you control the order in which each data task is performed. This is important, because the wrong data flow can cause major issues with your data and sometimes cause severe data corruption.
4) What is data transformation?
Data transformation is a vague term, because you can pull data from any format and transform it to any other format. That is the goal of data transformation. In many database jobs, you’ll have data listed in a simple file such as a CSV or Excel file. Your job is to automatically pull data from this file and import it into your database tables. You can sometimes perform data updates and do “scrubbing” to the data to clean it up, because these flat files can contain raw data that needs to be better formatted. All of this can be accomplished using an SSIS package.
5) What can you do in an SSIS task?
A task is one step in your SSIS job. A task can be almost database transformation step. It can be connection to another database, importing data from a file or database table or running a stored procedure against your database tables. You can also customize tasks with the Microsoft .NET language, which makes SSIS a very powerful tool with your database.
6) What are the result statuses for your tasks?
There are three statuses after a task attempts to complete. The first status is “Success,” which allows the next task to perform. You set what happens after a task is successfully run. The next status is “Failure.” This status seems self explanatory, but it’s very vague. A failure can be a number of problems, and you must view your server logs and investigate the issues. A failure will stop the rest of the SSIS job from running, so you must fix the failed task to allow the rest of your transformation steps to run. It’s not always easy figuring out the problem, but you can run each task one by one to help identify the issue.
The final status is “Complete.” This status tells you if the task is completed, which means all steps within that task were fully completed.
7) What kind of containers can you use with SSIS packages?
There are three types of containers: sequence, for loops and for each loops. A sequence container is a simple way to group similar tasks together. Think of a sequence container as an organization container for more complex SSIS packages.
A for loop contain is what you can use to repeat steps a certain number of times. Perhaps you need to update records ten times. You can use this for loop container to complete a number of tasks 10 times before moving on to another step.
A for each loop can only be used in a collection. A collection is an enumerated object. For instance, you can use the for each loop to a specific number of records in a record set or a certain number of files in a directory. The for each loop is better than the standard for loop, because you don’t accidentally make a mistake of looping through numerical values too many times and causing an error in your SSIS package.
8) What kind of variables can you create?
You can create global variables and task level variables in SSIS. For programmers, these variables are the same as global and function level variables. A global variable is available to all tasks across the entire job. Variables created in tasks are only available within that task.
These are just a few questions you might be asked when you go into an SSIS job interview. Make sure you study and understand SSIS before you answer any
SSRS Interview Questions-Reporting tool
SQL Server Reporting Services (SSRS) is a product that combines the SQL Server database engine and a front end GUI that users and programmers can use to create reports. The latest version of SSRS was released with SQL Server 2012, but SSRS was also included with SQL Server 2000, 2005 and 2008. You might need to know several versions to work in some companies, but most companies stick with the same version of SQL Server for several years to avoid production problems with its database. To get started in the industry, you need to answer some technical questions regarding SSRS. Here are some questions that you might run into when you go for your next job interview.
1) Do you create your reports using the wizard or manually?
As with most Microsoft tools, SSRS includes a wizard that helps you create reports. The wizard is great for people who are new to the product, but it’s not good if you can’t go beyond the tools and create a report from scratch. Before you go for your interviews, make sure you can create a report from scratch without any wizards or tools to create them for you. Most companies need someone who can customize reports, and wizards leave room for very little customizations.
2) What are query parameters?
Query parameters are the parts of an SQL query that allow you to filter results. Parameters are contained in the SQL’s “where” clause. These parameters tell the SQL server which records you want to update, select, or delete. In other words, if it wasn’t for the where clause parameters, you would affect all records in your tables.
3) What is a sub-report?
Sub-reports are inserted into a main report. Just like a main report, you also pass parameters and queries to it. Think of a sub-report as an extension to your main report, but it contains a different data set. For instance, you could create a report of customers and then use a sub-report to display a list of orders for each customer.
4) What are RDL files?
RDL files are like the “language” for SSRS servers. However, RDL files are created in XML, so really the “language” of SSRS servers is XML. RDL files contain the queries and layout format for your reports.
5) What is a data set?
Data sets are the components that contain your records. You can have a blank data set, a data set with one record, or a data set with millions of records (although millions of records will probably cause performance issues for your users). You can view a data set like a customized table. The data set has the columns and rows like a table, but you determine the columns and number of rows using your SQL statements.
6) What is a data source?
A data source is the database and tables where your data set comes from. You must define the data source when you create your database connection. Every data set needs a source from which to pull the data. Your reporting server can be SQL Server 2000, 2005, 2008 and 2012.
7) What servers can be used with SSRS?
While most companies use SQL Server with SSRS, you can also integrate other database servers with your SSRS reports. SSRS is compatible with Oracle, ODBC and OLEDB connections, Hyperion, Teradata and flat XML files that contain data.
8) What is mixed mode database security?
When you install SQL Server, you have the option to allow SQL Server to integrate with Windows or require users to have a separate SQL Server user name and password. While Windows integration is convenient, it’s not considered the most secure of the two security options. Instead, it’s better to require a separate SQL Server user name and password when logging in to the database server. Your reports will need their own user name and password to run reports from SSRS.
9) Can SSRS reports cache results?
Caching reports makes it much faster for users to access and view data. If you have common data that doesn’t change often, it’s best to cache results. SSRS lets you cache reports on your reporting server. This means that it reduces the load on the SQL Server and your users can quickly access data without querying the server several times.
10) What formats can SSRS export or render to?
SSRS is compatible with several file formats. When the user runs the report, the default format prints to the web browser. You can also save or export to HTML, Excel, CSV, Image, PDF and XML formats. The SSRS reporting software has buttons at the top of each report that the user can use to export these files.
11) How do you deploy a report to a reporting server?
The SSRS reporting software includes tools to deploy directly to the SQL Server reporting server. After you’ve configured your software with the proper SQL Server, user name and password, you just need to right-click the report you want to deploy and click “Deploy.” Make sure you test the report after you deploy it to ensure there are no bugs and that the data returned is correct.
12) How do you edit a report?
Open SQL Server Management Studio and the reporting project. Expand the node that contains your reports. Right-click the report and click “Edit.” You can also save your reporting file from this view.
This is a dozen possible questions you could be asked in an SSRS interview, but it’s best to brush up on the tools and information you’ll need when you’re on the job. SSRS is not tough to learn, but the nervousness of an interview can affect your ability to answer questions. Make sure you practice before the interview.
Tuesday 19 August 2014
Watch youtuve for ssis
https://www.youtube.com/watch?v=3cPq9FXk-RA&list=PLNIs-AWhQzcmPg_uV2BZi_KRG4LKs6cRs
Transformations in SSIS
1. Fuzzy Grouping
2. Lookup and Fuzzy Lookup
3. Percentage Sampling[2] and Row Sampling Transformation.
4. Copy/Map, Data Conversion, and Derived Column
5. Aggregation
6. Data Mining Model Training, Data Mining Query, Partition Processing, and Dimension
7. 7Processing
8. Pivot and Unpivot
9 .Slowly Changing Dimension(ACD)
10. Script Component
11. Audit
12. Cache Transform[3]
13. Export and Import Column
14. OLE DB Command
15. Row Count
16. Term Extraction
17. Term Lookup
18. Row Sampling
19. Foreach Loop Container
20. For loop Container
The Conditional Split transformation is used to conditionally route rows to other transformation objects based on a particular condition. It is similar to the "if..else" construct in the C language.
2. Lookup and Fuzzy Lookup
3. Percentage Sampling[2] and Row Sampling Transformation.
4. Copy/Map, Data Conversion, and Derived Column
5. Aggregation
6. Data Mining Model Training, Data Mining Query, Partition Processing, and Dimension
7. 7Processing
8. Pivot and Unpivot
9 .Slowly Changing Dimension(ACD)
10. Script Component
11. Audit
12. Cache Transform[3]
13. Export and Import Column
14. OLE DB Command
15. Row Count
16. Term Extraction
17. Term Lookup
18. Row Sampling
19. Foreach Loop Container
20. For loop Container
The Conditional Split transformation is used to conditionally route rows to other transformation objects based on a particular condition. It is similar to the "if..else" construct in the C language.
Features of SSIS
*
The SSIS Import/Export Wizard lets the user create packages that move data from
a single data source to a destination with no transformations.
*
The Wizard can quickly move data from a variety of source types to a variety of
destination types, including text files and other SQL Server instances.
*
Developers tasked with creating or maintaining SSIS packages use a visual
development tool based on Microsoft
Visual Studio called the SQL Server Business Intelligence Development Studio
(BIDS).
* It allows users to edit SSIS packages using a
drag-and-drop user interface. A scripting environment in which to write
programming code is also available in the tool. A package holds a variety of
elements that define a workflow. Upon package execution, the tool provides
color-coded, real-time monitoring.
Saturday 5 July 2014
What is SSIS?
SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks.
->SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data.
->First released with Microsoft SQL Server 2005, SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0. Unlike DTS, which was included in all versions, SSIS is only available in the "Standard" and "Enterprise" editions.
Connections:-
A connection includes the information necessary to connect to a particular data source. Tasks can reference the connection by its name, allowing the details of the connection to be changed or configured at run time.
Tasks :-
A task is an atomic work unit that performs some action. There are a couple of dozen tasks that ship in the box, ranging from the file system task (which can copy or move files) to the data transformation task. The data transformation task actually copies data; it implements the ETL features of the product
Precedence constraints :-
Tasks are linked by precedence constraints. The precedence constraint preceding a particular task must be met before that task executes. The run time supports executing tasks in parallel if their precedence constraints so allow. Constraints may otherwise allow different paths of execution depending on the success or failure of other tasks. Together with the tasks, precedence constraints comprise the workflow of the package.
Event handlers :-
A workflow can be designed for a number of events in the different scopes where they might occur. In this way, tasks may be executed in response to happenings within the package —such as cleaning up after errors.
Variables :-
Tasks may reference variables to store results, make decisions, or affect their configuration.
Parameters :- (SQL Server 2012 Integration Services)
Parameters allow you to assign values to properties within packages at the time of package execution. You can project parameters and package parameters. In general, if you are deploying a package using the package deployment model, you should use configurations instead of parameters.
A package may be saved to a file or to a store with a hierarchical namespace within a SQL Server instance. In either case, the package content is persisted in XML.
Once completed, the designer also allows the user to start the package's execution. Once started, the package may be readily debugged or monitored.
->SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data.
->First released with Microsoft SQL Server 2005, SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0. Unlike DTS, which was included in all versions, SSIS is only available in the "Standard" and "Enterprise" editions.
Connections:-
A connection includes the information necessary to connect to a particular data source. Tasks can reference the connection by its name, allowing the details of the connection to be changed or configured at run time.
Tasks :-
A task is an atomic work unit that performs some action. There are a couple of dozen tasks that ship in the box, ranging from the file system task (which can copy or move files) to the data transformation task. The data transformation task actually copies data; it implements the ETL features of the product
Precedence constraints :-
Tasks are linked by precedence constraints. The precedence constraint preceding a particular task must be met before that task executes. The run time supports executing tasks in parallel if their precedence constraints so allow. Constraints may otherwise allow different paths of execution depending on the success or failure of other tasks. Together with the tasks, precedence constraints comprise the workflow of the package.
Event handlers :-
A workflow can be designed for a number of events in the different scopes where they might occur. In this way, tasks may be executed in response to happenings within the package —such as cleaning up after errors.
Variables :-
Tasks may reference variables to store results, make decisions, or affect their configuration.
Parameters :- (SQL Server 2012 Integration Services)
Parameters allow you to assign values to properties within packages at the time of package execution. You can project parameters and package parameters. In general, if you are deploying a package using the package deployment model, you should use configurations instead of parameters.
A package may be saved to a file or to a store with a hierarchical namespace within a SQL Server instance. In either case, the package content is persisted in XML.
Once completed, the designer also allows the user to start the package's execution. Once started, the package may be readily debugged or monitored.
Subscribe to:
Posts (Atom)