ODBC Connection fails from SSIS and Excel - SSO Token configuration
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)
1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource
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?
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks @Georgie ,
I created ticket and while we were discussing on the layout:
- Simba driver 64bit mismatch error
- Simba driver 32bit no detail error
- 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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives