DATASOURCE

The very first thing that should be done at the top of the import file (.imp) is to declare the Data sources you want to import-from by using the DATASOURCE command.

Note: You can declare as many datasources as you need in order to create your TARGITDB file.

Note: DOTNETDataSouce and MEDDataSources are covered seperately

Sources can be ODBC, OLEDB, DOTNET or several other sources listed below.

The following can be used in a single .imp file importation.

Syntax:

DATASOURCE Source1 = ODBC 'odbc_connection_X' 
DATASOURCE Source2 = ODBC 'odbc_connection_Y' 
DATASOURCE Source3 = SQLSERVER 'connection_string' 
DATASOURCE Source4 = DOTNET PROVIDER 'System.Data.SqlClient' 'Data Source=localhost;Initial Catalog=yourDB;User Id=your_username; Password=your_password;' 

You can open other locally stored .TARGITDB Data files and import tables or execute sql against them like any other Data Source.

Syntax:

DATASOURCE yourname=LOCAL INMEMORYDB 'LOCAL_FILE_Name'

Example:

/* Connection_string is an ODBC Connection string: */ 
/* dsn=ir_northwind for a system odbc datasource called ir_northind. */ 
/* yourname is the name that you use as an alias for the datasource in IMPORT commands */ 
/* Refer to www.connectionstrings.com for a full reference on odbc connection strings */ 
DATASOURCE yourname=ODBC 'connection_string'

/* Connection_string is an OLE DB Connection string */ 
/* Refer to www.connectionstrings.com for a full reference on oledb connection strings */ 
DATASOURCE yourname=OLEDB 'connection_string' 

/* Connection_string in this case is the connection string for the Sql Server Dot Net Provider for Standard Authentication */ 
DATASOURCE yourname=SQLSERVER 'connection_string' 

/* Note: The Dot Net Provider is the fastest way of connecting to sql server */ 
.DATASOURCE mySQLSource=SQLSERVER ‘Data Source=localhost; Initial Catalog=myDBName;User Id=myUser; Password=myPwd;' 

/* This opens the local data contoso.targitdb as datasource A1 */ 
DATASOURCE A1=LOCAL INMEMORYDB 'contoso.targitdb’

DOT NET Datasource

A Dot Net Data provider source can be set with the keywords DOTNET PROVIDER after the equals symbol. It takes two parameters.

Param 1 is the dot net name of the class

Param 2 is the connection string for the provider

Example:

DATASOURCE a1=DOTNET PROVIDER 'System.Data.SqlClient' 'Data Source=localhost; Initial Catalog=yourDB; User Id=your_username; Password=your_password;'

The Dot Net provider Class needs to be accessible from tiImport/tiImport32

Example:

LOAD ASSEMBLY 'Npgsql.dll' 
DATASOURCE a1 = DOTNET CONNECTION 'Npgsql.NpgsqlConnection'
User ID=your_user_id;Password=your_password;Host=your_host;Port=5432;Database=your_database'

This example loads the Postgres Dot Net driver with the LOAD ASSEMBLY Command, and creates a connection to a Postgres server.

ME Datasource

ME (case unimportant) is a special “built-in” data source that refers to the current InMemory database you are building. ME is not declared at the top of the import file since it always refers to “this current import process”. ME is used with the IMPORT statement when you want to refer to a table or execute a SQL command against a table that you have already imported in this same .imp file import.

DATASOURCE Source1 = ODBC 'odbc_connection_X' 

/* Comments can be entered in an impfile like this */ 
DATASOURCE Source2 = ODBC 'odbc_connection_Y' 

/* Comments can also begin and end with these characters which can be useful if they span more than one line */ 
DATASOURCE Source3 = SQLSERVER 'Provider=SQLNCLI11;
Server=192.168.1.45;Database=ir_dw;Uid=usr1; Pwd=pwd;' 
DATASOURCE yesterdays_targitdb = LOCAL INMEMORYDB 'contoso.targitdb' 
DATASOURCE A1 = ODBC ‘odbc_connection_Z’