Loop through files in a folder Use ActiveX

There are times when you need to process all files in a folder. SSIS gives us the While loop that can parse all files in a folder. However DTS requires a work around with ActiveX and the trusty FileSystemObject. Here is some code that parses through all the files in a folder and pops up a message box with the file name.

'**********************************************************************
' Visual Basic ActiveX Script
'**********************************************************************

Function Main()

'Set the target folder name
folder = "\\MyDrive\Myfolder\"

set fso = CreateObject("Scripting.fileSystemObject")
set fold = fso.getFolder(folder)

' Loop through the files in the folder
for each file in fold.files

' Pop up a Message Box with the file name
MsgBox( file.name)

' You can also set global variables with this option
'set DTSGlobalVariables("gv_FileName").Value = file.name

next

set fold = nothing
set fso = nothing

Main = DTSTaskExecResult_Success

End Function

Thats it! This script is also handy for files that don't have a predefined name, such as filenames that contain a timestamp (ie Filename_YYYYMMDDHHMMSS.csv)

Using parameters in Oracle Queries via DTS

My latest adventure had me working with Oracle and SQL Server databases. The task required setting global variables in SQL DTS, then using those global variables in the Data Pump. However, it seems that the Oracle ODBC connection did not like me using the standard parameter formatting:

SELECT COL1, COL2, COL3

FROM ORACLEDB.TABLE

where COL4 = ?

As we all know, when you throw in a ? into a query, you pick the parameter at design time. But, no matter what I did, the task would not accept the parameter. It wouldn't even let me pick a parameter from the list, so I knew that I had to go about it a different way. As always, the trusty ActiveX task fit the bill. Here is a script to dynamically change the Query in a Data Pump:

Option Explicit

Function Main()
 Dim oPkg, oDataPump, sSQLStatement

 ' Build new SQL Statement
 sSQLStatement = "SELECT COL1, COL2, COL3" &_
 " FROM ORACLEDB.TABLE where COL4 = '" & DTSGlobalVariables("gv_Variable").Value & "'"

 ' Get reference to the DataPump Task
 ' This is for the first datapump. Change as needed
 Set oPkg = DTSGlobalVariables.Parent
 Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

 ' Assign SQL Statement to Source of DataPump
 oDataPump.SourceSQLStatement = sSQLStatement

 ' Clean Up
 Set oDataPump = Nothing
 Set oPkg = Nothing

 Main = DTSTaskExecResult_Success
End Function

Now, the query text will include all of the previous text and the parameter.

CTEs and SQL Server

Thanks to the guys at TechRepublic.com I have used CTEs to delete duplicates with ease in SQL Server 2005 and higher. Here is the link. Here meat of the article a sample:

;WITH SalesCTE(Product, SaleDate, SalePrice, Ranking)
AS
(
SELECT
 Product, SaleDate, SalePrice,
Ranking = DENSE_RANK() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC)
FROM SalesHistory
)
DELETE FROM SalesCTE
WHERE Ranking > 1

Very easy and handy!