question

HSever-0909 avatar image
0 Votes"
HSever-0909 asked HSever-0909 rolled back

OLE connection manager with driver installed on another server than SSIS

I need to do a 100 GB data pull from DB2. Our db/integration server is SQL/SSIS 2014, but do not have maintenance window to install MS OLE DB2 driver soon on the db/integration server. I found though the MS OLE DB2 driver is installed on a file server in same environment. In the data flow while pulling the data, I cannot store intermediate data - due to sensitivity – it needs to be pulled directly.
I tried if there is any way to pull data directly from DB2 source to our db/integration server using the driver from this file server (while creating the data source connection manager) but could not.

Wondering if is possible and you see any solution.

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered HSever-0909 rolled back

Hi @HSever-0909,

Thanks for your posting.

Is it possible to install ODBC? If yes, you could refer to ETL Method – Fastest Way To Get Data from DB2 to Microsoft SQL Server. The other way is using the SQL Gateway and the ODBC Driver to set up a linked server for DB2 data, please refer to Connect to DB2 Data as a Linked Server.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Carrin, thank you for replying,

no, unfortunately I am not able to install any driver (OLE, ODBC) on the database/Integration server anytime soon, since do not have window available for this. But i was asked to run the one time import and this was why i started this thread.

0 Votes 0 ·

Hi @HSever-0909, sorry, based on my research, I cannot found any way about connecting DB2 and SQL Server without install OLEDB or ODBC. And you also said that cannot store intermediate data because of the sensitivity. Maybe you should discuss with your colleagues or clients to get the permission to install ODBC or OLEDB.

1 Vote 1 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered HSever-0909 commented

but do not have maintenance window to install MS OLE DB2 driver

I installed not MS, but origin IBM OLE DB2 data provider several times on different server, a restart was never required and so no maintenance window.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Olaf for the input, will not be allowed any change on the environment for more than a month, but was still asked to pull the data, will look for an alternate solution

0 Votes 0 ·