Points should be noted while creating a ssis Packages

3.SQL job with many atomic steps.

For the SQL job that calls the SSIS packages, make multiple steps, each doing small tasks, rather than a single step doing all the tasks. In the first case, the transaction log grows too big, and if a rollback happens, it may take the full processing space of the server.

4.Avoid unnecessary typecasts.

Avoid unnecessary type casts. For example, the flat file connection manager, by default, uses the string [DT_STR] data type for all the columns. In case you want to use the actual data types, you have to manually change it. Better to change it at the source-level itself to avoid unnecessary type castings.

5.Transactions.

Usually, the ETL processes handle large volumes of data. In such a scenario, do not attempt a transaction on the whole package logic. However, SSIS supports transaction, and it is advisable to use transactions where the atomicity of the transaction is taken care of.

For example, consider a scenario where a source record is to be spitted into 25 records at the target - where either all the 25 records reach the destination or zero. In this scenario, using a transaction, we can ensure either all the 25 records reach the destination or zero.