Datura - Coding with Rudy

Move a file with ActiveX in DTS

noreply@blogger.com (Rudy) — Wed, 20 Jan 2010 22:38:00 +0000

Once you process a file, you will usually archive it. Often, you may run into cases where the file already exists in the archive directory. Unfortunately, the Move command does not have an overwrite option. You will need to use the Copy command with the Overwrite option.

Function Main()
Dim oFSO
Dim vSourceFile
Dim vDestinationFile

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Const OverwriteExisting = True
Const DeleteReadOnly = True

vSourceFile = "\\MySourceDirectory\*.csv"
vDestinationFile = "\\MyDestinationDirectory\"

' Move the files Use OverwriteExisting in case the file is there
objFSO.CopyFile vSourceFile, vDestinationFile, OverwriteExisting

' Delete the files in the source directory
objFSO.DeleteFile(vSourceFile ) , DeleteReadOnly

Set oFSO = Nothing

Main = DTSTaskExecResult_Success

End Function

Now, all of the files in the source directory are now in the archive.

Grab the filename for a file in a Folder

noreply@blogger.com (Rudy) — Wed, 20 Jan 2010 22:24:00 +0000

You will probably come across a file that is formatted like this

filename_20100101235938.csv

where we are dealing with the full timestamp in the name. As long as the file is the only file in the directory, we can use the script below:

Function Main()

Set objFSO =
CreateObject("Scripting.FileSystemObject")
objStartFolder = "\\DriveName\FolderName\"

Set objFolder = objFSO.GetFolder(objStartFolder)

Set colFiles = objFolder.Files
For Each objFile in colFiles
DTSGlobalVariables ("gv_FileName").Value =
objStartFolder & objFile.Name
Next
Main = DTSTaskExecResult_Success

End Function

You now have the full path and filename in the global variable. Now, if there are multiple files in the directory, you will have the last file name in the global variable.

Loop through files in a folder Use ActiveX

noreply@blogger.com (Rudy) — Fri, 04 Dec 2009 23:01:00 +0000

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

noreply@blogger.com (Rudy) — Fri, 10 Jul 2009 20:24:00 +0000

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

noreply@blogger.com (Rudy) — Thu, 21 May 2009 19:33:00 +0000

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!

Using ActiveX to check the age of a file

noreply@blogger.com (Rudy) — Mon, 29 Dec 2008 21:10:00 +0000

Sometimes you need to check the age of a file before proceeding. You really don't want to work with old data, so this is how you can do this in the middle of an ActiveX task.

Option Explicit

Function Main()
Dim oFSO, oConn, sFileName, oFile

' Get the filename from my Text File connection called "Text File (Source)"
' There are other ways to do this, of course.
Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Source)")
sFilename = oConn.DataSource
Set oConn = Nothing

Set oFSO = CreateObject("Scripting.FileSystemObject")

' Check File Exists first
If Not oFSO.FileExists(sFilename) Then
' Return Error
Main = DTSTaskExecResult_Failure
Else
' Get file object
Set oFile = oFSO.GetFile(sFilename)

' Check age of file is less than 24 hours
If DateDiff("h", oFile.DateLastModified, Now) >= 24 Then
' Return Error
Main = DTSTaskExecResult_Failure
Else
' Return Success
Main = DTSTaskExecResult_Success
End If
End If

Set oFile = Nothing
Set oFSO = Nothing

End Function

Using ActiveX to Dynamically set a filename

noreply@blogger.com (Rudy) — Mon, 18 Feb 2008 21:53:00 +0000

From time to time, you will need to dynamically set a filename in the middle of your DTS packages. The following code shows you how easy it is to build a filename dynamically:

'*************************
' Sets the file name to the format: MyFile_YYYYMMDD.csv
'*************************
Function Main()

Dim oPkg, oBulkExport

'Set the Path where the file is located
strPath = "c:\MyPath\"

'Next build the different parts of the file name,
'including the prefix and file extension using
'the Now function, which returns the current date and
'the Month(),Day(), and Year() function which
'return the respective datepart.
strPrefix = "MyFile_"
strMonth = Month(Now)
strDay = Day(Now)
strYear = Year(Now)
strFileExt = ".csv"

'If you only require 2 digit years, trim the first 2 digits
'strYear = Right(strYear, 2)

'If the day or month is a single digit, the
'Now function will only return that single digit
'We will append a leading zero to the Month
'and/or Date string if needed
If Len(strMonth) = 1 Then strMonth = "0" & strMonth
If Len(strDay) = 1 Then strDay = "0" & strDay

