SSIS 2008

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!

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

bit poor,

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!

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).

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.

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.