February 4, 2019 at 7:43 am
On a SQL Server 2016 server (not my local machine), where do I need to install a vendor's ODBC and/or ADO.NET drivers for use in a SQL Agent job that runs an SSIS package using these drivers? I installed them on my local machine and was able to get Visual Studio 2015 to recognize and successfully use these drivers when designing an SSIS package, and they were also installed on the SQL Server box. However, when the SSIS package was deployed to the SQL Server (into the SSISDB) and used in a SQL Agent job, they don't seem to be recognized because using any of the vendor's drivers in a job that runs the SSIS throws an error "Exception has been thrown by the target of an invocation.". We did set up a System DSN through ODBC admin, and it did test successfully, but still the same error. On the SQL Server, the dl's were copied to C:\Program Files\Microsoft SQL Server\130\DTS\PipelineComponents and C:\Program Files (x86)\Microsoft SQL Server\130\DTS\PipelineComponent. Wondering if there is some other place these need copied to on the server? They are also not visible when trying to use them in the import/export wizard on the SQL Server, nor are they available when trying to set up a linked server. Any help is appreciated!
February 4, 2019 at 11:15 am
there is no particular place to install the drivers. some will default to a hardcoded path (normally C:\program files (or x86)) others will allow you to select where to do it - but the location on this case does not matter for SQL Server (unless you working with clusters where you need to install it on all nodes).
you just need to ensure that the full version of the said driver is installed through the standard setup (not copy files!!!) and that it matches the bitness of the sql server - on this case your odbc drivers need to be 64 bit if you wish to ensure a smooth operation.
if driver is only to be used by SQL Server Jobs SSIS (not sure if ssisdb will work with 32bit drivers) then you can also set the job to work with 32bit ssis if the only version of that odbc is 32bit.
it may help if you state what is the odbc driver for (vendor and version) and also check yourself if you have the correct 32/64bit setup including the sql server/ssisdb package execution.
February 4, 2019 at 12:35 pm
It may also be necessary for the folder where they were installed to be in the PATH environment variable.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 4, 2019 at 1:21 pm
It's Sybase ASE. I downloaded and installed their latest version of their 64-bit SDK (16.0) and it works fine in Visual Studio 2015 in an SSIS package on my local machine but installed the same SDK on the server and can't get the SSIS package to run either by itself (right-click execute in SSMS) or in an Agent job. Tried verbose logging and it's just not saying much other than the below. That was with their ADO.NET driver. So I set up a 64-bit System DSN to use their ODBC driver and it set up successful in ODBC admin, but using it in the SSIS and running on the server failed with same thing. Contacted SAP for help and only get sporadic help that is not really helpful at this point.
(server and account names changed to protect the innocent)
OnPipelinePreComponentCall,sql_srvr_name,xxxxxx\proxy_acct,Data Flow Task,{D2FF0573-C622-49FF-BB15-179DC1B6DC69},{62C53832-C1BE-4B3C-B3E6-B51198BBA9E0},02/04/2019 10:57:53 AM,02/04/2019 10:57:53 AM,0,0x,Data flow engine will call a component method. : 2 : ADO NET Source : AcquireConnections
Diagnostic,sql_srvr_name,xxxxxx\proxy_acct,sybase_srvr.report1,{85333B85-0A64-4822-86E0-255C0A059742},{62C53832-C1BE-4B3C-B3E6-B51198BBA9E0},02/04/2019 10:57:53 AM,02/04/2019 10:57:53 AM,0,,ExternalRequest_pre: The object is ready to make the following external request: 'IDbConnection.Open(ConnectionType: Sybase.Data.AseClient.AseConnection, Sybase.AdoNet45.AseClient, Version=16.0.1.2, Culture=neutral, PublicKeyToken=95d94fac46c88e1e
ConnectionString: server=sybase_srvr;user id=xxxx;initial catalog=myDB;EncryptPassword=1;port=5000;Persist Security Info=True;quotedidentifier=True;enabletracing=False;dynamicprepare=True;)'.
Diagnostic,sql_srvr_name,xxxxxx\proxy_acct,sybase_srvr.report1,{85333B85-0A64-4822-86E0-255C0A059742},{62C53832-C1BE-4B3C-B3E6-B51198BBA9E0},02/04/2019 10:57:53 AM,02/04/2019 10:57:53 AM,0,,ExternalRequest_post: 'IDbConnection.Open failed'. The external request has completed.
OnError,sql_srvr_name,xxxxxx\proxy_acct,Data Flow Task,{D2FF0573-C622-49FF-BB15-179DC1B6DC69},{62C53832-C1BE-4B3C-B3E6-B51198BBA9E0},02/04/2019 10:57:53 AM,02/04/2019 10:57:53 AM,-1071610807,0x,ADO NET Source has failed to acquire the connection {85333B85-0A64-4822-86E0-255C0A059742} with the following error message: "Exception has been thrown by the target of an invocation.".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply