SSIS

  There are 6 different types of protection levels. Do not save sensitive – (When exporting using DTUTIL specify for protection- 0) Encrypt sensitive with user key – 1 Encrypt sensitive with password – 2 Encrypt all with password -3 Encrypt all with user key – 4 Rely on server storage Do not save sensitive:  makes the sensitive data unavailable to other users. If a different user opens the package, the sensitive information is replaced with blanks and the user must provide the sensitive information. Encrypt sensitive with user key:  Uses a key that is based on the current user profile to encrypt only the values of sensitive properties in the package. Only the same user who uses the same profile can load the package. If a different user opens the package, the sensitive information is replaced with blanks and the current user must provide new values for the sensitive data. If the user attempts to execute the package, package execution fails. Encrypt sensitive with password:  Uses a passw

  Below are the lists of various types of enumerators provided by SSIS Foreach Loop Container: Foreach  File Enumerator : It enumerates files in a folder. The plus point here is it can traverse through subfolders also. Foreach  Item Enumerator : It enumerates items in a collection. Like enumerating rows and columns in an Excel sheet. Foreach  ADO Enumerator : Useful for enumerating rows in tables. Foreach  ADO.NET Schema  Rowset  Enumerator : To enumerate through schema information about a data source. For example, to get list of tables in a database. Foreach   From  Variable Enumerator : Used to enumerate through the object contained in a variable. (if the object is enumerable) Foreach   NodeList  Enumerator : Used to enumerate the result set of an XML Path Language (XPath) expression. Foreach  SMO Enumerator : It enumerates through SQL Server Management Objects (SMO) objects.

  Rowcount component is a synchronous component and it doesn’t actually do anything particularly resource intensive means the performance degradation of your package should be negligible. We do use this component to capture the number of inserts, deletes and updates from each data-flow and then using “OnPost Execute” event this information would be written to a SQL Server table.

  If this property is set to true then the SSIS engine ignore the unused/unmapped columns. Means it does not allocate memory to store data for those columns. At the compilation phase itself SSIS engine identifies what are the columns from source are using across the package, if it finds any columns are neither using nor mapping to destination, it simply ignores all those columns. We can set this property at two levels “Project Level” and “Package Level”. Project Level: From project properties → Debugging → RunIn*****. By default “FALSE” Package Level: Can find in DataFlow properties. By default “TRUE”

  The OLE DB Destination provides more than one way to load data in the destination (5 types of Data Access Mode).  Use Fast Load option while loading data into the destination. Data Access Mode  – It allows to define the method to upload data into the destination. The fast load option will use  BULK INSERT  statement instead of  INSERT  statement. If the fast load option is not selected then by default INSERT is used. Keep Identity  – If selected, the identity values of source are preserved and the same are uploaded into the destination table. Else destination table will create its own identity values if there is any column of identity type. Keep Nulls  – If selected, the null values of the source are preserved and are uploaded into the destination table. Else if any column has default constraint defined at destination table and NULL value is coming from the source for that column then in that case, default value will be inserted into the destination table. Table Lock  – If selected,

  OLEDB connection  – Used to connect to any data source requiring an OLEDB connection (i.e., SQL Server) Flat file connection –  Used to make a connection to a single file in the File System. Required for reading information from a File System flat file ADO.Net connection –  Uses the .Net Provider to make a connection to SQL Server 2005 or other connection exposed through managed code (like C#) in a custom task Analysis Services connection –  Used to make a connection to an Analysis Services database or project. Required for the Analysis Services DDL Task and Analysis Services Processing Task File connection –  Used to reference a file or folder. The options are to either use or create a file or folder Excel FTP HTTP MSMQ SMO SMTP SQL Mobile WMI