| Author |
Topic |
|
asbharadwaj
Starting Member
27 Posts |
 Posted - 2010-01-06 : 21:32:12
|
| Hi All,I am trying to connect to SAP and execute a BAPI using SSIS.And the BAPI accepts a parameter which is sysdate.I need to pass this dynamically.I have declared a variable in the Task scope and am assigning the sysdate to the variable.And when I am passing the variable to the Sql command in the Datareader Source it throws an error.Any Ideasexec BAPI_PATIENT_DETAILS @P_DATE='20100107'I want it to be exec BAPI_PATIENT_DETAILS @P_DATE=@sysdate where @sysdate is the variable that I declared.Please adviceCheersSridhar |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-07 : 00:55:13
|
| what happens when you pass @sysdate directly? whats the datatype of @sysdate? what is the error you got? |
 |
|
|
asbharadwaj
Starting Member
27 Posts |
Posted - 2010-01-07 : 00:57:57
|
quote:
Originally posted by visakh16
what happens when you pass @sysdate directly? whats the datatype of @sysdate? what is the error you got?
Hi visakh,Thanks for your reply.In either the cases this is the error that I am encountering "Error at DataReader Source 1 : An error Occured executing the provided SQL Command" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-07 : 00:59:03
|
| you're giving this inside ssis oledb command right? |
|
|
|
asbharadwaj
Starting Member
27 Posts |
Posted - 2010-01-07 : 01:07:31
|
quote:
Originally posted by visakh16
you're giving this inside ssis oledb command right?
Yeah..actually,I have a control flow in which there is a data flow and in the Datareader Source..in the Sqlcommand field I am trying to execute this statement "exec BAPI_PATIENT_DETAILS @P_DATE='20100107'"I have declared the variable(@Sysdate) in the Dataflow Scope as string.I even tried assigning the variable with "@[User::Sysdate]= YEAR( GETDATE() ) + MONTH( GETDATE() )+ DAY( GETDATE() )" so that I can get the value..Am I doing something wrong?Please advice |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-07 : 01:19:28
|
| why are you declaring as a string? use DB_DATE or DB_TIMESTAMP. Also are you giving parameter placeholder with ? and mapping the value in parameter mapping tab? |
|
|
|
asbharadwaj
Starting Member
27 Posts |
Posted - 2010-01-07 : 01:28:35
|
quote:
Originally posted by visakh16
why are you declaring as a string? use DB_DATE or DB_TIMESTAMP. Also are you giving parameter placeholder with ? and mapping the value in parameter mapping tab?
Do we have paramter mapping in Datareader Source?Can you tell me..I dont find any |
|
|
|
asbharadwaj
Starting Member
27 Posts |
Posted - 2010-01-07 : 01:32:46
|
quote:
Originally posted by asbharadwaj
quote:
Originally posted by visakh16
why are you declaring as a string? use DB_DATE or DB_TIMESTAMP. Also are you giving parameter placeholder with ? and mapping the value in parameter mapping tab?
Do we have paramter mapping in Datareader Source?Can you tell me..I dont find any
Do you want me to use Execute SQL Task?? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-07 : 01:33:46
|
| oops I thought you're using execute sql task. cant you use that instead? |
|
|
|
asbharadwaj
Starting Member
27 Posts |
Posted - 2010-01-07 : 01:36:09
|
quote:
Originally posted by visakh16
oops I thought you're using execute sql task. cant you use that instead?
I can..but again the problem is how do I pass this value to the Datareader Source..and especially to the exec...statement?? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-07 : 01:41:37
|
| sorry i cant understand necessity of datareader source? what does procedure return? how will you be using the data? |
|
|
|
asbharadwaj
Starting Member
27 Posts |
Posted - 2010-01-07 : 01:51:27
|
quote:
Originally posted by visakh16
sorry i cant understand necessity of datareader source? what does procedure return? how will you be using the data?
Data Reader Source is used to execute the BAPI in SAP.Once SAP returns certain values I need to insert that into my local DB.I am using the OLEDB Destination for this.If I hardcode the date value everything works fine.But I need to schedule this Package.So I need to pass the calues Dynamically.I hope you got the scenario |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-07 : 02:00:59
|
quote:
Originally posted by asbharadwaj
quote:
Originally posted by visakh16
sorry i cant understand necessity of datareader source? what does procedure return? how will you be using the data?
Data Reader Source is used to execute the BAPI in SAP.Once SAP returns certain values I need to insert that into my local DB.I am using the OLEDB Destination for this.If I hardcode the date value everything works fine.But I need to schedule this Package.So I need to pass the calues Dynamically.I hope you got the scenario
Ok is it not possible to execute BAPI from execute sql task? |
|
|
|
asbharadwaj
Starting Member
27 Posts |
Posted - 2010-01-07 : 02:08:20
|
quote:
Originally posted by visakh16
quote:
Originally posted by asbharadwaj
quote:
Originally posted by visakh16
sorry i cant understand necessity of datareader source? what does procedure return? how will you be using the data?
Data Reader Source is used to execute the BAPI in SAP.Once SAP returns certain values I need to insert that into my local DB.I am using the OLEDB Destination for this.If I hardcode the date value everything works fine.But I need to schedule this Package.So I need to pass the calues Dynamically.I hope you got the scenario
Ok is it not possible to execute BAPI from execute sql task?
I dont think it is..Anyways I would try again..even in that case How do I pass the parameter value to the exec statement.. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
asbharadwaj
Starting Member
27 Posts |
Posted - 2010-01-07 : 22:27:57
|
quote:
Originally posted by visakh16
that you can passseehttp://www.sqlis.com/post/The-Execute-SQL-Task.aspx
Hi Visakh,I tried using Execute SQL Task..but I get this error.."Unable to cast object of type 'System.Data.SqlClient.SqlParameter' to type 'Microsoft.Adapter.SAP.SAPParameter'." |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-08 : 02:32:54
|
| whats the source to execute sql task? |
|
|
|
asbharadwaj
Starting Member
27 Posts |
Posted - 2010-01-08 : 02:53:43
|
quote:
Originally posted by visakh16
whats the source to execute sql task?
I am connecting to SAP server and am using ADO.NET as connection type and Resultset=FullResultSetI am trying to execute BAPI call in the Execute SQL task.I have cerated a variable and am trying to pass it to the SQL Command.Exec BAPI_GET_PAT_DETAILS @P_DATE=@Sysdate;Where @sysdate was declared as a variable at the control flow scope as a string.Later I have mapped under Parameter mapping as input(Direction) |
|
|
|
calaris
Starting Member
1 Post |
Posted - 2010-02-17 : 19:38:47
|
| On the data flow task itself you can click on the expressions button. One of the expressions that's available to set would be the [SAP adoreader object].[SqlCommand].You can use this to set the dynamic sql to contain your sysdate value. |
|
|
|
BobC
Starting Member
1 Post |
Posted - 2010-02-19 : 16:01:15
|
quote:
Originally posted by calaris
On the data flow task itself you can click on the expressions button. One of the expressions that's available to set would be the [SAP adoreader object].[SqlCommand].You can use this to set the dynamic sql to contain your sysdate value.
I'm trying to access an AS400 through an ADO.NET in an ADO. I have changed the property [ADO NET Source].[SQLCommand] to "SELECT * FROM xdb.xtable WHERE xfield > " + (DT_WSTR, 30) @[User::datavalue]But then the data source gets a funky purple triangle in the corner and it says: "Validation errors occurred"The column mappings in the Advanced Editor are all blank.Is there another setting I need to configure? Thanks. |
|
|
|
|
Parameter Passing to a data reader source in SSIS