SSIS 2008

More
9 years 10 months ago #103 by shoubs
shoubs created the topic: 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!

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

More
9 years 10 months ago #104 by shoubs
shoubs replied the topic: Re: SSIS 2008
Looks like I need to use an SQL Varible which is an expression instead?

bit poor,

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

More
9 years 10 months ago #105 by Moderator
Moderator replied the topic: Re: SSIS 2008
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!

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

More
9 years 10 months ago #106 by shoubs
shoubs replied the topic: Re: SSIS 2008
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).

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

More
9 years 10 months ago #107 by Moderator
Moderator replied the topic: Re: SSIS 2008
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.

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

More
9 years 10 months ago #108 by shoubs
shoubs replied the topic: Re: SSIS 2008
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.

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

More
9 years 10 months ago #109 by shoubs
shoubs replied the topic: Re: SSIS 2008
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).

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

More
9 years 10 months ago #110 by shoubs
shoubs replied the topic: Re: SSIS 2008
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

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

More
9 years 10 months ago #111 by Moderator
Moderator replied the topic: Re: SSIS 2008
> 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 <!-- w --> www.pgoledb.com/downloads/PGNP32-1.3.0.2096.exe <!-- w --> 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.

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

More
9 years 10 months ago #112 by Moderator
Moderator replied the topic: Re: SSIS 2008
> 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).

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

More
9 years 10 months ago #113 by shoubs
shoubs replied the topic: Re: SSIS 2008
Thanks for your replys. :)

Moderator wrote: > 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).


Yeah, this is by design if you choose DontSaveSensitive, what I meant is - I can't get SSIS to remember the connection password with any of the other protection options when the package is executed on the SQL server though the SQL Server Agent -either scheduled or ran manually. (The same error message - posted above - occurs when you choose dontsavesensitive - so it would appear that the password is never being sent to the provider, regardless of the option you choose)

Also there is possibly a problem when performing a multiple statement query in the SQL task, the task always errors, looks like it doesn't like the semicolon?

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

More
9 years 10 months ago #114 by shoubs
shoubs replied the topic: Re: SSIS 2008
I've tried using a configuration file as well and it doesn't work. I'm not having this problem if I connect oledb to sql server provider.

I have got it to work by encryptsensativewithpassword and sending the password via the commandline.

So all good. I don't get the problem with parsing the query anymore either since upgrading to 2096.

The only outstanding problem I have at the moment is multiple statements in a sql task.

and I dont' really know what you mean by the copy command - it looks like it is for files only - [url:24y9izgs]http://www.postgresql.org/docs/8.0/interactive/sql-copy.html[/url:24y9izgs]

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

More
9 years 10 months ago #115 by Moderator
Moderator replied the topic: Re: SSIS 2008
Current implementation of PGNP provider does not support multiple commands separated with semicolons. We plan to add this support later with a hotfix for 1.3.

Version 1.3 already uses COPY command implicitly for bulk insert via RowsetChange interface. For example, when you have a data flow in SSIS, it will insert rows very fast (>300K rows a second). I plan to add COPY statement support (below) to allow user to use COPY explicitly:

1) load rows from text parameter (ISequentialStream or string)
COPY tbl FROM ? WITH DELIMITER '|'
2) load row from an external file
COPY tbl FROM "C:\Users\vasya\Documents\tbl.csv" ...
3) save rows into output text parameter
COPY tbl TO ? ...
4) save rows to an external file
COPY tbl TO "C:\Users\vasya\Documents\tbl.csv"

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

More
9 years 10 months ago #116 by shoubs
shoubs replied the topic: Re: SSIS 2008
Ok, cool.

I would be very interested in the multi statement command, it would make a vast improvement to individual commands and having to set "RetainSameConnection=True" on the connection object. Which causes problems when async'ing sql tasks using the same connection.

This would be useful as I can return the value of the row's serial id I just inserted.

Worryingly your support email address listed on the site doesn't seem to be working: Send failed due to: Unable to resolve host - pgoledb.com

Also, do you have a date planned for the release of 1.3?

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

More
9 years 10 months ago #117 by Moderator
Moderator replied the topic: Re: SSIS 2008
Ok, we'll add multi-statement support. The "support" e-mail is working now, probably it was a glitch.

Development and beta-cycle for 1.3 are finished. We are preparing web-site and legal at the time. It will be officially anounced on October 26 (or close to that date).

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

More
9 years 10 months ago #118 by shoubs
shoubs replied the topic: Re: SSIS 2008
Cool. Will multi-statement support be included with version 1.3?

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

Time to create page: 0.085 seconds
Powered by Kunena Forum