Tuesday, October 29, 2013

SSIS Useful Links

SSIS

http://weblogs.sqlteam.com/dmauri/category/328.aspx
http://learnsqlwithbru.com/2009/11/26/configure-logging-in-ssis-packages/
http://www.f5debug.net/
http://www.bimonkey.com/2009/09/the-import-column-transformation/
http://sqlage.blogspot.in/search/label/SSIS%20INTERVIEW%20QUESTIONS
http://www.jasonstrate.com/tag/31daysssis/
http://www.allaboutmssql.com/
http://sqlage.blogspot.in/
http://www.sqlis.com/sqlis/ (for extra SSIS components like Row number transformation, Regular expression transformation)



SSAS

http://www.mssqltips.com/sqlservertutorial/2000/sql-server-analysis-services-ssas/
https://mis.uhcl.edu/rob/Course/DW/Resources/SQL-ServerAnalysisServices2008.pdf

General
http://www.dotnet-tricks.com/Tutorial/sqlserver/b4I8120313-Calculate-Running-Total,-Total-of-a-Column-and-Row.html
http://www.sql-server-performance.com/
http://www.databasejournal.com/features/article.php/3593466/MS-SQL-Series.htm


Posted by at No comments:

Monday, October 28, 2013

SSIS - How to configure a Foreach Loop Container: File Enumerator

http://microsoft-ssis.blogspot.in/2011/02/how-to-configure-foreach-loop-file.html

How to configure a Foreach Loop Container? The Foreach Loop Container is a repeating container that loops through a collection. These are the collections:

This article explains the File Enumerator. The others will follow in the coming time.

Foreach File Enumerator
The file enumerator loops through a collection of files within a folder and makes it possible to execute for example a Data Flow Task foreach of the files without manualy changing the connection string.

1) Create variabel
We need a variable to store the filepath in. Create a string variable with the name FilePath.

Variabel










Note: I addad a value (the filepath of one of the sourcefiles) to show in the expression later on. It's optional.

2) Foreach Loop
Add a Foreach Loop Container to the Control Flow and give it a suitable name. Go to the Collection tab and:
A) Select Foreach File Enumerator. You have to selected it although it is already selected by default, but that's a little bug in the user interface. They eventually solved it in SQL 2012.
B) Select the folder where your sourcefiles are and use wildcard characters to specify the files to include in the collection. There is one little strange behaviour here that could cause unexpected results: *.xls will also return files with the extension .xlsx (it's simulair to a dos command dir *.xls).
C) Choose Fully qualified because that returns the complete filepath and not only the name and extension.

Configure Foreach Loop Container




















3) Variable mapping
Go to the Vaiable Mappings tab. Now we are gonna use the variable from step 1 and fill it with the filepath from the collection.

Map variable to Index 0 of the collection



















4) Dataflow
The loop is now ready. Add a Data Flow Task to the loop and create a simple dataflow that reads one of your source files and add the data to a database table. (Flat File Source/Connection Manager can be replaced by an Excel Source/Connection Manager)

Add Data Flow Task. => Add Source & Destination




















5) Connection Manager Expression
The Flat File Connection Manager YourSourceFile from the previous step is still hardcoded with the path of one of your sourcefiles and the variable FilePath contains the filepath from the Foreach Loop.

To replace this hardcoded path with the value of the variable we use an expression on the Connection Manager. Goto the properties of the FlatFile Connection Manager and add a new expression.

Add an expression






















Note: This step is the same if you use an Excel Connection Manager.


6) Expression
Select Connection String in the Expression Editor and press the button to edit the expression. In the Expression Builder drag the variable FilePath to the textbox.


Build the expression. Use Evaluate to test the expression.



















Note: If you used an Excel Connection Manager, then don't add an expression on the Connection String, but on ExcelFilePath. The rest is the same.


7) Testing
Now your Foreach Loop is ready for testing, but you could also add a File System Task to move the imported files to an archive folder or add a Script Task that archivces the source files in a zip file.

