FAQ: MS SQL Business Intelligence

[Merkulov Andrew]

Hello! In this topic we will be collect frequency asked question related to Business Intelligence (SSIS, SSRS, SSAS) and IBProvider

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.
First Solution:
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:
http://www.ibprovider.com/download/Samples/SSISOleDbDestinationExample.zip

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