First questions related to SQL Server Integraion Services:
SSIS. When I setup OleDbSource or OleDbDestination i have received an error:
LCPI.IBProvider.2 - "Opening a rowset for "TABLENAME" failed. Check that the object exists in the database
It was because SSIS by default does not start transactions.
add "auto_commit = true" into the connectionString of a Connection Manager. IBProvider will be manage transactions in automatic mode like OLE DB Provider for SQL Server
Second Solution (recomended):
Control Flow components provide 2 properties: TransactionOption and IsolationLevel. They used for setup distributed transactions.
Set properties for DataFlow Component which contains you OleDbSource or OleDbDestination:
TransactionOption = Required
IsolationLevel = RepeatableRead
SSIS. You get an error::
The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.
Solution Run Distributed Transaction Coordinator Service.
SSIS. You are set correct user name and password for each connection manager but get an error::
Your user name and password are not definded. Ask your database administrator.
Solution: Package Protection level by default does not allowed to storing passwords in package.
You can download my example and explore a way for storing password in package:
Sample used the following method:
1. Declare Variables to define location, user and password.
2. Define expression for setup connectionString for ConnectionManager in runtime
If you search you will find more solution for store passwords. Keywords are: SSIS, ProtectionLevel, password