how do i create an ssis connection

Using PGNP with MS SQL Server, Data Transformation Services (DTS), SQL Server Integration Services (SSIS), Linked Servers, Snapshot and Transactional Replication and more.

how do i create an ssis connection

Postby lester » Fri May 03, 2013 2:13 am

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??
lester
 
Posts: 3
Joined: Fri May 03, 2013 2:09 am

Re: how do i create an ssis connection

Postby Moderator » Mon May 06, 2013 9:02 am

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?
User avatar
Moderator
Site Admin
 
Posts: 293
Joined: Wed Oct 29, 2008 11:27 pm

Re: how do i create an ssis connection

Postby lester » Mon May 06, 2013 10:40 am

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
lester
 
Posts: 3
Joined: Fri May 03, 2013 2:09 am

Re: how do i create an ssis connection

Postby Moderator » Tue May 14, 2013 8:12 pm

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.
User avatar
Moderator
Site Admin
 
Posts: 293
Joined: Wed Oct 29, 2008 11:27 pm

Re: how do i create an ssis connection

Postby lester » Wed May 15, 2013 4:35 am

that works great,

I was selecting Native OLEDB\PostreSQL Native Provider (BI Alias) which was not working for me
lester
 
Posts: 3
Joined: Fri May 03, 2013 2:09 am


Return to SQL Server/DTS/SSIS/Linked servers/Replication

Who is online

Users browsing this forum: No registered users and 0 guests

cron