File System Task





















Note: the File Enumerator only loops through files. It doesn't return folders. Read this arcticle for a Foreach Folder Enumerator.
Note: the File Enumerator can't be sorted. Read this article for a sorted File Enumerator
Posted by at No comments:

Wednesday, October 23, 2013

How to check Shrink Process Progress

SELECT
 percent_complete,
 start_time,
 status,
 command,
 estimated_completion_time,
 cpu_time,
 total_elapsed_time
FROM
 sys.dm_exec_requests
WHERE
 command = 'DbccFilesCompact'
Posted by at No comments:

SQL SERVER – Find First Non-Numeric Character from String - PATINDEX

SQL SERVER – Find First Non-Numeric Character from String

http://blog.sqlauthority.com/2012/10/14/sql-server-find-first-non-numeric-character-from-string/

It is fun when you have to deal with simple problems and there are no out of the box solution. I am sure there are many cases when we needed the first non-numeric character from the string but there is no function available to identify that right away. Here is the quick script I wrote down using PATINDEX. The function PATINDEX exists for quite a long time in SQL Server but I hardly see it being used. Well, at least I use it and I am comfortable using it. Here is a simple script which I use when I have to identify first non-numeric character.
-- How to find first non numberic characterUSE tempdb
GO
CREATE TABLE MyTable (ID INT, Col1 VARCHAR(100))GO
INSERT INTO MyTable (ID, Col1)SELECT 1, '1one'UNION ALLSELECT 2, '11eleven'UNION ALLSELECT 3, '2two'UNION ALLSELECT 4, '22twentytwo'UNION ALLSELECT 5, '111oneeleven'GO
-- Use of PATINDEXSELECT PATINDEX('%[^0-9]%',Col1) 'Position of NonNumeric Character',SUBSTRING(Col1,PATINDEX('%[^0-9]%',Col1),1) 'NonNumeric Character',Col1 'Original Character'FROM MyTable
GO
DROP TABLE MyTable
GO
Here is the resultset:
Where do I use in the real world – well there are lots of examples. In one of the future blog posts I will cover that as well. Meanwhile, do you have any better way to achieve the same. Do share it here. I will write a follow up blog post with due credit to you.
Posted by at No comments:

Friday, October 4, 2013

TSQL INTERVIEW QUESTIONS [Database Interview Questions]

http://sqlage.blogspot.in/search/label/TSQL%20INTERVIEW%20QUESTIONS

Here are TSQL Interview Questions and Database Interview questions. I am going to do my best to provide a good list of real time questions.  I am not going to categories the questions depending upon topics but rather have them one after one as you never know what interviewer is going to ask you next ;). All the best with Database developer interview!

