While gathering data from different sources and writing it to a destination, connection managers are helpful. Connection manager facilitates the connection to the system that include information’s like data provider information, server name, authentication mechanism, database name, etc.
What will be your first approach if the package that runs fine in Business Intelligence Development Studio (BIDS) but fails when running from an SQL agent job?
The account that runs SQL Agent Jobs might not have the required permission for one of the connections in your package. In such cases, either you can create a proxy account or elevate the account permissions.
Variable in SSIS is basically used to store values. In SSIS, there are two types of variables system variable and user variable.