Custom Auditing Vs Logging

Logging is basically used for capturing information about package execution itself - errors encountered, execution time for the package or steps within the package, data flow buffer
details, etc. Auditing is typically information about the data - how many records inserted,
updated, and deleted; when it was done; what was the source; who ran the package; what
machine did it run on, etc.

Script Task Vs Script Component

The Script component hosts script and enables a package to include and run custom script code.

You can use the Script component in packages for the following purposes:

• Apply multiple transformations to data instead of using multiple transformations in the data flow. For example, a script can add the values in two columns and then calculate the average of the sum.
• Access business rules in an existing .NET assembly. For example, a script can apply a business rule that specifies the range of values that are valid in an Income column.
• Use custom formulas and functions in addition to the functions and operators that the Integration Services expression grammar provides. For example, validate credit card numbers using the LUHN formula.
• Validate column data and skip records that contain invalid data. For example, a script can assess the reasonableness of a postage amount and skip records with extremely high or low amounts.

The Script component provides an easy and quick way to include custom functions in a data flow. However, if you plan to reuse the script code in multiple packages, you should consider programming a custom component instead of using the Script component. For more information, see Extending the Data Flow with Custom Components.

The Script component can be used as a source, a transformation, or a destination. This component supports one input and multiple outputs. Depending on how the component is used, it supports either an input or outputs or both. The script is invoked by every row in the input or output.

• If used as a source, the Script component supports multiple outputs.
• If used as a transformation, the Script component supports one input and multiple outputs.
• If used as a destination, the Script component supports one input.

The Script component does not support error outputs.

You can configure the Script component in the following ways:

• Select input columns to reference.
• Provide the script that the component runs.
• Specify whether the script is precompiled.
• Provide comma-separated lists of read-only and read/write variables.
• Add more outputs, and add output columns to which the script assigns values.

The Script task provides code to perform functions that are not available in the built-in tasks and transformations that SQL Server 2005 Integration Services provides. The Script task can also combine functions in one script instead of using multiple tasks and transformations. The code is custom Microsoft Visual Basic .NET code that is compiled and executed at package run time.

You can use the Script task for the following purposes:

• Access data using other technologies that are not supported by built-in connection types. For example, a script can use Active Directory Service Interfaces (ADSI) to access and extract user names from Active Directory.
• Create a package-specific performance counter. For example, a script can create a performance counter that is updated while a complex or poorly performing task runs.
• Replace data in the source with different data in the destination. For example, a script can replace a two-digit state code in the source data with the state name in the destination data.
• Validate important columns in the source data and skip records that contain invalid data to prevent them from being copied to the destination. For example, a script can assess the reasonableness of a postage amount and skip records with extremely high or low amounts

Differences between....

            a.  merge and merge join-- Merge Acts like a union all in T-sql but you can provide only two input to this transformation which must be pre- sorted and merge join joins the two datasets like T-SQL joins(inner join , left join, right join )

            b.  union and union all---- The union command is used to select related information from two tables but with union distinct values are selected. The union all command is equal to the union command, except union all selects all values and will not get rid of duplicate rows.

            c.  merge join and union all--- Merge can only accept two datasets while Union All can accept more than two datasets for input. The second difference is that Merge requires both datasets to be sorted while Union All does not require sorted datasets

            d.  row sampling and percentage sampling--- Row sampling is used to randomly select some rows and move them as output to the required process as and when required to divide the rows. Percentage Sampling on the other hand, selects rows randomly by percentage.

            e.  dts and ssis---- DTS is a set of objects using an ETS tool to extract, transform, and load information to or from a database; SSIS is an ETL tool provided by Microsoft to extract data from different sources.

            f.  pivot and unpivot---- Pivot will convert rows to columns and un-pivot will go the other way, columns to rows

            g.  scnhronous transformation  and ascnchronous transformation--- A synchronous transformation processes incoming rows and passes them on in the data flow one row at a time. Output is synchronous with input, meaning that it occurs at the same time.  The transformation does not need information about other rows in the data set. The Rows are grouped into Buffers as they pass from one component to the next. Meanwhile,  in an asynchronous transformation, you cannot pass each row along in the data flow as it is processed, but instead must output data asynchronously, or at a different time, than the input.

            h.  look-up and merge join--- The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset.  Lookup uses an ole db connection manager. While a merge join combines two sorted datasets using a FULL, LEFT, or INNER join.

Ole db Destination Vs SQL Server Destination