1.         What is Heap?
2.         What are DMVs in SQL Server and name two of them that will you use to find out long running queries or stored procedures?
3.         If you need to find out the last time the job was run, which database will help you to find that information?
4.         If I want you to provide me the list of all SQL Server Agent jobs with Schedules which database and tables will you use?
5.         What are 2 differences between Delete and Truncate?
6.         Let me know at least five differences between a stored procedure and a function?
7.         What is parameter sniffing in Stored Procedure, how you will overcome that?
8.         Can you create a function and have dynamic SQL in it?
9.         What are CROSS APPLY and OUTER APPLY in SQL Server and why we need to use it?
10.     What are window functions in TSQL, IN SQL 2012 new window functions are added such as FIRST_VALUE, LAST_VALUE, LEAD, LAG, Explain the definition with scenario where you will use these?
11.     There is duplicate data in table, Write three different queries to find out those duplicate records?
12.     What is difference between ISNULL ( ) AND COALESCE ( ), which one you prefer and why?
13.     I heard indexes are good, Can I create cluster index on multiple columns or cluster index can be created only on one column?
14.     I need to create non-cluster index on 20 columns, data type of each column is Char (7000), Can I include all of the columns in non-cluster index? What is max number of columns I can include in non-cluster index, what other options I have if there is limit on number of columns?
15.     One of our developer has created 5 functions. Whenever new database is created these functions should be created in it without running creates statements for these functions every time after create database. What are my options?
16.     I have two tables with same columns, if I want to find out all records those are in Table A and not in Table B ,  Let me know the at least two queries How I can do that?
17.     I want the list of all tables with column name with data type name, which system table I can use to get that information.
18.     In one of our database, we have 100 stored procedures, each stored procedure has comments. I want to find if comment are something like this ‘Creator Name=Aamir’ , What options I have?
19.     If you need to script a table with data, how will you do that?
20.     A big update is happening on one of the table, you are still interested to read the data even update is running, what will be your Select query?
21.     How will you find blocking processes?
22.     You have ten tables in database and all have cluster indexes on them, If I ask you to give me the total row number in each table without using count(*) from table, which table can help you out without using count(*)?
23.     If we want to put table name and total record count in each of the table in database, what will be your approach?
24.     I have 100 SQL Server agent jobs, each job has different TSQL Statements , how can you find a job that is having this statement ‘ Select count(*) from dbo.test’ ?
25.     I want to audit information such as who created table, who has dropped table, what are my options?
26.     I have a Audit Table that has couple of columns, If I want to send email evey morning with all these records in email, how will you be able to do that in SQL Server?
27.     We have an application that is running on SQL Server 2005. Our company has decided to have no more SQL server 2005 but only SQL Server 2012. We have migrated the Database to SQL Server 2012. How I will set the compatibility to SQL Server 2005 so application can run without any issues?
28.     When I run this query I receive no records ( Select * From dbo.TEST) but when I run (Select * from dbo.test) , I get 10 records. In both queries table name is Test. Which database property is playing role here and how we can address that to return records in any case?
29.     If I run Select 100/11 what out I will get?
30.     How do you handle exceptions in TSQL?
31.     List at least 5 system functions those you have used or will use to Retrieve Error information
32.     What is uncommittable state?
33.     Let’s say you have found bunch of duplicate records in table, we want to keep only one record for each of duplicate set, How will you do that?
34.     What are the things you will look into when you do performance tuning of TSQL query?
35.     What is difference between Rollup and Cube, What are alternative of these in SQL Server 2012?
36.     I have sale column in one of the table, Explain two different queries those you can use to have running total in your final output?
37.     I have two tables with same column and data types. If I need to get distinct records from both of tables how I can do that?
38.     Have you heard Wait for, Break, Continue key words in TSQL , explain about each of them?
39.     If you have create a table with identity (1, 1), is that possible that sequence can break and if yes then in which situation that happen?
40.     What are common table expressions (CTE), what is Recursive CTE, in which situation you have used it? If you want to limit recursion how you can do that in CTE?
41.     What type of parameters you can provide to stored procedure? Explain where did you used them and why?
42.     What string functions you have used, if you need to find the starting position of character or string which function you will use?
43.     What is the difference between LEN() AND LENGTH( ) Function?
44.     What is the difference between ISNULL () AND NULLIF() Functions?
45.     Can you insert ordered data into SQL Server table and will SQL Server store data in ordered you have inserted? And if you will write Select Query to extract that data, will you retrieve in order the way you have inserted or not?
46.     Explain a scenario where you needed to write Correlated subquery?
47.     When do you like to create filter index, do they take less or more space than cluster index on Disk? What are advantages of using Filter index?
48.     Sometime when you run your query you receive this warning with result sets as well “Warning: Null value is eliminated by an aggregate or other SET operation”. How can you avoid this?
49.     If you need to find the difference in days between two dates which function you will use?
50.     Can you use having clause without group by?
51.     Let’s say you have tableA on SERVERA and ServerB. If you want to see all the records from TableA on both servers, how you will be able to do that?
52.     There is Excel file siting on your Desktop. You want to write a query to read that file and see the records in SSMS. How will you do that in TSQL?
53.     You have few word documents those you want to save in SQL Table, What data type will you use to save the word document in SQL Table?
54.     Let’s say you have used dynamic SQL, you have created a temp table inside Dynamic SQL , Will you be able to read data from that temp table after execution of dynamic SQL?
55.     What keyword will you use to retrieve unique rows from table?
56.     Can I execute stored procedure inside Function?
57.     What is for xml, in which Scenario you have used?
58.     Can you use Case Statement in Order by Clause?
59.     Which data type will you use which should be time zone aware?
60.     What is Sparse Column?
61.     What are different ways to run dynamic SQL; if I need to pass parameter to dynamic SQL How I can do that?
62.     Cross join gives you Cartesian product, where have you used Cross Join?
63.     If I need to add “Total” Line at the end of result set, what option do I have?
64.     Can we send emails in HTML Format from SQL Server, How will you do that?
65.     You have built a big dynamic SQL query. The total length of this built query is more than 20,000 characters, now you want to see if your built query looks good, how will you print that query?
66.     How will you transpose column into row by using TSQL?
67.     Can you execute stored procedure in View?
68.     Can you declare variable in Create View Statement if not then what object you prefer to create so can us in Select statement like view?
69.     What is Self-Join; explain a scenario where you need to use that?
70.     What are the steps to create partitioned table, what are benefits of creating it?
71.     What is Switch Operator in TSQL?
72.     If you need to get month name which TSQL Function you will use?
73.     If you need to get Year which function you will use?
74.     If you need to extract data from Oracle table and join with SQL table, what steps are required, explain sample query as well?
75.     What new features are available in SSMS those were not in previous versions? Any third party tools those can be add in to SSMS and are helpful for daily use?
76.     Can you save query results to flat file from SSMS?
77.     What are partitioned views and where we need to create these views?
78.     To connect to SQL Azure, do you need any different tool or you can use SSMS?
79.     What is Key Lookup operator (bookmark lookup) and how you can remove that?
80.     What is estimated Execution plan?  How we can display execution plan in text?
81.     What is plan cache and how does it relate to Execution plan?
82.     Which is better Index Scan or Index Seek? In which scenario Scan is better than seek?
83.     What type of Trigger can be created on Views?
84.     What are different ways available to set value to Variable?
85.     What is @@RowCount?
86.     What is computed column and have you used it, Is there any disadvantages of using computed column?
87.     Is ORDER BY clause valid in views, inline functions, derived tables, subqueries, and common table expressions?
88.     I want to create view with order by clause by any means, how I can do that?
89.     Which function we can use to concatenate strings in SQL Server 2012?
90.     How many different values I can store in bit type data type?
91.     When you should run UPDATE STATISTICS?
92.     To see the total space taken by database which query you will run?
93.     What is data compression, what level of compression is available?  Should we enable on OLTP tables or DWH Tables to get benefit of this feature?
94.     What is CDC, in which version it was introduced? To Enable CDC on table, do you have to enable on Database?
95.     Can you enable CDC on single column or you have to enable on all the columns in table?
96.     If you drop Column from Source on which CDC was enabled, will it be dropped from CDC table as well?
97.     If you include a new column in Source, Will it be automatically included in CDC?
98.     If you need to find out, CDC is enabled on Database, how will you do that?
99.     What is difference between Left Join and Inner Join?
100. What are SQL Constraints and which one you have used often? Explain with scenarios where do you need them?
101. Can we create triggers on temp table and system tables?
102. If you need to save more than two 2 GB file in SQL Table, which data type will you choose?
103. What is the lowest level of transaction lock Table, Row or Field?
104. Can we create unique constraint on more than one columns?
105. Can we create more than one unique constraint per table?
106. Can you use these functions in SQL Server and what they will return {fn NOW()},{fn CURRENT_TIMESTAMP()}. In TSQL they are equivalent to which functions?
107. What is difference between Convert () and format () function?
108. Can you create Primary key constraint on a column that has one null value?
109. Can you create Unique Constraint on a column that has one null value?
110. We can create only one cluster index per table, Is this statement true?
111. What is the difference between a trigger and stored procedure?
112. What are Deleted and Inserted tables in SQL Server? Can you update these tables?
113. I want to create only a table with one column and that should be identity (1,1) . After creating this table. I want to populate this table from 1 to 100000, what will be my script?
114. There are some missing values in our identity column, if I want to fill those gaps which strategy I should use?
115.  I have created a table with Identity (1, 1) and it has no record at the moment. If I want to insert a record with Id=100, How can I do that?
116. I have a table that has 300 million records; I need to delete 100 million records from this table, Explain different approaches those can be used?
117. What are different types of cursors available in SQL Server? Which one you use often?
118. If you do not want to write your code by using cursor, what alternatives you have in TSQL?
119. Define the steps in TSQL cursor?
120. What is Columnstore Index? Should be consider using Columnstore indexes in dataware house or in OLTP database?
121. To generate manual checkpoint which TSQL statement will you use?
122. You are doing performance tuning on your query, Can you display  Estimated Execution plan and Actual Execution plan together?
123. Can estimated execution plan be different from Actual execution plan?  And if yes what is the reason behind that?
124. What is difference between DMV and DMF?
125. Which system table or DMV you can use to find out all the tables those do not have cluster index?
126. What are benefits of using sp_executesql over EXEC?
127. What is federated database?
128. Can I update the column on which I have created columnstore index?
129. What is sql variant data type?

