.NET Provider for SAP and variables

Hi,

I am using .NET Provider for SAP to import data from SAP BW to the SQL-Server 2005.

The OLE-DB source has an sql command like this

"exec Z_SQL_EXPORT @.I_WEEK_FROM='200601', @.I_WEEK_TO='200602'

I want to make this command more flexible by using a variable strWEEK_FROM and strWEEK_TO.

How can I do this?

Thank you for yout help.

Gerd

Two methods, expressions or OLE-DB parameters.

You can use a ? as the placeholder, so like this -

exec Z_SQL_EXPORT @.I_WEEK_FROM=?, @.I_WEEK_TO=?

Then go to the parameters dialog in the source and map the variables to the placholders.

This is a better method, I think, more secure for a start, but does not always work due to what the parameter is or the OLE-DB provider.

or

Create a variable and set EvaluateAsExpression to true.

Set the expression on the variable to build the string you require, perhaps something like this, but do some proper date formatting -

"exec zsqlexport @.week = '" + (DT_WSTR, 10)@.[Varname] + "'"

Change the source query type to be SQL Command from variable.

|||

Thanks DarrenSQLIS for your answer.

I found similar informations on other web-sites.

When I try to use your first possibility and click OK in the dialog box then I receive the response

"Error in data-flow-task: An error occured...".

How can I "map the variables to the placholders"?

Then I treid the second solution, but with the oledb-data-provider for sap I found no possibibilty to change the source type to SQL command from variable.

Can you help me again?

Thanks.

Gerd.

|||

Sorry, my mistake. The SAP provider is a ADO.Net managed provider, so of course you use the DataReader Source which does not have the same options as the OLE-DB Source.

There is no parameter support, but you can use an expression. Rather than using a variable, you can set a property expression actually on the SqlCommand property for the DataReader source.

To set an expression a component property (not all component properties upport though however), you need to be in the context of the data flow. Either select the task in Control View, or just click on a clean area of the Data Flow design surface, and then look at the Properties grid, showing the properties of that task. Now select Expressions from the properties grid. You can then select the SqlCommand property for the instance of the data reader source you wish to work against.

|||

Thanks again,

this works!

For those who have a similar problem, I found an article that give a detailled description on how to work with datareader expressions and sqlcommand:

http://www.vsteamsystemcentral.com/cs/blogs/applied_team_system/archive/2006/5/7.aspx