The SQL Server Destination is an adapter for getting data into SQL Server really fast. However, it can only be used when the ssis package is being run on the same serverthat you are inserting into.

The Ole Db Destination loads data into a variety of ole db compliant databases using a database table or view or a SQL command.

Slowly Changing Dimensions (SCD)

Slowly Changing Dimensions: This transformation co-ordinates the updating and inserting of records in data warehouse dimension tables. It manages the tracking of attribute history in dimensional data warehouses. 3 important types:

Type 0-Fixed: It cannot be changed.

Type 1-Changing: It indicates that the changes in the attribute must be replaced.

Type 2-Historical: Tracks the history by creating a new record.

Fuzzy Lookup

Fuzzy Lookups build specialized indexes of the input stream and the reference data for comparison purposes. You can store them for efficiency but these indexes can use up some disk space or can take up memory if you choose to rebuild them on each run.

Custom Auditing/Logging

Auditing logs information about the data and its flow.

Logging logs information about the package execution.

Max Concurrent Executable Property

The Max Concurrent Executable Property defines how many tasks can run simultaneously by specifying the maximum number of executables that can execute in parallel per package. The default value is -1, which is equal to the number of physical or logical processors plus 2.

Fast Parse

Fast Parse : A set of operations within a package that can be used for very fast loading of flat file data. Fast parse is only available when you use the flat file source or the data conversion editor.

How to schedule a SSIS Package

First go to sql server management studio and create new job from the sql server agent menu and then click create new step. After that select the type as Sql Server Integration Services Package. Next select the package souce as File System and give the package path. After that click 'OK' and a screen comes up. Finally, on that screen you can configure your schedule as you desire.

Performance Tuning in SSIS

Well this is a very broad subject. Basically you're finding out what is running slow and eliminate whatever is causing the slowness. When performance tuning a data flow, the goal should be to pass as many records as possible through  a single buffer while utilizing memory. Proactive caching also supports great performance. Wrong data types and size can also affect performance.

I'll come back to this one shortly...

How do you pass Variable from Parent package to Child Package?

First create 2 packages: one named parent and one named child. On both packages create a string variable named "StrVar." In the parent package give the variable the value of "parent." Pass this value to the child package. In the child package, give the variable the value of "child", which will be over written by the parent package.

File System and Store in MSDB

File system is for the packages which have been stored physically on the drive, this can be anywhere on the drive.

MSDB are the packages installed on the system and can be run instantly. These are the ones which are stored inside the pace allocated by the integration services for its settings.

How to deploy a package

A ssis package can be simply copied from its project location to a folder on the deployment target. These are the 3 ways to deploy a package.

• Create a deployment utility from our project.
• Use SSMS
• Use DTUTIL command line utility

What is command line utility?

Command Line Utility Allows developers to execute the package. It can directly be used from the command line prompt by moving around the folder to where the package is available and the  run the utility.

C:\>dtexec.exe /F "C:\ImportCSV\Package.dtsx"

How to make your package more dynamic?

You can always use variables to configure a package dynamically at runtime. Without variables, each time you wanted to deploy a package from development to production, you would have to  open the package and change all the hard-coded connection settings and then run the utility. Other ways to make a package more dynamic: use .xml files and use package configuration.

Error Handling in SSIS

Errors can occur frequently because of unexpected data values. But many data flow components support error outputs which let you control how the component handles row level errors in both incoming and outgoing data. So there are 3 actions that are available when an error occurs:

1. Ignore Failure- If an error occurs, SSIS ignores the error and moves ahead.

2. Redirect Row- If error occurs, SSIS moves those bad records to a different destination and correct records to the actual destinations.

3. Fail Component- Default component being used in SSIS, which fails the package if there is any error.

ETL

ETL: Extract,Transform, Load is the core proccess of data integration. ETL tools extract data from a chosen source(s), transforms it into new formats according to business rules, and then load it into target data structures.

Logging

Logging in SSIS can capture runtime information about a package, container, and a task. When you add the log to a package, it helps you audit and troubleshoot a package everytime it runs! For example: a log can capture the name of the operator who ran the package and the time the package began and finished.

Checkpoint and Breakpoint

Checkpoint: Ensures that if your package fails after  multiple tasks have succeeded, the progress you made will not be lost. A checkpoint will save all of the information prior to that task failing. You can run the package from that checkpoint and not have to start over.

Breakpoint: A stopping point in the code that gives you the chance to review the status of the data, variables, and overall status of the ssis package.