130.  Can you use ranking functions without using Order by clause?
Posted by at 1 comment:

SSIS Interview Questions

http://sqlage.blogspot.in/search/label/SSIS%20INTERVIEW%20QUESTIONS

Here are some SSIS interview questions those you can expect as ETL Developer. I will be working on below list to add more questions and different small scenarios. Good luck with yourSQL Server Integration Services interview:)

General

  1. What is ETL
  2. What are important components of ETL Tool
  3. Which ETL Tool you have used as ETL Developer
  4. Which version of SSIS you have worked
  5. What is DTS and how it is different from SSIS
  6. What are few different features you have in SSIS 2008 and those were not available in SSIS 2005
  7. What features are new in SSIS 2012 those were not in SSIS 2008
  8. What is difference between Control  Flow and Data Flow in SSIS
  9. What is parallel execution in SSIS and how many Data Flow Tasks a package can run in parallel
  10. What is MaxConcurrentExecutables  property on Package level
  11. What is Engine Thread property of Data Flow
  12. What is Precedence Constraints in SSIS and where you have used them and why
  13. What is difference between Success and Completion value of Precedence Constraint
  14. What is DelayValidation properties of Data Flow Task , Why we need to use this property
  15. What is RetainSameConnection Property on Connection, Why we use that
  16. If we create temp table in SSIS Package and want to use further in other Tasks, which properties we need to set to be able to use temp table.
  17. What is data Viewer in SSIS, Is data viewer available in Control Flow or Data Flow
  18. I am running my package for debugging, I do not want to load data into any destination, which transformation I can use for test purpose as destination without writing data to anywhere
  19. What is difference between checkpoint and breakpoint in SSIS
  20. Will my package run successfully by using SQL Server Agent if I have data viewers enabled and breakPoint
  21. What are different ways to execute your SSIS Package, Can I run SSIS Package by using a Stored Procedure
  22. What type of deployment available for SSIS Package, explain all
  23. What is difference between Package deployment and Project Deployment
  24. Which version of SSIS track versions of SSIS Package deployed to Server
  25. To run your SSIS Package, the Integration Services Server and SQL Server should be installed on same server, Is this correct statement?
  26. What are different ways to run your package on schedule
  27. What are Event Handlers in SSIS , On which Events you can run the event handler part, Name few of the Events available
  28. Let’s say you have configured Event handler to send an email on error for data flow inside For each Loop, If error occurred in data flow you will get multiple emails, why is that? How we can handle those series or email coming for one error
  29. How do you debug your package
  30. Explain important steps for  performance tuning of your SSIS Package
  31. If your package is scheduled to run every night at 10 PM. If package fails in production , where will you start to look for error
  32. You have created a variable in SSIS, You used that variable in Data Flow task in Row Count transformation, if you want to display or see the value of variable after Data Flow execution how will you do that
  33. You are looking at Control Flow Item and you cannot find Execute SQL task, how will you bring it back to Control Flow Items.
  34. Your company is using some third party transformations and task, you need to add them to Control Flow Item and Data Flow Items , how will you do that
  35. What are Attunity Drivers and why do we use them with SSIS
  36. If you do not want to use Slowly Changing Transformation, Which T-SQL statement can help you to load SCD type table in one statement( insert, update, delete)
  37. What is Annotation, Is it only available in Control Flow Pane or available in other Panes as well
  38. Let’s say we have two Execute SQL Tasks in Sequence container, If any of them fail we want to roll back transaction. What setting we need to configure to achieve this.
  39. What is TransactionOption property at Package level, Is this property only available on package level or It is also available on Container level,Task level
  40. Can you create SSIS Package without using BIDS
  41. You have excel file and you want to clear first cell of sheet1, which task you will use to do that
  42. A third party software is available that you need to execute by using SSIS, which task can be used to run .EXE file
  43. You need to load 500 million records in table; Table has cluster and non-cluster indexes already created. What will be your approach to load data
  44. What are Asynchronous and Synchronous data flow components
  45. What are Non-blocking, Semi-blocking and Fully-blocking components in SSIS?
  46. What is Balanced Data Distributor( BDD)
  47. What is Error Output? Can you redirect rows from Sources, Transformations and Destinations in SSIS?
  48. If you need to check If File exists in Folder which Tasks you will use to find out that in SSIS
  49. Let’s way you have created an Excel File by using Excel Destination. If you have to make the Header row bold, how you will do that in SSIS?
  50. If you need to send HTML email, Can you use built in Send Mail Task? What other options you have? 
  51. If you are using SQL Server Log Provider, Can you make the SQL Server connection dynamic?
  52. If you need to watch a directory for a specific file to be added which Task you will use?
  53. What is For-each Item Enumerator in For-each loop and where would you need to use this?
  54. If you need to move a file and rename as the same time how will you do that?
  55. Which Package property will help you to encrypt packaged to restrict access to the contents of a package?

