Monday, December 1, 2008 11:13 PM
CFMX and SQL Server Data Transformation Services (Part one)

CFMX and SQL Server Data Transformation Services (Part One of Two)
Author: Tedd Van Diest

So you have a bunch of data that you need to migrate from one source to another and preserve the integrity of your data, or you need to import delimited data from a third party that is in the form of "LARGE" flat file(s) without bringing your server to a halt. Well... the answer is Data Transformation Services. If you have not worked with Data Transformation Service yet, I recommend taking a look into, or wait, why don't I just show you.

So what we are going to do today is use coldFusion to generate some data for our text file and then we are going to use coldFusion & DTS to import that data from the text file into SQL Server.

Requirments:
 » coldFusion MX
 » SQL Server 2000
  1. Create a directory under your web root and name it "cfmxDTS".
  2. Copy and paste the code below into .cfm files and name them with the coresponding listing header.
  3. Open up SQL Server and create a new database and name the database "cfmxDTSDB".
  4. Go to, Start > settings > Control Panel > Administrative Tools > Data Sources (ODBC)
    then click on the Sytem DSN tab > Add > Select SQL Server > Finish then type "cfmxDTSDSN" in the Name
    field, select (local) from the server list click Next > check Windows Authentication > Next > check "Change Default Database to:"
    and select "cfmxDTSDB" from the list click Next > Finish > OK > OK > OK.
  5. Open up coldFusion administrator, got to "Data Sources" then type "cfmxDTSDSN" in the datasource field and select ODBC Socket
    from the Driver list and click add, check Trusted Connection then submit.
Now let us move on to the DTS Package steps.
coldFusion
application.cfm
<cfapplication name="cfmxDTSApp">

<!--- If the application is not "Initialized", Initialize it --->
<cfif not isDefined("Application.isInitialized") or isDefined("url.init")>
<cfscript>
// Config Variable
Application.Config.DSN = 'cfmxDTSDSN';
Application.isInitialized = true;
</cfscript>
</cfif>
template.cfm
<cfswitch expression="#thistag.executionmode#">

<cfcase value="start">
<html>
<head>
<title>CFMX and SQL Server DTS</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
body,table {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 9px;
}
.statsHeader {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 9px;
font-weight: bold;
background-color: #CCCCCC;
}
-->
</style>
</head>
<body>

<table align="center" width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<th colspan="3">CFMX and SQL Server DTS</th>
</tr>
<tr>
<td width="150" valign="top">
<a href="">Home</a><br>
<a href="index.cfm?page=stats">Stats</a><br><br>
<a href="index.cfm?page=dts">DTS Package</a><br>
</td>
<td align="center" valign="top">
</cfcase>

<cfcase value="end">
</td>
<td width="150" valign="top">&nbsp;</td>
</tr>
</table>

</body>
</html>
</cfcase>

</cfswitch>
index.cfm

<cfmodule template="template.cfm">

<!--- Defualt page to be dislayed --->
<cfparam name="url.page" default="home">

<!--- Evaluate what page is being called --->
<cfswitch expression="#url.page#">

<!--- Display home page --->
<cfcase value="home">
<cfinclude template="dsp_home.cfm">
</cfcase>

<!--- Display stats --->
<cfcase value="stats">
<cfinclude template="dsp_stats.cfm">
</cfcase>

<!--- Display dts packages page --->
<cfcase value="dts">
<cfinclude template="dsp_runDTS.cfm">
</cfcase>
</cfswitch>

</cfmodule>
dsp_home.cfm
<cfscript>
// Make an array of dummy browsers to give us some random data.
browsers = arrayNew(1);
// v 1.0
browsers[1] = 'Internet Explorer 1.0';
browsers[2] = 'Netscape 1.0';
browsers[3] = 'Opera 1.0';
browsers[4] = 'googleRobot 1.0';
browsers[5] = 'msnRobot 1.0';
browsers[6] = 'yahooRobot 1.0';
// v 2.0
browsers[7] = 'Internet Explorer 2.0';
browsers[8] = 'Netscape 2.0';
browsers[9] = 'Opera 2.0';
browsers[10] = 'googleRobot 2.0';
browsers[11] = 'msnRobot 2.0';
browsers[12] = 'yahooRobot 2.0';
// v 3.0
browsers[13] = 'Internet Explorer 3.0';
browsers[14] = 'Netscape 3.0';
browsers[15] = 'Opera 3.0';
browsers[16] = 'googleRobot 3.0';
browsers[17] = 'msnRobot 3.0';
browsers[18] = 'yahooRobot 3.0';
// Get our current directory.
cfmxDTSPath = ExpandPath("*.*");
cfmxDTSDirectory = GetDirectoryFromPath(cfmxDTSPath);
</cfscript>

