SSIS Retain Connection

When you connect to an Oracle database, you sometimes need to keep the connection to stay in the same session:

It’s simple, goto the properties of your connection and set RetatinSameConnection to TRUE:

Till Next Time

SSIS Dynamic File Name

"D:\\TEMP\\FilePrefix_" + (DT_WSTR,30)(YEAR(GETDATE()) ) + RIGHT ( "0"+(DT_WSTR,30)(MONTH(GETDATE()) ) ,2) +RIGHT("0"+ (DT_WSTR,30)(DAY(GETDATE()) ),2)+ RIGHT ("0" + (DT_WSTR,30) (DATEPART( "Hh",GETDATE())),2) +RIGHT ("0" + (DT_WSTR,30) (DATEPART( "Mi",GETDATE())),2) +RIGHT ("0" + (DT_WSTR,30) (DATEPART( "ss",GETDATE())),2) +".TXT"
Till Next Time

SSIS Generating new package GUID

Remember to generate a new package id when using a package template or a copy:

Till Next Time

SSIS First of the month Expression builder

DATEADD( "M", -24, (DT_DATE) ((DT_WSTR, 2) MONTH(getdate()) + "/"+"01" + "/" + (DT_WSTR, 4) YEAR( GETDATE())))

Till Next Time

SSIS First and Last Day of the month

declare @date datetimeset

@date = getdate()

--get first day of month

select dateadd(m, datediff(m, 0, @date), 0)

--get last day of month

select dateadd(m, datediff(m, 0, dateadd(m, 1, @date)), -1)

Till Next Time

SSIS Reading all the user defined variables

In an SSIS scripttask you are not able to loop trough all the user defined variables if you don't know the name of the variable. I created a workaround.

Step 1:
create an ActiveX scripttask with the following script:'GETVARS.SCR

'This function gets all the user defined variables from a SSIS package
'(except ado Objects) and put them in a flatfile
'This flatfile can be
used to transfer the variables to a LOG file
'in the database
'20081030
John Minkjan


Function Main()
Dim oVal
Dim isSystem, varType,
varName, varValue
Dim str, FileName, filesys, filetxt

Const
ForReading = 1, ForWriting = 2, ForAppending = 8

Set filesys =
CreateObject("Scripting.FileSystemObject")

FileName = "c:\varfile_"&
DTSGlobalVariables("System::PackageName").Value &".txt"

Set filetxt
= filesys.OpenTextFile(FileName, ForWriting, True)

'Put in a header
filetxt.WriteLine("varName;varType;varValue")

For each oVal in
DTSGlobalVariables
isSystem = oVal.SystemVariable
varType =
oVal.DataType
varName = oVal.QualifiedName
varValue = oVal.Value


if not isSystem then
if varType <> 13 then '(Ado Objects)

if varValue = null or varValue = "" then
varValue = "NULL"
end
if

filetxt.WriteLine(Cstr(varName) &";" & Cstr(varType) &
";" & Cstr(varValue))

end if
end if

Next

filetxt.Close

Main = DTSTaskExecResult_Success

End
Function

Step 2:
Use a data flow to pull the flatfile back into the database for logging:

Till Next Time

SSIS Delete old backup files

Wrote a simple scripttask for deleting old backup files:

Public Sub Main()
Dim strBackupdir As String
Dim strBackupSubDir As
String
Dim strProjectName As String
Dim intDaysBack As Integer
strBackupdir = "..\Backup\"
strProjectName = "project name"
intDaysBack = 7

For Each strSubDirFound As String In Directory.GetDirectories(CStr(strBackupdir))If Mid$(strSubDirFound, Len(strBackupdir) + 1, Len(strProjectName)) = strProjectName Then
For Each FileFound As String In Directory.GetFiles(strSubDirFound, "*.bak")
If File.GetCreationTime(FileFound) < Now().AddDays(-intDaysBack) Then
File.Delete(FileFound)
End If
Next
End
If
Next
Dts.TaskResult = Dts.Results.Success
End Sub

Till Next Time