Sources

1.       Name few of the Sources available in SSIS
2.       If we need to read XML Source file which Source we will use in SSIS
3.       What problems you have faced when you used Excel Source
4.       Why we add IMEX=1 to extended properties of excel connection manager
5.       If one of the excel column has alphanumeric data and other developer has changed the registry setting TypeGuessRow=0what does that mean to you
6.       How will you handle a column such as comments that has more than 255 characters in excel at row number 1000.
7.       What is fast parse property in Flat File Source? What are the requirements and limitation to use this property
8.       Can I read csv file with Raw File Source, if not then what type of files I can read by using Raw File Source
9.       Can we use Stored procedure in OLE DB Source, if yes how do you map the parameters


Transformations

1.       What is a SSIS Transformation
2.       If you need to get Username, Package Name, Package Start Time which transformation you will use
3.       Which Transformation can perform operations such as Sum, count, Group by
4.       If you need to convert Data Type from String to Integer which transformation you will use
5.       If you need to Add new column which transformation you will use
6.       Let’s say you have column Name, some of the record for Name column are null, if you want to replace Null with “Unknown” which transformation you will use
7.       What is syntax for writing IF ELSE in Derived column Transformation
8.       What is lookup transformation and why we use this transformation when we load Fact Table
9.       What are three modes of Lookup transformation and what criteria should be used to choose
10.   Can we insert record in Lookup Table by using Lookup Transformation
11.   Should you use drop down for Table choose in Lookup and how good/bad it is for performance
12.   What is multicast Transformation in SSIS
13.   What is conditional Split Transformation in SSIS
14.   What is major difference between Conditional Split and Multicast
15.   What is OLE DB Command Transformation, Where you use it and what are disadvantages/Advantages of using OLE DB Command Transformation
16.   What alternative methods you would like to use for OLE DB Command Transformation
17.   If you need to get the Row Count for record those loaded from Source to Destination, which transformation you will use.
18.   If you need to create sequence number with input records which transformation you will use
19.   Which transformation can be used as Source, Destination Or transformation
20.   What is Slowly Changing Dimension and which transformation can help you to load those tables
21.   How will you load SCD1 type table by using SSIS
22.   How will you load SCD2 type Table by using SSIS
23.   What are the best practices when you load huge SCD type table
24.   Which transformation require us to use SORT Transformation with it
25.   What are the alternatives of SORT Transformation if our source is SQL Server and we need to use Merge Join for multiple source
26.   What is IsSorted Property and why we use it
27.   How will you remove duplicate records in SSIS, which transformation can help with this task
28.   Let’s say if we have some reference data in Excel and we want to use that excel Data in Lookup Transformation , how we can achieve that without loading into staging or temp table
29.   What is Cache Transformation and which transformation can use Cache Transformation loaded data
30.   Why we need to use Cache Transformation
31.   Which transformation can be used to change column data to Lower case, Upper case
32.   What is difference between Copy Column Transformation and Derived Column Transformation
33.   Let’s say my table contains images, I am reading data from table in Data Flow task, Which transformation can help me to save those images to files
34.   Which transformation I can use in SSIS to import image files to Table
35.   I have a source file that contains 1000 records, I want to insert 15% records in TableA and remaining in TableB which transformation I can use
36.   What is difference between Row Sampling and Percent Sampling transformations
37.   Which transformation can be used to extract nouns only, noun phrases only, or both nouns and noun phases from text input column
38.   How different is Term Lookup from Lookup Transformation
39.   To Pivot or Unpivot input data which transformations are available in SSIS
40.   There is no Union Transformation in SSIS , How to perform UNION operation by using built-in Transformation
41.   If we have source S1 and Source S2, we need to merge them and want to get sorted output , which transformation we can use
42.   What is difference between Merge and Union All Transformation
43.   In Merge Join Transformation, I see we can use Inner Join, Left Join and Full Outer Join, What If I have to use Cross Join which transformation I can use to perform that
44.   What is FindString function in Derived column
45.   Do we have MonthName and DayName functions available in SSIS so we can use them in expression, in Derived Column Transformation?

