SSIS carriage return in expression builder

Found it on MSDN:
Property Expression for the Message of an E-mail Message


The following property expression can be used to set the MessageSource property of a Send Mail task. The expression uses a combination of string literals, user-defined variables, and the concatenation (+) operator. The user-defined variables are named nasdaqrawrows, nyserawrows, and amexrawrows. The string "\n" indicates a carriage return.

"Rows Processed: " + "\n" +" NASDAQ: " + (dt_wstr,9)@[nasdaqrawrows] + "\n" + " NYSE: " + (dt_wstr,9)@[nyserawrows] + "\n" + " Amex: " + (dt_wstr,9)@[amexrawrows]


If nasdaqrawrows is 7058, nyserawrows is 3528, and amexrawrows is 1102, the expression evaluates to the following string.


Rows Processed:



NASDAQ: 7058



NYSE: 3528



AMEX: 1102

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

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

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.....

Wow,

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

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

John