Integration Import Script Example

-------------------------------------------------------------------
Function SQLIntegration(strLoc, lngCatKey, dblPerKey, strWorkTableName)
‘------------------------------------------------------------------
‘ FDM Integration Import Script: 
‘Created By:  	Admin
‘Date Created: 04/19/2004 2:18:39 PM
‘Purpose: This import integration script connects to the sample 
‘		Northwind SQL Server database and imports sample 
‘		source data from the Orders table into FDM.
‘-----------------------------------------------------------------
Dim cnSS		‘ADO connection object
Dim strSQL 	‘SQL string
Dim rs   	‘Source system recordset
Dim rsAppend 	‘ FDM recordset

‘Initialize ADO objects
Set cnSS = CreateObject(“ADODB.Connection”)
Set rs = CreateObject(“ADODB.Recordset”)

‘Open  FDM work table recordset for appending
Set rsAppend = DW.DataAccess.farsTableAppend(strWorkTableName)

‘Connect to Northwind SQL Server database (our data source)
Dim strConn
strConn=”Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;”
strConn=strConn & “Initial Catalog=Northwind;Data Source=LocalHost;”
cnss.open strConn

‘Create source query string
strSQL = “Select * “
strSQL = strSQL & “FROM Orders “

‘Open source recordset
rs.Open strSQL, cnSS

‘Check for data in source system
If rs.bof AND rs.eof Then
‘Give error message
	RES.PlngActionType = 2
RES.PstrActionValue = “No records to load!”
	‘Assign Return value of function
	SQLIntegration = False
	Exit Function
End If

‘Loop through source records in Northwind database (Orders table) and append to FDM work table
If Not rs.bof And Not rs.eof Then
	Do While Not rs.eof
		rsAppend.AddNew
		rsAppend.Fields(“PartitionKey”) = RES.PlngLocKey
		rsAppend.Fields(“CatKey”) = lngCatKey
		rsAppend.Fields(“PeriodKey”) = dblPerKey
		rsAppend.Fields(“DataView”) = “YTD”
		rsAppend.Fields(“Amount”) = rs.fields(“Freight”).Value
		rsAppend.Fields(“Account”) = rs.fields(“CustomerID”).Value
		rsAppend.Fields(“Entity”) = rs.fields(“ShipCountry”).Value
		rsAppend.Fields(“Desc1”) = rs.fields(“ShipName”).Value
		rsAppend.Update
		rs.movenext
	Loop
End If

‘Give success message
RES.PlngActionType = 2
RES.PstrActionValue = “SQL Import successful!”

‘Assign Return value
SQLIntegration = True

End Function
--------------------------------------------------------------------