Destination

1.       Raw File Destination , where and why we use it
2.       What is difference between OLE DB Destination and SQL Server Destination
3.       What is Recordset Destination and where we need that to use
4.       Can we redirect rows from OLE DB Destination, if yes what are the important things need to remember while we configure that
5.       What does “Keep Identity”,”Keep Nulls”,”Table lock” and “Check Constraints” Check box means on OLE DB Destination
6.       What does Rows Per batch and Maximum insert commit Size mean to you in OLE DB Destination, how we use them and why we need to change values from default
7.       If there is trigger on table and we are loading data in that table by using SSIS, Access Mode properties is set to “ Table or View-fast Load”  , Will trigger fire? If not then what Mode(
Table or View,
Table or View-Fast Load,
Table Name or view name variable,
Table name or view name variable –fast load,
SQL Command) we can choose to load data as well trigger get fired

Variable and Expressions

1.       What is variable in SSIS, What data types are available if we can declare a variable in SSIS
2.       What is Scope of Variable, How we can change Scope of variable in SSIS 2008 and SSIS 2012
3.       What are expressions in SSIS, Where and Why we need to use them
4.       Can we write expression on Variable? If yes how and where you write them
5.       Can we write expression on Connection Managers, If yes where did you use them and why
6.       What are system variable in SSIS, Name few of them you have used
7.       Can we write expression on Precedence Constraint, if yes how you do it
8.       What is the maximum length of expressions in SSIS 2008 and SSIS 2012
9.       What is new Expression Task in SSIS 2012
10.   What are Parameters in SSIS 2012, how are they different from Variable


