I need advice. Here what I am doing in SSIS.
Simple SSIS Package.
- Execute SQL Task (Insert file name in the table)
- Data Flow Task (My Source is Flat File, Flat file could be 20k to 100k records or may be more, Couple of Transformation (Derived Column & Data Conversion) and insert the data into SQL Database table as a destination.
Here is my question is, This Package could run 10 to 20 times a day. Destination SQL Table is my Production DB. What methodology should I use for avoid DEAD LOCK on the table,
Should I use LOCK TABLE on OLE DB Destination?
If I use SEQUENCE CONTAINER to roll back if for some reason package file, should be fine or not?
Any Other Advise to Avoid DEAD LOCK on the table would be great appreciate.