Integration Script Example

Function SQLIntegration(strLoc, lngCatKey, dblPerKey, strWorkTableName)
‘------------------------------------------------------------------
‘FDM Integration Import Script: 
‘
‘Created By:  	wladmin
‘Date Created: 	04/19/2004 2:18:39 PM
‘
‘Purpose:     Pull data directly from SQL DB     
‘------------------------------------------------------------------
Dim objSS		‘ADODB.Connection
Dim strSQL 		‘SQL string
Dim rs   		‘Recordset
Dim rsAppend 	‘tTB table append rs object
‘Initialize objects
Set cnSS = CreateObject(“ADODB.Connection”)
Set rs = CreateObject(“ADODB.Recordset”)
Set rsAppend = DW.DataAccess.farsTable(strWorkTableName)
‘Connect to SQL Server database
cnss.open “Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=WLDemo;Data Source=DBServerName;”
	‘Create query string
	strSQL = “Select * “
	strSQL = strSQL & “FROM tGL “
	‘Get data
	rs.Open strSQL, cnSS
  
  	 ‘Check for data
	If rs.bof And rs.eof Then
		RES.PlngActionType = 2
    		RES.PstrActionValue = “No Records to load!”
		Exit Function
	End If
  ‘Loop through records and append to tTB table in location’s DB
	If Not rs.bof And Not rs.eof Then
		Do While Not rs.eof
		rsAppend.AddNew
			rsAppend.Fields(“PartitionKey”) = RES.PlngLocKey
			rsAppend.Fields(“CatKey”) = RES.PlngCatKey
			rsAppend.Fields(“PeriodKey”) = RES.PdtePerKey
			rsAppend.Fields(“DataView”) = “YTD”
			rsAppend.Fields(“CalcAcctType”) = 9
			rsAppend.Fields(“Amount”) = rs.fields(“dblAmt”).Value
			rsAppend.Fields(“Desc1”) = rs.fields(“txtAcctDes”).Value
			rsAppend.Fields(“Account”) = rs.fields(“txtAcct”).Value
			rsAppend.Fields(“Entity”) = rs.fields(“txtCenter”).Value
		rsAppend.Update
		rs.movenext
		Loop
	End If
	‘Records loaded
	RES.PlngActionType = 6
  	RES.PstrActionValue = “SQL Import successful!”
‘Assign Return value
SQLIntegration = True
End Function