Page 1 of 1

FAQ: MS SQL Business Intelligence

PostPosted: 20 Dec 2007, 12:31
by 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

Re: FAQ: MS SQL Business Intelligence

PostPosted: 16 Apr 2013, 14:54
by Kovalenko Dmitry
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"