<!--- If form is submited.... --->
<cfif isDefined("form.submit")>

<!--- If the file doesn't exists, create it. --->
<cfif not fileExists("#cfmxDTSDirectory#browserLog.txt")>
<cffile action = "write"
file = "#cfmxDTSDirectory#browserLog.txt" output="Internet Explorer 1.0,11/02/04">
</cfif>

<!--- Generate 1000 lines of dummy data. --->
<cfloop from="1" to="1000" index="i">
<cfscript>
// Grab a random value from our browser array.
randomBrowser = randRange(1,18);
// Get a random month.
randomMonth = randRange(1,12);
// Get a random day.
randomDay = randRange(1,27);
// Generate a random date.
randomDate = createDate(year(Now()),randomMonth,randomDay);
</cfscript>

<!--- Append our dummy data to the browserLog.txt. --->
<cffile action = "append"
file = "#cfmxDTSDirectory#browserLog.txt"
addnewline="yes"
output = "#browsers[randomBrowser] & "," & dateFormat(randomDate,"mm/dd/yy")#">
</cfloop>

<!--- Just because I hate that annoying refresh alert. --->
<cflocation url="index.cfm">
</cfif>

<br>
<br>
<div align="center">
Click Submit button to append some dummy data to our text file.
<br>
<form name="writeData" action="index.cfm?page=home" method="post">
<input type="submit" name="Submit" value="Submit">
</form>
</div>
dsp_runDTS.cfm

<cfif isDefined("form.submit")>
<cfstoredproc procedure="spExecuteDTS" datasource="#Application.Config.DSN#" debug="NO">
<cfprocresult name="packageResult">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@Server" value="127.0.0.1" null="no">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@PkgName" value="cfmxDTS" null="NO">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@ServerPWD" value="sa" null="NO">

<!---// the next line is only required if using the Windows Authorization //--->
<cfprocparam type="In" cfsqltype="CF_SQL_BIT" dbvarname="@IntSecurity" value="1">
</cfstoredproc>


<table border="0" cellspacing="1" cellpadding="3">
<tr class="statsHeader">
<td>Step Description</td>
<td>Step Name</td>
<td>Success</td>
</tr>
<cfoutput query="packageResult">
<tr>
<td>#stepDescription#</td>
<td>#stepName#</td>
<td><cfif result eq 0><b>Yes</b><cfelse><b>No</b></cfif></td>
</tr>
</cfoutput>
</table>

</cfif>

<br>
<br>
<div align="center">
Click Submit button to run "cfmxDTS" DTS Package.
<br>
<form name="runDTSPackageForm" action="index.cfm?page=dts" method="post">
<input type="submit" name="Submit" value="Submit">
</form>
</div>
dsp_stats.cfm
<!--- Execute spGetStats --->
<cfquery name="qStats" datasource="#Application.Config.DSN#">
exec spGetStats
</cfquery>

<cfoutput>
<table width="300" border="0" cellspacing="1" cellpadding="3">
<tr class="statsHeader">
<td>Browser Type</td>
<td>Hits</td>
</tr>
<cfloop query="qStats">
<tr>
<td>#browser#</td>
<td>#visitorSum#</td>
</tr>
</cfloop>
</table>
</cfoutput>


All ColdFusion Tutorials By Author: Tedd Van Diest
Copyright © 2002-2008 Pablo Varando D/B/A EasyCFM.COM, LLC
You are 1 of 314 active visitors!
ColdFusion Hosting | ColdFusion Development | ColdFusion Training