Finish connections and passwords

The importer creates one Hop connection for every database connection manager (OLE DB, ADO.NET or ODBC), in the package or in a project .conmgr file, in metadata/rdbms/. Every task and component that used the connection manager refers to it. Connection managers with the same name and settings share one Hop connection across packages; one with the same name but other settings gets the package's name added, so connections never overwrite each other. File, SMTP and other connection managers get no Hop connection: the components that use them carry the settings themselves.

1. See what needs attention

The report's Database connections section lists every connection it created, its Hop database type, the connection manager it came from, and what to do on each:

The report says	Why	What you do
SSIS does not store the password in clear text: set the variable SSIS_<CONNECTION>_PASSWORD	The connection manager logs in with a user name. SSIS keeps the password out of the package, or encrypted with a key or password the importer cannot use	Set the variable (step 2)
Windows authentication (Integrated Security)	The connection manager logs in as the Windows user that runs the package	Switch the connection to a login, or enable integrated security (step 3)
Named … in Hop: … is not a valid file name	SSIS's default names (SERVER\INSTANCE.Database) are not valid file names, and Hop finds a connection by its file	Nothing: every reference uses the new name
ODBC connection: written as a Generic database connection	ODBC has no direct Hop equivalent	Set the JDBC URL and driver class
… provider … has no Hop database type	An OLE DB provider other than SQL Server, Oracle or DB2, or an ADO.NET provider other than SQL Server, Oracle, PostgreSQL or MySQL	Set the JDBC URL and driver class of the Generic connection
The connection is configured by property expressions	SSIS sets the connection string at run time; the Hop connection has the design-time values	Use variables in the Hop connection for the parts that change

2. Set the password variables

A connection that logs in with a user name has ${SSIS_<CONNECTION>_PASSWORD} as its password, the connection manager's name in capitals, with each run of other characters than letters and digits replaced by _: a connection manager Warehouse gives ${SSIS_WAREHOUSE_PASSWORD}. Give the variable its value in each environment, or from the environment of the process that runs Hop.

Don't type the password into the connection instead: the connection file would then hold it, and Hop's Encrypted prefix in that file is obfuscation, not encryption. Do the same for user names and hosts that differ between environments.

3. Replace Windows authentication

The importer leaves the user name and password of a Windows-authenticated connection empty. Either:

• switch the connection to a SQL Server login, with a variable for its password as in step 2; or
• enable integrated security on the Hop connection. That needs the Microsoft JDBC driver's authentication library, and a Windows host or Kerberos for the account that runs Hop.

4. Check the host

The host comes from the connection manager's data source: a port after a comma becomes the port (db01,1450), . and (local) become localhost, and a named instance stays on the host (db01\SALES), which the Microsoft JDBC driver accepts. SQL Server connections get port 1433 when none is given.

5. Test

Use Test in the connection dialog, with the environment you are going to run with selected. For a Generic connection, put the driver jar in <hop>/lib/jdbc on every machine that runs the pipelines.