EDIS Online User Guide
To use EDIS to connect to a database for performing imports, exports, and other operations, you need to setup a service ID. A service ID is a named alias to use with EDIS tasks to access databases at run time. What’s great about service ID’s is that once the service ID is setup, the developer just needs to reference the alias and does not need the credentials. And if you need to update a password to a service ID, you simply run the service ID configuration proc (below) again.
When a new database connection is created, it is by default accessible to all users in the EDIS_Role. To lock to a specific ID, group, or set of ID’s, see section Multi-Tenant Support.
Configuring Database Service ID’s
Stored Procedure: SSISDB.EDIS.usp_config_db_conn
|@server_id||Alias you want to use to access the database.||None; Required|
|@server_platform||The database server platform i.e. MSSQL, ORACLE, TERADATA, IBM DB2||Null|
|@server_provider||The provider technology to use to connect to the database. Options are ODBC and OLEDB||Null|
|@use_ado_net||Flag indicating if you want to use ADO.NET to connect to the database.||0 (False)|
|@cn_str||Connection string used to connect to the database.||Null|
Example 1: Adding an OLEDB Server connection
Example 2: Adding an ODBC Server Connection
Example 3: Adding an ADO.NET, ODBC Server Connection
Example 4: Adding an ADO.NET, OLEDB Server Connection
Creating SQL Server Specific database connections
EDIS makes connecting to other SQL Servers with a Windows user ID simple and easy. Below is the configuration task to create a SQL Server connection with a windows ID.
When you create a service ID for SQL Server that connects using a Windows user ID, a credential and proxy ID are created on the host SQL Server to support this ID. Additionally, if the Windows ID is not listed as a login for the SQL Server, it is added to ensure the ID can connect at runtime.
Stored Procedure: SSISDB.EDIS.usp_config_db_conn_mssql
|@server_id||Alias to use when connecting to the SQL Server||None; Required|
|@server_instance||The SQL Server Instance to connect to||None; Required|
|@default_db||default database for the ID to connect to||master|
|@use_integrated_security||Flag indicating if you want to use integrated security (windows authentication) to connect to the SQL Server.||1 (True)|
|@sql_auth_user_id||SQL Server user ID if using SQL authentication to connect to the server. This argument is only applicable if you are not using integrated security to connect.||Empty String|
|@sql_auth_password||SQL Server user password if using SQL authentication to connect to the server. This argument is only applicable if you are not using integrated security to connect.||Empty String|
|@windows_auth_user_id||Windows login ID to connect to the SQL Server. This argument is only applicable if you are using integrated security to connect.||Empty String|
|@windows_auth_password||Windows login ID pasword to connect to the SQL Server. This argument is only applicable if you are using integrated security to connect.||Empty String|
Example 1: Creating a SQL Server service ID using Windows Authentication
Example 1: Creating a SQL Server service ID using SQL Authentication