SSIS 2008

[shoubs]

There doesn't seems to be a topic on SSIS 2008, so I am starting one as I have ran into a few problems.

We are attempting to use SSIS 2008 to maintain a data warehouse between two Postgres db's, one is OLAP, and one is OLTP. Pretty standard stuff.
We're using the latest trial version to see if the provider is worth purchasing. Unfortunately, I have ran into a fundamental problem regarding parameterised queries.
For example, when creating an OLEDB source, I want to (simplified):

SELECT col1, col2, col3 FROM Customers WHERE col4 = ?

This works find in the query builder when I click run, a dialog appears asking me what the parameter should be and the data comes back as expects. However, when I come out of the query builder, I have my query all ready to go in the text box - I want to set the parameter to a variable. I click on the parameters button, and I get the following error:

“The SQL statement does not contain any parameters”

If I use the SQL server database and it’s OLEDB provider, I don’t get this problem.
Similar problems happen elsewhere, e.g. with “execute sql task” and “oledb command”, but we’ll tackle them later...
Has anyone else come across such problems, how have you worked around them?

I’ve done data warehousing before between two SQL servers and it works like a dream. It’s a shame we can’t get this to work. PLEASE HELP!

[shoubs]

Looks like I need to use an SQL Varible which is an expression instead?

bit poor,

[Moderator]

I have reproduced and fixed the bug. Please use build 1.3.0.2094 with the fix (it can be obtained from the downloads page).

Thank you for letting us know!

[shoubs]

Brillient, thank you. - that fixes the problem ^_^
That also fixed the parameter/column mapping problem with the OLE DB command.
Not a major problem - the the preview button doesn't work (on the oledb source).

We need to do a lot of updates, I'd rather not do this row by row, does anyone know of any good resources to achieve this - I'm new to postgres, but basically I want to do something like this (which works within the same db):

update tableB b set
b.col = a.col, etc
from tableA a
where a.id = b.id

but between servers (in Sql server this idea is call a linked server).

[Moderator]

The preview button did not work because pParams argument to command call was equal to null and provider did not check for that condition. I have added that check to build 2095.

The updates to a linked server table can be done with syntax you mentioned but an "inner join" has to be added to the statement as shown below:
update LnkSvrTbl
set LnkSvrTbl.strcol = LocalSvrTbl.strcol
from test LocalSvrTbl
inner join PGNPWW.postgres.[public].test LnkSvrTbl on (LocalSvrTbl.id = LnkSvrTbl.id)
where LocalSvrTbl.id = LnkSvrTbl.id

The build 2094 has a bug that results in following error:
OLE DB provider "PGNP" for linked server "PGNPWW" returned message "Row cannot be located for updating".
Msg 7343, Level 16, State 4, Line 1
The OLE DB provider "PGNP" for linked server "PGNPWW" could not UPDATE table "[PGNPWW].[postgres].[public].[test]".

We'll try to fix it and we'll send you a new build soon.

[shoubs]

Coming from a Sql Server background, I prefer using the inner join syntax, and I would use that as it looks neater, but when I tried it has an exception. is this what you mean?
I'm transferring data postgres to postgres (physically seperate) first to archive, then to build a data warehouse, I'm only using SQL server for SSIS, nothing else. It would seem you can't do cross db queries the same way you can in SQL server - and I don't really want to use the linkName.dbname.schema.table as in the past I've seen pretty bad performance - it's adding another layer in, (I've always populated table varibles or temp tables using openQuery), so we're just using straight ole db using your provider.

The only way I could think of doing what I want is to set up duplicate "temp" tables and insert all the changed and new data into that, then run my set based update and insert on the existing archive table based on the data in the temp tables (which arn't actually temp tables - I would have done select into temp tblName but its eaiser just to have the schema set up for you to insert into).

Thanks for sorting these things out, we need to test everything we want and ensure it all works.

[shoubs]

I think I found another bug (whose those, I am unsure): -

When I am doing an insert query, and click parse query, BID's crashes. Yay.

and we can't seem to perform a multiple statement query in the SQL task - e.g. do an insert then select currval('seqName')
says here http://msdn.microsoft.com/en-us/library/ms141003.aspx however that the select should come first which is no good in this case so put it as a seperate sql task and set retain same connection to true, which I am hoping doesn't cause performance problems with other parralelled tasks. (could put this in a function instead - which I'd rather avoid).

[shoubs]

Biggest problem I'm having is getting this test package (and it's children) to run on the production server - Note I can run it using execute package and I have played with the various package protection level property.

Every time I restart the computer, SSIS seems to loose the password information on the connection.
This also happens if I open the connection manager for that particular connection - password exists in the properties - though this I think is by design.

This error below can replicated in the dev environment (BIDS) by changing package protection level to "don't save sensative"

Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2009-10-13 16:40:00.89
Code: 0xC0202009
Source: Audit Connection manager "Warehouse"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "PostgreSQL Native Provider" Hresult: 0x80004005 Description: "Cannot open DB connection.". An OLE DB record is available. Source: "PostgreSQL Native Provider" Hresult: 0x00000000 Description: "fe_sendauth: no password supplied ". End Error Error: 2009-10-13 16:40:00.90
Code: 0xC00291EC
Source: PopulateVars Execute SQL Task
Description: Failed to acquire connection "Warehouse". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 16:40:00 Finished: 16:40:00 Elapsed: 0.711 seconds. The package execution failed. The step failed.
00:00:03

[Moderator]

> you can't do cross db queries the same way you can in SQL server
Yes, Postgres does not support cross DB queries.

> I don't really want to use the linkName.dbname.schema.table as in the past I've seen pretty bad performance
Sure, it is not performing fast in current implementation. However, there is a lot to improve. Similiarly to bulk INSERT, provider can support some kind of bulk UPDATE. We will definitely add this capability to future versions of the provider. In the mean time the most efficient way would be to use two OLEDB connections, create temp table and perform UPDATE with INNER JOIN as you said. For inserting large number of rows you can use COPY since it is very fast in Postgres. We are adding COPY support into provider now, so it will be available soon.

> When I am doing an insert query, and click parse query, BID's crashes.
I cannot reproduce it in build 2096. Could you try it with www.pgoledb.com/downloads/PGNP32-1.3.0.2096.exe and send us the insert statement if it still crashes?

> we can't seem to perform a multiple statement query in the SQL task
Let me experiment with this. I'll let you know.

[Moderator]

> Every time I restart the computer, SSIS seems to loose the password information on the connection...
I have reproduced the error - it happens because SSIS passes empty password to provider. Looks like DontSaveSensitive option should not be used (unless you want to re-enter password every time).