Logging

1.       What is logging in SSIS , How many type of logging available in SSIS, Which one you have used
2.       If you need to create a text type log file with timestamp, how you will do that
3.       If you use SQL Server Logging, which table will be used to store log information
4.       What are the few column names that sysssislog have to store log information
5.       Do you log all the events or prefer to choose few of them?
6.       Name few of Events you like to use in logging
7.       What is custom Logging and how it is different from built in Logging
8.       If you use timestamp in your text file logging , it creates multiple log files with each execution, how to avoid that and why single execution create more than one file
9.       If we are using SQL Server Logging, how often the records got deleted from syssislog table or we have to create some purge process

Configuration

1.       What is Configuration in SSIS
2.       Why we even need configuration
3.       What configuration types are available for us to use
4.       What is difference between Direct and Indirect Configuration
5.       If we use SQL Server Configuration, what table is created by configuration Wizard
6.       How many columns configuration table do have, which one are important for us
7.       For indirect configuration, do you create user variable or system variable to hold connection string?
8.       What is parent child configuration and where we need to use that
9.       Explain best practices for Integration Services Configuration
10.   Let’s say we have SQL Server Connection Name ‘MyConnection’,  when you will enable configuration which properties of  Connection manager you will choose (Description,EvaluateAsExpression,Expression,IncludeInDebug,Name,NameSpace,RaiseChangedEvent,ReadOnly,Value)


