Wednesday, December 3, 2008

SSIS Generating new package GUID

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


Till Next Time

Thursday, November 20, 2008

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

Wednesday, November 19, 2008

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

Thursday, October 30, 2008

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
John Minkjan

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

ForReading = 1, ForWriting = 2, ForAppending = 8

Set filesys =

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

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

'Put in a header

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

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

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

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

end if
end if



Main = DTSTaskExecResult_Success


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

Till Next Time

Friday, September 5, 2008

SSIS Delete old backup files

Wrote a simple scripttask for deleting old backup files:

Public Sub Main()
Dim strBackupdir As String
Dim strBackupSubDir As
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
End If
Dts.TaskResult = Dts.Results.Success
End Sub

Till Next Time

Friday, August 29, 2008

SSIS Directory exists

We needed to check if an old backupdirectory exists before we started processing it. I made a simple scripttask with a expression to the next stage:

In the scripttask we us the DIR function to check if a directory exists:

With an expression we check if can go to the next stage:

Till Next Time

SSIS Carriage return in annotation

One those things you tend to forget:
A carriage return (enter) in SSIS is done with control + enter

Till Next Time

Saturday, August 16, 2008

SSIS Variables in procedures

Say you have a procedure like this on your MSSQL db which you want to call from SSIS. It has an input and output parameter which we want to use: In SSIS we make two variables:

Add an Execute SQL task to your Sequence Container
In the properties set the name, description and connection. For the SQLstatement put:

At the parameter mapping set direction and Parameter Name. The Parameter Name should a sequence starting at 0. The sequence should correpond with the order of the question marks in in your SQL statement.

Run your package (F5) to see if it works

Till Next Time

SSIS Back in town.....


Started on a SSIS project today after being an OBIEE junkie for a year. There was a lot of stuff I knew I had done before, but how the F*&% did I do it? Natural conslusion: Let's start up this blog.

Till Next Time

Sunday, February 3, 2008

Welcome to SSIS101

Hi, My name is John Minkjan and I'm a senior BI-consultant at CIBER in the Netherlands. I reserverd a couple of ...101 blogspots. At the moment I'm mostly active on OBIEE101. I will start posting here as soon as my focus in back on Microsoft Sequel Server Integration Services.

Till Next Time