Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
loudbliss
Starting Member
37 Posts |
 Posted - 2008-06-05 : 13:05:11
|
| Hi,Im working with SSIS and im trying to get some data from SAP using the .net Data Provider for mySap.The problem is that i want do to a query where the where values changes, that is, to use parameters in the select statement. The way to use parameters here is the SAPParameters but i haven't been able to find out how to create/use them in SSIS.Has anybody used this or know how to work with them?Thx. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-06 : 13:40:09
|
| http://www.simple-talk.com/sql/sql-server-2005/executing-ssis-packages-/ |
 |
|
|
loudbliss
Starting Member
37 Posts |
Posted - 2008-06-20 : 09:40:39
|
| Hi Guys, im back with the answer to my question.The White Paper of .net Data Provider for mySap has a code that shows you how to extract data from Sap to SSIS with a query that has parametres (SapParametres)The thing is you cannot use a standard SSIS connection (destination) and link it to the mySap Provider because there you are only allowed to write standard querys w/0 parametres (select * from table)If you want to to a query with parametres like "select * from table where date = @yesterdaydate you have to go to the script component and add a code like this which you can find in the White Paper: Dim yesterdayDate As System.DateTime = DateTime.Today.AddDays(-1) Dim sapCmd As IDbCommand Dim sapRdr As IDataReader Dim sapConnection As IDbConnection sapConnection = CType(Connections.Connection.AcquireConnection(Nothing), IDbConnection) sapCmd = sapConnection.CreateCommand() sapCmd.CommandText = "Select VBELN as ID,POSNR as ItemID,MATNR as ProductID,ARKTX as Item,KMPMG as Quantity,NTGEW as Weight,VOLUM as Volume,NETWR as Value,NETPR as Price from VBAP WHERE DATE = '" & yesterdayDate & "'" sapCmd.Prepare() sapRdr = sapCmd.ExecuteReader() While (sapRdr.Read()) Output0Buffer.AddRow() Output0Buffer.ID = sapRdr.GetString(0) Output0Buffer.ItemID = sapRdr.GetString(1) Output0Buffer.ProductID = sapRdr.GetString(2) ' And all other column you are reading in the select statement End WhileAs you can see now i can run this package daily and it will only load the data from the date before.You have to remember that you have to add and output column in the Script Transformation Editor of the Script Component for each column you are calling in your select statement (if you dont do that you wont have to column available as an output in your DataFlow)The .Net Data Provider for mySap can be downloaded from http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=enIt comes with the White Paper Mentioned before (the pges with the code are 23 & 24 )Any help ill be here for a while. |
|
|
|
|
|
|
|
|
Working with SAP and SAPParameter