SSIS

SSIS Tips:

  • When you create packages in BIDS, the package is stored in the fi le system with the .dtsx fi le extension. This .dtsx fi le is an XML fi le that contains the logic and the layout for the design you have developed in BIDS, and you can move the file to a different project, manually deploy it to different servers, or make it part of a deployment package.
  • Variables are viewable to a task or container only if the variables’ scope is at the scope of
    the task or container in question, at the scope of a parent container level, or at the package
    scope itself.
  • If you want to reference SSIS variables within a Script Task, you need to include the variables in the Readonlyvariables or ReadWritevariables list, depending on whether you will be just accessing the variable for read purposes or updating the variable.
  • Many SSIS objects have a ValidateExternalMetadata property that you can set to False if the object being referenced (such as a table) does not exist when the package is being designed. This property is most commonly used for source or destination adapters, such as when a destination table is created during package execution.
  • You can configure the OLE DB Destination adapter to insert data from the data flow through bulk batches of data, instead of one row at a time. To use this destination-optimization technique, edit the oLE DB Destination and set the Data Access Mode to Table or view— fast Load. When the OLE DB Destination is not configured with fast load, only one row at a
    time will be inserted into the destination table.
  • When trying to determine which transformation to use that brings more than one data source together, remember that the Merge Join Transformation brings two sorted sources together and matching rows together with either an Inner Join, a full outer Join, or a Left outer Join. Merge Join can match more than one row across the join columns. This behavior is different
    from that of the Lookup Transformation, which brings back only a single match across the join
    columns of the Lookup table.
    The union All Transformation does not join rows together but rather brings each row separately from the sources, stacking the rows together. The number of rows in the output of union All is the combined row counts of all the inputs. The Merge Transformation is similar to union All, except that the sources have to be sorted and the sort position is preserved.
  • At times, you might want to enable a transaction for a container but exclude some of the tasks within the container. for example, if you have a couple of Execute SQL Tasks in the container that are used for auditing purposes but the Transactionoption property for the container is set to Required, if an error occurs, the logging tasks will also be rolled back. To prevent the auditing tasks from rolling back, set the Transactionoption property for those tasks to not Supported. This will still let the other tasks in the container be in the transaction, but it will not include the auditing tasks as part of the transaction.
  • If you set the Checkpointusage property to Always, the checkpoint fi le must be present or the package will not start. In addition, using checkpoints is not allowed if you have set the Transactionoption of the package to Required.
  • you can turn off event handlers for any task or container by setting the DisableEventHandlers property of the task or container to True. So if you have an event handler defi ned but you do not want it to be invoked for a specifi c task, you can turn off event handlers for that task only.
  • When you use an environment variable as the pointer to the XML fi le, it is called an indirect confi guration. Indirect confi gurations are useful when the location of the fi le changes from the development server to the deployment server. To use the indirect confi guration, you first need to create the file by using the wizard and then go back to edit the configuration
    and assign the environment variable.
  • Packages can share SQL Server configurations even if they are running on different servers because the SQL Server configuration does not have to be stored on the local server, unlike the other configuration types.
  • Not only can you set package, task, and container properties through expressions, but you can also update the value of connection properties while a package is running. This capability is especially useful when you have a ForEach loop that is iterating over files. you can use the variable that the full path is being captured to and update the connection for the fi le with a property expression. In Exercise 3 in this lesson’s practice section, you will update the value of the Microsoft office Excel connection while the package is running.
  • When you deploy a package to SQL Server, the package is stored in the msdb database in a table named dbo.sysssispackages. Therefore, if you want to back up packages that have been deployed to SQL Server, you can back up the msdb database.
  • Whether you are deploying to SQL Server or to the fi le system, the Package Installation Wizard copies dependent fi les to the folder you specify. This includes XML configuration files that are used by any package in the project (but only where the XML configuration path is hard-coded in the package and does not use an environment variable).
    The Package Installation Wizard will also update the XML configuration entries in the package and change them to point to the new XML Configuration file path.
  • When you are running a package on a server node of a Windows cluster environment and the node fails, the restartability rules apply. you can turn on checkpoints in your packages and have the checkpoint fi le created on a share so that if the package needs to be restarted, it can locate and use the checkpoint file.
  • If you are deploying SSIS packages to a new server with the EncryptSensitiveWithuserKey ProtectionLevel setting, any connection password in the package will not be decrypted, and the package execution will fail. This applies even if you are using the deployment tools in SSIS and you run the deployment manifest on a different computer than where the
    packages were developed.
  • If you are running an SSIS package and you want to create a logging dump file if an error occurs, you can use the /DumpOnErr[or] switch. This is different from the /Dump switch, which creates the dump when a specifi ed event occurs.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.