Control Flow Task

1.       What is Data Flow Task
2.       If you need to run some SQL script( stored procedure, DML,DDL ) which task you will use in Control Flow
3.       If you have create object type variable that you want to use in script task later, how you will load data into Object Type variable in Control flow, which task will help
4.       If your database in Full Recovery mode cans you use Bulk Insert Task to load data? What are requirements to use Bulk Insert Task
5.       If your company is using Ftp site to receive daily data files, which task you will use to download/upload/Delete files on FTP Site
6.       What is difference between Script task and Script component
7.       What script languages are available for you to do scripting in Script task and script component
8.       What is difference between ReadOnlyVariables and ReadWriteVariable in Script task
9.       Can you do debugging in Script task or Script component in SSIS 2012
10.   You have create 5 packages, you want to call all of them in one package, which task you can use to call them
11.   You have deployed your SSIS Package to Integration Services Server, Can you still call them in Parent Package
12.   If you load set of big files, after each load you want to zip them and put into archive folder. Which task you need to use to Zip them
13.   Which task you can use to delete, rename, move files and folders
14.   Can you rename and move file by using one task, which task is that and how you will do that
15.   If your data flow task fails in Control Flow, which task you will use to send email on Error
16.   Before you create your SSIS Package and load data into destination, you want to analyze your data , which task will help you to achieve that
17.   What is WSDL and in which task you have to use it
18.   You have loaded email addresses in Object type variable, now you want to send email to each of the address you have in object type variable, which task you will use to loop through and send email one by one
19.   There are 10 files in folder, we want to load them one by one, which tasks are required to perform this job
20.   You have a lot of Tasks sitting in Control Flow task , you want to group them, which container can help you to achieve that
21.   You got this assignment where you want to perform the same task 10 times, which loop you will use to do that
22.   Name few of the task those are available to create Maintenance plan or those can perform DBA tasks such as Create indexes, take backup etc.
23.   In Execute SQL Task what is Result Set( Single Row, Full Result Set,XML)
Posted by at   
Labels: , , , 
Subscribe to: Posts (Atom)
Posted by at No comments:
Subscribe to: Posts (Atom)