Using SSIS variable with ODBC Progress

[Dominique]

hello,
I'm using SQL Server 2005 - SSIS module to extract data from a progress database. It works fine except when I want to use a variable in my request like
select * from pub.vkorder_intake where datum_creatie=
@[User::StartDate]. I got an error message 10713 saying there is a syntax error in sql statement. I've tried everything without success. It works fine without variable.
Can anyone help? Thanks in advance

 

[doom1701]

I didn't know you could do that, no matter what the source. I could be wrong, but if I'm not, here's how I do it:

• DataReader Source, setup with a SQL Statement without my Where clause (so it can get the field layout).
• I create a project wide variable to hold my SQL statement
• On the Control Flow tab, I add a script task before my data flow task. I make both my SQL variable and the variable I need for the Where clause available to the script (SQL variable:Read/Write)
• In the script, I build my complete SQL statement (I try to have the bulk of the statement in the variable already, and append the Where clause to the end, or use the .NET Replace string function) and plug it into the variable.
• Then click on the Data Flow Task and go to "Expressions" in Properties. Expand it and the first option will probably be the SQL Statement property for your Datareader source. Set it to your SQL variable.

It seems a little convoluted, but that's what I've had to do even for SQL Server data sources. If you can use an OLE DB Reader Source (you can with SQL, not sure about Progress) it's a little easier, because you can assign the SQL command in that directly to a variable. It would make sense to be able to use a variable in the SQL command property itself, but this was the solution for it that my research found.

 

[Jambobob]

I am trying to set up an extract from Progress to SQL Server 2005 using SSIS. I am using the following connection:

Dsn=OpenaccLIVE;Driver={DataDirect 4.1 32-bit Progress SQL92 v10.1a};uid=*****;pwd=*****;host=*****;port=5016;db=openacc;authenticationmode=Trusted_Connection

The SQL statement reads:

SELECT * FROM PUB.nl_trans

I get the error:

ERROR [42S02] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Table/View/Synonym not found (7519) (pgoe1021.dll)

Any ideas on what I am doing wrong (I am new to SSIS and Progress so sorry if the answer is very basic).