What are the fast load options available in SSIS?

 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, the TABLOCK is acquired on the table during data upload. It is the recommended option if table is not being used by any other application at the time of data upload as it removes the overhead of lock escalation.
  • Check Constraints – Check constraints will always check for any constraint for the data that is coming through pipeline. It is preferable to uncheck this option if constraint checking is not required. This will reduce the overhead for the pipeline engine.
  • Rows per batch – RowsPerBatch is the number of rows you would want in One Buffer. SSIS automatically sets this property based on the RowSize and MaxBufferRows property. The number of rows coming from the pipeline per batch can be defined by user. The default value is -1 if it is kept blank. You can specify the no. of rows as a positive integer (N) so that the records will come as small segments or batches, each segment containing N no. of rows.
  • Maximum insert commit size – You can specify the batch size that the OLE DB destination tries to commit during fast load operations; it actually splits up chunks of data as they are inserted into your destination. If you provide a value for this property, the destination commits rows in batches that are the smaller from either (a) the Maximum insert commit size, or (b) the remaining rows in the buffer that is currently being processed.
  • Network limitations:  You can transfer data as fast as your network supports. But use them efficiently; you can customize SSIS to use the maximum bandwidth of your network. You can set the Packet Size property of the connection manager to an integer value that suits you. The max value that you can insert is 32767.

Comments

Post a Comment

Popular posts from this blog

Difference between ETL vs. ELT

Image
  Difference between ETL vs. ELT Basics ETL ELT Process Data is transferred to the ETL server and moved back to DB. High network bandwidth required. Data remains in the DB except for cross Database loads (e.g. source to object). Transformation Transformations are performed in ETL Server. Transformations are performed (in the source or) in the target. Code Usage Typically used for Source to target transfer Compute-intensive Transformations Small amount of data Typically used for High amounts of data Time-Maintenance It needs highs maintenance as you need to select data to load and transform. Low maintenance as data is always available. Calculations Overwrites existing column or Need to append the dataset and push to the target platform. Easily add the calculated column to the existing table. Analysis
Read more

What are the for each loop enumerators available in SSIS?

  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.
Read more

What are the SSIS package protection levels?

  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:...
Read more