ODBC Connection fails from SSIS and Excel - SSO Token configuration

oyes704

We use VPN, single sign on from Office to SmartSheet and I got my SmartSheet token generated.

I have ODBC 64bit 1.4.34.0 connector installed and configured with my email and the token.

I tried to connect from MS SQL SSIS 19.1

TITLE: SQL Server Import and Export Wizard

The operation could not be completed.

ADDITIONAL INFORMATION:

ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

Detailed Message Shows:

at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
at Microsoft.SqlServer.Dts.DtsWizard.DTSWizard.GetOpenedConnection(WizardInputs wizardInputs, String connEntryName)
at Microsoft.SqlServer.Dts.DtsWizard.Step1.OnLeavePage(LeavePageEventArgs e)

In Excel if I specify email and Credential Connection it says:

"The connection Striing is invalid. Format of the initialization string does not conform to specification starting at index 0".

If I write credentials token into the password field, it gives architecture mismatch error as above in SSIS.

Does SmartSheet ODBC driver support SSO? Any alternative drivers that do support it?

Georgie

Hi @oyes704,

Yes, the ODBC supports SSO - check out this section of the documentation for how SSO users can connect with an API token.

If you’ve followed all the steps in the documentation and are still running into issues, I’d suggest creating a Support ticket so that this can be investigated further by the relevant team. 

Thanks,

Georgie

oyes704

Thanks @Georgie ,

I created ticket and while we were discussing on the layout:

1. Simba driver 64bit mismatch error
2. Simba driver 32bit no detail error
3. Both Simba driver 32bit and 64bit driver works great!

For SSIS I had to provide complete connection string like: DSN=Smartsheet2;UID={MyEmail};PWD={SSO API Key}

In Alteryx I had to select a table and in the SQL editor remove {MyEmail} prefix in the Table name.

Everything works great now.

Thank you,

Oly