June 5, 2008 at 11:05 am
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.
June 6, 2008 at 1:13 am
Hey,
The only time I queried SAP is through Datastage Parallel Extender which pretty much wrote the ABAP for me.
However, there is an interesting article that may assist you
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2368202&SiteID=1
If you do get it right, please post what you did
~PD
June 20, 2008 at 7:43 am
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 While
As 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=en
It comes with the White Paper Mentioned before (the pges with the code are 23 & 24 )
Any help ill be here for a while.
Go to Top of Page
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply