Q. 1 Would you consider it a best practice to create a configuration file for each connection managers or one configuration file for all connection managers?
à There should be a single configuration file for each connection manager in your packages that stores their connection string information. So if you have 6 connection managers then you have 6 config files. You can use the same config file across all your packages that use the same connections.
If you have a single config file that stores all your connection managers then all your packages must have contain the connection managers that are stored in that config file. This means you may have to put connection managers in your package that you don’t even need.
Q. 2 How checkpoints work in a package?
à Possible values are:
· Never
· IfExists
· Always
To implement the CheckPoints, need to follow the below steps:
1. Open the package level properties.
2. Set the SaveCheckPoints property to True. This allows SSIS to save the checkpoints during package execution.
3. Provide a valid path and file name for the CheckpointFileName property, package uses this file to restart the package.
4. Set the CheckpointUsage property to ‘IfExists’, which causes the package to run from beginning if the checkpointfile is not present or to run from the identified point if file exists.
5. If you set the CheckpointUsage to ‘Always’ the file must be present at the specified path or the package will not start. In addition, using CheckPoints is not allowed if you have set the TransactionOption of the package to Required.
6. After you enable the checkpoints in your package, the final step is to set checkpoints at the various tasks within package. To do this, set the FailPackageOnFailure property at each task or container to True.
7. What if, we don’t set the FailPackageOnFailure property of each task in the package:- Package will fails if error comes, but it will not save the checkpoint file.
Q. 3 How transactions work in a package?
à Possible values are:
· Required
· Supported
· NotSupported
To implement the transactions in SSIS package, you need to follow the below guidelines:
1. You need to start the Microsoft Distributed Transaction Coordinator (MSDTC) service.
2. If a series of tasks must be completed as a single unit in which either all the tasks are successful and committed or some error occurred and none of the tasks are committed. In such scenario, place all the tasks in a sequence container and set the TransactionOption property of the container to Required.
3. A task can inherit the transaction setting to its parent when the TransactionOption property is set to supported, which is the default setting when creating a task or container.
4. You can prevent a task from participating it in transaction by setting its TransactionOption property to NotSupported.
5. If you set the TransactionOption property to Required of a ForEach loop container or For Loop container to Required, a new transaction will be created for each loop.
6. Transactions work with control flow level and not in data flow level.
Q. 4 If you have a package that runs fine in Business Intelligence Development Studio (BIDS) but fails when running from a SQL Agent Job what would be your guess on what the problem is?
à The account that runs SQL Agent Jobs likely doesn’t have the needed permissions for one of the connections in your package. Either elevate the account permissions or create a proxy account.
To create a proxy account you need to first create new credentials with the appropriate permissions. Next assign those credentials to a proxy account. When you run the job now you will select Run As the newly created proxy account.
Q. 5 What techniques would you consider to add notification to your package?
à This could either be set in the SQL Agent when the package runs or actually inside the package you could add a Send Mail Task in the Event Handlers to notify when a package fails.
Q. 6 What techniques would you consider to add auditing to your package?
à I like to create a database that is designated for package auditing. Track row counts coming from a source and which actually make it to a destination. Row counts and package execution should be all in one location and then optionally report off that database.
Q. 7 What techniques would you like to use for CDC?
à If for some reason you’ve avoided using a whiteboard to show your ideas to this point then make sure you start on this question! For small amounts of data I may use the Slowly Changing Dimension.
More often than not the data is too large to use in such a slow transform. I prefer to do a lookup on the key of the target table and rows that don’t match are obviously new rows that can be inserted. If they do match it’s possible they are updates or duplicates. Determine this by using a conditional split comparing rows from the target to incoming rows. Send updates to a staging table that can then be updated in an Execute SQL Task.
Explain that putting updates in a staging table instead of updating using the OLE DB Command is much better for performance because the Execute SQL Task performs a bulk operation.
8. Explain what breakpoints are and how you would use them.
à Breakpoints put pauses in your package. It’s a great tool for debugging a package because you can place a breakpoint on a task and it will pause the package based on execution events.
A reason in which I have used breakpoints is when I have a looping container and I want to see how my variables are changed by the loop. I would place a watch window on the package and type the variable name in. Set a break point on the container the stop after each iteration of the loop.
Q. 8 How can we skip first ‘n’ rows from an excel file.
à Need to follow the below steps:
1. Create a variable and provide a value as ‘<sheet name>$A<starting row number>:F<ending row number>. i.e “SheetName$A12:H125”.
2. In your excel source, select data Access Mode as ‘Table Name or View name variable’.
3. Now select the above created variable as source and you are done. Your excel connection manager will start reading rows from where you have provided it.
Or
1. We can go to the property window of Excel Source and set the AccessMode property to OpenRowSet.
2. Assign the sheetname and cell range to the OpenRowSet property of Excel Source.
Q. 9 Data Flow Sources?
à ADO NET: Provides connection to tables and queries through an ADO.NET provider.
Excel Source: Provides connection to an excel workbook.
OLE DB Source: Connects to installed OLE DB providers, like SQL Server, Access, SSAS and Oracle.
Flat File Source: Connects to a delimited or fixed-width file created with different code pages.
Raw File Source: Stores native SSIS data in a binary file type useful for data staging.
XML Source: Allows raw data to be extracted from an XML file, requires an XML schema to define data associations.
ADO.NET and OLE DB Connection managers are almost same and OLE DB connection manager is said to be more faster, but in case we want to play with Active Directory then ADO.NET is much preferable than OLE DB connection manager.
Q. 10 What is ValidateExternalMetadata?
à If the object being referenced (such as a table) does not exist when the package is being designed, we can set this property as ‘False’.
This property is most commonly used for source or destination adapters, such as when a destination table is created during the package execution.
Q. 11 Data Flow Destinations?
à
ADO.NET Destination:
Data Mining Model Training:
DataReader Destination:
Dimension Processing:
Excel Destination:
Flat File Destination:
OLE DB Destination:
Partition Processing:
Raw File Destination:
Recordset Destination:
SQL Server compact Destination:
SQL Server Destination:
When the destination is on the same machine as the source, then use SQL Server Destination else always use OELDB destination. SQL Server Destination is quite fast as compared to OLEDB but only in the case I mentioned.
Conditional Split: Routes or filters data based on Boolean expression to one or more outputs, from which each row can be sent out to only one output path.
Lookup: Allows matching between pipeline column values to the external database tables; additional columns can be added to the data flow from the external table.
Merge: Combines the rows of two similar sorted inputs, one on top of the other, based on a defined sorted key.
Merge Join: Joins the rows of two sorted inputs, based on defined join column(s), adding columns from each source.
Multicast: Generates one or more outputs, from which each and every row is sent out to each output.
Union All: Combines one or more similar inputs, stacking rows one on top of another, based on matching columns.
Q. Difference between Merge and Union All transformation?
a. Merge Transform can accept only two inputs where as Union all can take more than two inputs.
b. Data has to be sorted before merge transformation where as Union all doesn’t have any condition like that.
Q. New features in SSIS 2008?
· Improved scripting: - SQL Server 2008 (Katmai) uses the VSTA as new scripting engine, which replaces the old VSA scripting engine used in SQL Server 2005 (Yukon). Most excited new feature is that it allows users to use C# as scripting language (as well as VB.Net). It also makes it easier to reference all .Net assemblies, which enables a number of common scenarios (such as easier web service integration).
· Data Flow Task Improvements: - It essentially boils down to smarter thread allocation and processing of our execution trees. Long chains of synchronous transforms are optimized, and most packages will see a performance increase on multi-processor machines.
· Enhanced ADO.Net support: - Katmai now has an ADO.net source (replacing the DataReader source) and destination adapters. They function like OLEDB ones, supporting a custom UI, drop down list of tables/views, and query builder. Its properties are also expression-able. This means we now support ODBC destination, as well.
· Cached Lookup: - Now we can create a lookup cache in a separate data flow using the new Lookup Cache transform and Cache Connection Manager, and persist it on the disk. This Cache can be generated using any data source supported by SSIS.
· Data Profiling Task: - The new Data Profiling task let’s you analyze SQL table to determine (and maintain) data quality.
· SSIS can make use of SQL Server 2008 feature of CDC and Merge statement.
Q. Parent-Child Configuration?
à
Q. How many types of configurations are available in SSIS?
à Below are the types:
1. XML configuration file
2. Environment Variable
3. Registry entry
4. Parent package variable
5. SQL server
Transformations:
1. Synchronous and Asynchronous Transformations:
There are two types of Asynchronous transforms, full blocking transform and partial blocking transform. Partial blocking transform is UNION ALL transform. Full blocking transform is Sort and aggregate transform.
Q. What are Precedence Constraints?
à Precedence Constraints direct the tasks to execute in a given order. In fact, precedence constraints are the connectors that not only link tasks together but define the workflow of your SSIS package.
There are three types of Constraint Values:
1. Success: A task that’s chained to another task with this constraint will execute only if the prior task completes successfully. These precedence constraints are colored green.
2. Completion: A task that’s chained to another task with this constraint will execute only if the prior task completes. Whether the prior task succeeds or fails is inconsequential. These precedence constraints are colored blue.
3. Failure: A task that’s chained to another task with this constraint will execute only if the prior task fails to complete. This type of constraint is usually used to notify an operator of a failed event or write bad records to an exception queue. These precedence constraints are colored red.
Containers:
Containers are the core unit in the SSIS architecture to group tasks together logically into units of work.
There are four types of containers in SSIS:
1. Task Host Container: The core type of container implements the basic interface to which every task implicitly belongs by default. The SSIS architecture extends variables and event handlers to the task through the Task Host Container.
The task host container is not visible element that you’ll find in the toolbox, but is an abstract concept like an interface.
2. Sequence Container: Allows you to group tasks into logical subject areas.
3. For Loop Container: Loops through a series of tasks for a given amount of time or using an iterator until a condition is met.
4. Foreach Loop Conatiner: Loops through a series of files or records in a data set, and then executes the tasks in the container for each record in the collection.
Difference between Sequence Container and Group property:
Sequence container is sort of physical grouping whereas Group property gives the logical grouping.
Data Flow Sources: There are six sources which can be used with SSIS.
1. OLE DB Source: Connects to nearly any OLE DB Source like Sql Server, Access, Oracle, or DB2, just to name few.
2. Excel Source:
3. Flat File Source: Connects to a delimited or fixed-width file.
4. Raw File Source: Produces a specialized binary file format for data that is in transit and is especially quick to read by SSIS.
5. XML Source: Can retrieve data from XML document.
6. ADO.NET Source:
We can also Script Component to create a source stream using the existing .NET libraries. By this we can also cater the requirement of reusability of custom source.
Destinations:
Transformations:
Q. By How many ways we can write the logs of a SSIS package.
1. Windows Event Log.
2. Text Files
3. XML Files
4. SQL Server
5. SQL Server Profiler.
Difference between Control Flow and Data Flow?
à Control Flow:
1. Package Control Flow is made up of Containers and tasks connected with Precedence Constraints to control the package flow.
2. Control Flow does not move data from Task to Task.
3. Smallest Unit of Control Flow is Task.
4. Precedence Constraints control the project flow based on task completion, success or failure.
5. Tasks are run in series if connected through Precedence Constraints and Task 1 needs to be completed before Task 2 begins or Tasks can be run parallel if not connected with Precedence Constraint.
Data Flow:
1. Data Flow is made up of source(s), transformations and destinations.
2. Data Flow does move the data between each component.
3. Smallest Unit of Data Flow is component.
4. Multiple components can process the data at same time.
Performance Tuning of Data Flow.
BufferSizeTunig event
BLOBTempStoragePath
BufferTempStoragePath
DefaultBufferMaxRows
DefaultBufferSize
EngineThreads
RunInOptimizedMode
PIVOT:
PivotUsage – Possible Values
0: The Column is just passed through as row attribute.
1: The column is the “part of Columns”
2: The Pivot Column
3: The Value Column
File System Task:
1. Select ForEach File Enumerator in ForEach Loop Task.
2. Then browse the source directory.
3. We can also use Variables for assigning source directory and/or file type which we want to be processed by ForEach loop.
a. Set “Directory” & “FileSpec” expression properties of ForEach Loop Task.
4. Then map a variable in “Variable Mappings” tab to accept the processing file’s full path, which can be used while setting the connection string of Source & Destination connection manager.
We can’t use “For Loop Container” for processing files in a folder. We have to use “ForEach Loop Container” for same.
How to execute SSIS package from a stored procedure?
è Firstly, we need to enable “xp_cmdshell” stored procedure by using the below code:
USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO
è Then execute the below code to execute the SSIS package.
declare @ssisstr varchar(8000), @packagenamevarchar(200),@servername varchar(100)
----my package name
set @packagename = '[ExcelDestination]'
----my server name
set @servername = 'Rajnish-PC\Rajnish'
----now making "dtexec" SQL from dynamic values
set @ssisstr = 'dtexec /sq ' + @packagename + ' /ser ' +@servername + ' '
DECLARE @returncode int
EXEC @returncode = xp_cmdshell @ssisstr
select @returncode
from file system
declare @ssisstr varchar(8000), @packagenamevarchar(200),@servername varchar(100)
----my package name
set @packagename ='E:\SSISInterview\Interview\Interview\Merge.dtsx'
----my server name
----now making "dtexec" SQL from dynamic values
set @ssisstr = 'dtexec /f ' + @packagename
DECLARE @returncode int
EXEC @returncode = xp_cmdshell @ssisstr
select @returncode
How to execute from command prompt:
è First go to the directory of SQL Server to find “dtexec.exe”, where it is installed on your machine. Typically it can be found at “C:\Program Files\Microsoft SQL Server\100\DTS\Binn”
è Now execute the below command:
To execute from filesystem: dtexec /f “path of package with file extension”.
To execute from SQL Server MSDB: dtexec /sql “only name of package in MSDB w/o extension”.
How to deploy packages:
We can deploy packages by 4 ways:
1. Deployment Utility: Need to follow below steps
a. In Business Intelligence Development Studio, open the solution that contains the Integration Services project for which you want to create a package deployment utility.
b. Right-click the project and click Properties.
c. In the <project name> Property Pages dialog box, click Deployment Utility.
d. To update package configurations when packages are deployed, setAllowConfigurationChanges to True.
e. Set CreateDeploymentUtility to True.
f. Optionally, update the location of the deployment utility by modifying theDeploymentOutputPath property.
g. Click OK.
h. In Solution Explorer, right-click the project, and then click Build.
i. View the build progress and build errors in the Output window.
2. Dtutil:
We can create a batch file or script file to do all the deployments.
From FileSystem to MSDB
dtutil /fi "D:\SSIS Interview\Interview\Interview\ExcelDestination.dtsx" /c sql;exceld
From MSDB to file system
dtutil /sq exceldestination /sourceserver rajnish-pc/rajnish /c file;"c:\exceldestination.dtsx"
We can also do below stuffs using dtutil
· /Encrypt – copies and encrypts the package with a specified password and protection level
· /IDRegenerate – Generates a new GUID for the package
· /Sign* – Applies a digital signature to a package
· /Delete – Delete a package
· /Exists – Test if a package exists
· /FCreate – Create a folder
· /FDelete – Delete a folder
· /FDirectory – List the contents of a folder
· /FExists – Test if a folder exists
· /FRename – Rename a folder
· /Move – Move a package from one location to another
· /Quiet – suppress prompts and any existing package is overwritten
· /Remark – add a comment
· /Decrypt – decrypt a package as an operation is performed
3. SQL Server Management Studio: We can deploy package by importing packages from file system to SQL Server MSDB.
what are the main component of ssis(project-archItecture)?
A).ssis archItecture has 4 main components
1.ssis service
2.ssis runtime engine & runtime executables
3.ssis dataflow engine & dataflow components
4.ssis clients
A).ssis archItecture has 4 main components
1.ssis service
2.ssis runtime engine & runtime executables
3.ssis dataflow engine & dataflow components
4.ssis clients
different components in ssis package?
a).1.control flow
2.data flow
3.event handler
4.package explorer
a).1.control flow
2.data flow
3.event handler
4.package explorer
TRANSFORMATIONS??
It is an object that generates, modifies, or passes data.
1.AGGEGATE T/R:-It applies an aggregate function to grouped records and produces new output records from aggregated results.
2.AUDIT T/R:-the t/r adds the value of a system variable, such as machine name or execution instance GUID to a new output column.
3.CHARACTER MAP T/R:-this t/r makes string data changes such as changing data from lower case to upper case.
4.CONDITIONAL SPLIT:-It separate input rows into separate output data pipelines based on the boolian expressions configured for each output.
5.COPY COLUMN:-add a copy of column to the t/r output we can later transform the copy keeping the original for audIting personal
6.DATA CONVERSION:-converts a columns data type to another data type.
7.DATA MINING QUERY:-perform a data mining query against analysis services.
8.DERIVED COLUMN:-create a new derive column calculated from expression.
9.EXPORT COLUMN:-It allows you to export a column from the data flow to a file.
10.FUZZY GROUPING:-perform data cleansing by finding rows that are likely duplicates.
11.FUZZY LOOKUP:-matches and standardizes data based on fuzzy logic.
eg:-transform the name jon to john
12.IMPORT COLUMN:-reads the dat from a file & adds It into a dataflow.
13.LOOKUP:-perform the lookup of data tobe used later in a transform.
ex:-t/f to lookup a cIty based on zipcode.
1.getting a related value from a table using a key column value
2.update slowly changing dimension table
3.to check whether records already exist in the table.
14.MERGE:-merges two sorted data sets into a single data set into a single data flow.
15.MERGE JOIN:-merges two data sets into a single dataset using a join junction.
16.MULTI CAST:-sends a copy of two datato an addItional path in the workflow.
17.ROW COUNT:-stores the rows count from the data flow into a variable.
18.ROW SAMPLING:-captures the sample of data from the dataflow by using a row count of the total rows in dataflow.
20.UNION ALL:-merge multiple data sets into a single dataset.
21.PIVOT:-converts rows into columns
22.UNPIVOT:-converts columns into rows
It is an object that generates, modifies, or passes data.
1.AGGEGATE T/R:-It applies an aggregate function to grouped records and produces new output records from aggregated results.
2.AUDIT T/R:-the t/r adds the value of a system variable, such as machine name or execution instance GUID to a new output column.
3.CHARACTER MAP T/R:-this t/r makes string data changes such as changing data from lower case to upper case.
4.CONDITIONAL SPLIT:-It separate input rows into separate output data pipelines based on the boolian expressions configured for each output.
5.COPY COLUMN:-add a copy of column to the t/r output we can later transform the copy keeping the original for audIting personal
6.DATA CONVERSION:-converts a columns data type to another data type.
7.DATA MINING QUERY:-perform a data mining query against analysis services.
8.DERIVED COLUMN:-create a new derive column calculated from expression.
9.EXPORT COLUMN:-It allows you to export a column from the data flow to a file.
10.FUZZY GROUPING:-perform data cleansing by finding rows that are likely duplicates.
11.FUZZY LOOKUP:-matches and standardizes data based on fuzzy logic.
eg:-transform the name jon to john
12.IMPORT COLUMN:-reads the dat from a file & adds It into a dataflow.
13.LOOKUP:-perform the lookup of data tobe used later in a transform.
ex:-t/f to lookup a cIty based on zipcode.
1.getting a related value from a table using a key column value
2.update slowly changing dimension table
3.to check whether records already exist in the table.
14.MERGE:-merges two sorted data sets into a single data set into a single data flow.
15.MERGE JOIN:-merges two data sets into a single dataset using a join junction.
16.MULTI CAST:-sends a copy of two datato an addItional path in the workflow.
17.ROW COUNT:-stores the rows count from the data flow into a variable.
18.ROW SAMPLING:-captures the sample of data from the dataflow by using a row count of the total rows in dataflow.
20.UNION ALL:-merge multiple data sets into a single dataset.
21.PIVOT:-converts rows into columns
22.UNPIVOT:-converts columns into rows
Control Flow Task:
1. Foreach Loop Container: - Done
2. Sequence Container: - Done
3. Data Flow Task: - Done
4. Data Profiling Task: -
a. Data profiling task only works with data that is stored in SQL Server only. It does not work with third-party or file based data sources.
b. Analyze the source data more effectively.
c. Understand the source data better.
d. Prevent data quality problems before they are introduced to data warehouse.
5. Execute Package Task: - Done
6. Execute Process Task: - Done
7. Execute SQL Task: - Done
8. File System Task: - Operation supported are:
a. Copy Directory
b. Copy File
c. Create Directory
d. Delete Directory
e. Delete Directory Content
f. Delete File
g. Move Directory
h. Move File
i. Rename File
9. FTP Task: - Operations supported are:
a. Send Files
b. Receive files
c. Create Local Directory
d. Create Remote Directory
e. Remove Local Directory
f. Remove Remote Directory
g. Delete local files
h. Delete remote files
10. Script Task: - Done
11. Send Mail Task: - Done
Data Flow Task:
1. ADO NET Source: - ADO.NET and OLE DB Connection managers are almost same and OLE DB connection manager is said to be more faster, but in case we want to play with Active Directory then ADO NET is much preferable than OLE DB connection manager.
2. Excel Source: - Excel always stores the data in Unicode (nvarchar) format. Hence if we are storing this data in varchar column, we need to convert the same.
3. Flat File Source: - Done
4. OLE DB Source: - Done
5. XML Source
6. Aggregate: - The Aggregate transformation applies aggregate functions, such as Average, to column values and copies the results to the transformation output. Besides aggregate functions, the transformation provides the GROUP BY clause, which you can use to specify groups to aggregate across.
7. Cache Transform: - Done
8. Conditional Split: - Done
9. Copy Column: - Done
10. Data Conversion: - Done
11. Derived Column: - Done
12. Export Column: - The Export Column transformation reads data in a data flow and inserts the data into a file. For example, if the data flow contains product information, such as a picture of each product, you could use the Export Column transformation to save the images to files.
The transformation uses pairs of input columns: One column contains a file name, and the other column contains data. Each row in the data set can specify a different file. As the transformation processes a row, the data is inserted into the specified file. At run time, the transformation creates the files, if they do not already exist, and then the transformation writes the data to the files. The data to be written must have a DT_TEXT, DT_NTEXT, or DT_IMAGE data type.
13. Fuzzy Grouping: - The Fuzzy Grouping transformation performs data cleaning tasks by identifying rows of data that are likely to be duplicates and selecting a canonical row of data to use in standardizing the data.
14. Fuzzy Lookup: - The Fuzzy Lookup transformation performs data cleaning tasks such as standardizing data, correcting data, and providing missing values.
15. Import Column: - Vice – a – versa of Export Column
16. Lookup: - Done
17. Merge: - Done
18. Merge Join: - Done
19. Multicast: - Done
20. OLE DB Command: - Done
21. Pivot
22. Row Count: - Done
23. Script Component: - Done
24. Slowly Changing Dimension: - Done
25. Sort: - Done
26. Term Lookup
27. Union All: - Done
28. Unpivot
29. ADO NET Destination: - Done
30. DataReader Destination
31. Excel Destination: - Done
32. Flat File Destination: - Done
33. OLE DB Destination: - Done
34. SQL Server Destination: - Done
For debugging
Dataviewer: - Done
Requirement Gathering techniques for data warehouse projects:
1. Brainstorming: Brainstorming can be done to get as many ideas as possible from a group of people. It can provide many solutions to problems.
2. Document Analysis: Reviewing the documentation of an existing similar system can help.
3. Interviews: Need to have discussions with business users or stakeholders.
4. Dimensional data modeling:
a. Understanding the core business properties (dimensions).
b. The essential knowledge to be analyzed (facts).
c. The level of details (the grain of the base fact table).
d. How core business objects need to be shared (conformed dimensions).
e. The business meaning of each dimension, fact and data item.
5. Process and context modeling:
a. The canned or predictable processes required to access and format data for end-user consumption in terms of user views.
b. The ad-hoc processes of accessing data at any level in data warehouse.
c. The data quality audit processes in verifying data loading into the warehouse.
d. The data access authorization processes in governing access to the various levels of data warehouse.
e. The change or problem management processes.
6. Prototyping:
7. Story boarding:
Providing estimates for data warehouse projects:
There are 2 techniques which can be used for providing estimates.
1. Top-Down Estimates: Estimates are given based on prior exp. Or just based on ideas.
2. Bottom-Up Estimates: Estimates are given based on detailed analysis.
How to make SSIS work fastest while loading data?
· Use “Fast Load” option
· In case we are using “Flat File Connection Manager” or data conversion transform, we can set “FastParse” property to true for the columns having data type as int, date & time in the “Output” column section of Advance Editor of Source Component.
· In Advance tab of “Source Connection Manager” try to provide the correct data type to each column as in destination table so that implicit data type conversion does not take much time.
· MaxConcurrentExecutables: MaxConcurrentExecutables property of package should be set = -1 (default value). It means that no. of logical/physical processors + 2 tasks can be executed in parallel. We can set any fix value as well, but in that case engine will execute only that much tasks in parallel.
· EngineThreads: This property defines how many threads the data flow engine can create and run in parallel. This property applies equally to both source threads that the data flow engine creates for source and worker thread which data flow engine creates for transformations and destinations. Default value of this property in SSIS 2008 is 10 and in 2005 it is 5, with a minimum value of 2. The general rule is to not run more threads in parallel than the number of available processors. Running more threads than the number of processors can hinder performance because of the frequent context-switching between threads.
Performance Tips:
1. While pulling high volume of data, drop all the indexes on destination table as it will hit performance while inserting the data.
2. Avoid “Select *”
3. Effect of OLEDB Destination Settings: There are couples of settings which can impact the performance of OLEDB destination.
a. Data Access Mode: This setting provides the “Fast Load” option which internally uses a bulk insert. If not require does not change this setting and load the data in fast load mode. With fast load option there are other 4 properties which we need to set.
b. Keep Identity:
c. Keep Nulls:
d. Table Lock:
e. Check Constraints:
4. Effect of Rows Per Batch and Maximum Insert Commit Size Settings.
a. Rows per Batch.
b. Maximum insert commit size.
Refer: http://www.mssqltips.com/sqlservertip/1840/sql-server-integration-services-ssis-best-practices/
5. Use SQL Server Destination Adapter, if your target is local SQL Server database.
6. Avoid asynchronous transformation wherever possible, such as Sort Transformation. All the transformations which wait for all the incoming records and then process them and which might decrease the number of output records in compare to input records are considered to be the asynchronous transformations.
7. DefaultBufferMaxSize and DefaultBufferMaxRows
8. BufferTempStoragePath and BLOBTempStoragePath
9. How DelayValidation property can help.
What are the Top 10 new features in SSIS 2012?
è Below are the features:
1. Change Data Capture:- We have got new CDC control task, CDC source component and CDC splitter (which helps in identifying the insert/update/delete operations)
2. ODBC source and destination components
3. Connection Manager changes: Now we have got expressionable shared connection managers, now we can also share the in-memory cache across package executions (i.e. parent package creates a cache and all child packages can use the same in-memory cache)
4. Flat File source improvement: Now flat file source component supports a varying number of columns and embedded qualifiers.
5. Package format changes: ----
6. Visual Studio configurations: Now we can apply configurations when package is executed in Visual Studio
7. Scripting Improvement: Scripting Engine have been upgraded to VSTA 3.0, which gives support for .Net 4.0 and script Component debugging is also added
8. Troubleshooting & Logging: -------
No comments:
Post a Comment