FAQ: MS SQL Business Intelligence

Discuss the Linked Server tehcnology, MS SQL Server and Business Intelligence environment

FAQ: MS SQL Business Intelligence

Postby Merkulov Andrew » 20 Dec 2007, 12:31

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:

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
User avatar
Merkulov Andrew
Site Admin
Posts: 222
Joined: 17 Nov 2005, 20:15

Re: FAQ: MS SQL Business Intelligence

Postby Kovalenko Dmitry » 16 Apr 2013, 14:54

Hints for work with MSDTS and SSIS

Mapping FIles
1. Check in IBProvider Installation Kit the "Integration Files"
2. Copy xml files from "C:\Program Files\LCPI\IBProvider.3\Integrations\" into:
  1. [MSSQL 2008 - v10]: {Folder with installation of Microsoft SQL Server}\100\DTS\MappingFiles
  2. [MSSQL 2005 - v9]: {Folder with installation of Microsoft SQL Server}\90\DTS\MappingFiles

In these files contain the rules for mapping FB/IB datatypes into MSSQL datatypes.

Increase speed of upload data into Firebird/Interbase database
1. Use normal IBProvider - LCPI.IBProvider.3 (not Lite and not Free)
2. Define in connection string the "modify_trans_type=1"
Kovalenko Dmitry
Site Admin
Posts: 824
Joined: 17 Nov 2005, 20:51
Location: Russia, Lipetsk

Return to Using IBProvider in MS SQL Server

Who is online

Users browsing this forum: No registered users and 0 guests