how do i create an ssis connection

More
7 years 3 weeks ago #9916 by lester
lester created the topic: how do i create an ssis connection
I have followed the instructions in the manual:

1. create new connection - postgressql native provider (bi alias)
2. enter postgres server name
3. the log on box looks wrong it says use windows authentication/use sql server authentication! I want to log in using my postgres user name and password.

Anyway I enter the details in the sql server password box in the absence of anything else that makes sense

no databases are available in the drop down box

test connection fails: test connection failed because of an error in initialized the provider. value does not fall within the expected range.

image:
http://i.imgur.com/KbXBHmh.png
http://i.imgur.com/UGlW1yg.png
http://i.imgur.com/uJRrd7v.png

what am i doing wrong??

Please Log in or Create an account to join the conversation.

More
7 years 2 weeks ago #9917 by Moderator
Moderator replied the topic: Re: how do i create an ssis connection
The steps are correct. The error is very generic and can be caused by multiple reasons. Are you connecting to remote or local Postgres? The Postgres server should be configured to accept remote connections. Firewall should be configured to allows connections. Please refer to Developer's Manual for for details.

What are the SQL Server, Windows and Pgoledb versions?

Please Log in or Create an account to join the conversation.

More
7 years 2 weeks ago #9918 by lester
lester replied the topic: Re: how do i create an ssis connection

Are you connecting to remote or local Postgres?

Remote

The Postgres server should be configured to accept remote connections. Firewall should be configured to allows connections.

It is, I have a linked server set up on the machine and can query it postgres box with no problems

What are the SQL Server, Windows and Pgoledb versions?

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Windows 2008 R2 SP 1

Postgres 8.3.5

Please Log in or Create an account to join the conversation.

More
7 years 1 week ago #9922 by Moderator
Moderator replied the topic: Re: how do i create an ssis connection
In BIDS Connection Managers panel choose New OLEDB Connection...
Select Native OLEDB\PostreSQL Native Provider.
For Server Name specify the remote server name, or IP address.
Specify user name and password.
If Postgres server is running on the non-default port (5432), switch to All panel, and in Extended Properties add PORT=nnnn.
In "database name" specify the database to connect to.
Click Test Connection button.

I recommend running PGNP Profiler and see if it show any errors during connection. Also, inspecting Postgres log may give some information.

Please Log in or Create an account to join the conversation.

More
7 years 1 week ago #9924 by lester
lester replied the topic: Re: how do i create an ssis connection
that works great,

I was selecting Native OLEDB\PostreSQL Native Provider (BI Alias) which was not working for me

Please Log in or Create an account to join the conversation.

Time to create page: 0.079 seconds
Powered by Kunena Forum