'Concatenate all parts of the file name with
'the file path in front.
outString = strPath & strPrefix & strYear & strMonth & strDay & strFileExt

'Once the path and file name are determined,
'set the global variable.
'You will still need to set the file name
using a Dynamic Properties Task.
DTSGlobalVariables("gv_FileName").Value = outString

Main = DTSTaskExecResult_Success
End Function

As stated in the code, you will still need a Dynamic Properties Task to actually set the file name, as you are only setting a global variable in the code.

ANSI warnings, NOCOUNT and Temp Tables

noreply@blogger.com (Rudy) — Thu, 05 Jul 2007 15:40:00 +0000

So, I'm always needing these code bits. And yet, I forget the exact syntax and I have to look all over the place for the exact code. Well, here it is. First, NOCOUNT. If you don't want SQL Server to return the Row Count after you execute a query, here is the code:

SET NOCOUNT ON

Next, do you get annoying ANSI errors when inserting data? Begone!

SET ANSI_WARNINGS OFF

Lastly, when working with true temp tables, the ones starting with ## or #, the old standard will not work. This is because Temp tables live in the database TempDB, not your current DB. SO, sysobjects won't have the table info you're looking for.

-- Won't Work!
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='##tmpTable')
BEGIN
Truncate table ##tmpTable
DROP TABLE ##tmpTable
END

You need this:

-- THis will delete your temp table
IF OBJECT_ID('tempdb..##tmpTable') IS NOT NULL
BEGIN
Truncate table ##tmpTable
DROP TABLE ##tmpTable
END

Change a Date Format Using ActiveX and DTS

noreply@blogger.com (Rudy) — Thu, 22 Feb 2007 23:27:00 +0000

Lets say you are importing data via DTS and the date is in this format:
YYYYMMDD
So, SQL server isn't going to know what to do with this data. So, you're going to have to use ActiveX copy instead of the plain old Copy Column Task. Here is the code you would use in your ActiveX task within the Data Pump:

'**************************************
' Visual Basic Transformation Script
'**************************************

' Copy each source column to the destination column
Function Main()
DTSDestination("myDate") = left(DTSSource("badDate"),4)+"-"&_
+mid(DTSSource("badDate"),5,2)+"-"+right(DTSSource("badDate"),2)
Main = DTSTransformStat_OK
End Function

Substitute myDate for your destination and badDate for the source. This will give you a nice result in the YYYY-MM-DD format.

You can also expand this technique to include times. You would have to use some two more Mid to pull the date and hour, and the right would pull the minutes.

Happy Coding!

Adding Foreign Keys after a table has been created

noreply@blogger.com (Rudy) — Thu, 11 May 2006 17:29:00 +0000

Well, you knew exactly what you needed when you created a table. Only now when you created a child table, the child table does not update with the information from your main table. Or worse you deleted something from your primary table and it still lives in the child table. You need a foreign key, my friend. This is how you define one after you've created your table.

ALTER TABLE My_Child_Table ADD CONSTRAINT FK_User_ID
FOREIGN KEY ( User_ID_In_Child_Table )
REFERENCES My_Primary_Table ( User_ID_In_Primary_Table )
ON DELETE CASCADE
ON UPDATE CASCADE

The last two lines are important. In this example, if the user id in the primary table is modified or deleted, the change will cascade to the child table. This ensures consistency between the two tables.

As always, this works on SQL Server. Check your documentation for mySql, Oracle, etc.

Converting Double to String with J#

noreply@blogger.com (Rudy) — Mon, 08 May 2006 21:12:00 +0000

So, you made an awesome function that does cool things. Only the return type is a double and you have to display it as a string. No problem, you'll just have to convert it using this little snippet:

String my_String_for_Display = new String();
my_String_for_Display = Double.toString(my_Function());

This should would for C# as well, with minor modifications, but check the MSDN pages. This will also work for a double value instead of a function call, function with parameters, etc.

Adding Checks after a table has been created

noreply@blogger.com (Rudy) — Thu, 12 Jan 2006 21:43:00 +0000

Did you create a table, only to find that you need to add a check constraint?
It happened to me, and I hopped on google to find the code. In this example, we'll use my fictional User table. We want to know if our user is alive or dead. 1 = alive, 0 = dead. Here is the code for SQL server:

alter table dbo.Tbl_User_Table add constraint
CK_User_Alive check (User_Alive in (0,1))

I don't know if it will work with MySql, Oracle, etc, so check your documentation. Happy Coding!