SSIS expressions I often use

expression

If either you are doing your SSIS expressions by hand or using the BIML framework, you’ve came across the expressions and the expression-builder.

This is a helper list, with my most often used, and wich are allways forgotten when I need them, of my commonly used SSIS expressions.

Strings

Filename from fully qualified pathstring

  • RIGHT([FilePath],FINDSTRING(REVERSE([FilePath]),”\\”,1) – 1)

Folderpath from fully qualified pathstring

  • SUBSTRING([FilePath], 1, LEN([FilePath]) – FINDSTRING(REVERSE([FilePath] ), “\\” ,1 ) + 1)

Foldername from fully qualified pathstring

  • TOKEN[FilePath],”\\”,TOKENCOUNT([FilePath],”\\”) – 1)

This is only for SSIS2012 and onwards. The TOKEN and TOKENCOUNT expressions are not in prior versions of SQL Server

For prior versions of SQL Server:

  • SUBSTRING([FilePath],LEN([FilePath]) – FINDSTRING(REVERSE([FilePath]),”\\”,2) + 2,(LEN([FilePath]) – FINDSTRING(REVERSE([FilePath]),”\\”,1)) – (LEN([FilePath]) – FINDSTRING(REVERSE([FilePath]),”\\”,2)) – 1)

Replace empty strings

With SQL Server 2012 the new REPLACENULL function was implemented, making it alot easier to replace empty values.

  • REPLACENULL([ColumnName], [replace value])

For earlier versions of SQL Server

  • LEN([ColumnName]) == 0 ? [replace value] : [ColumnName]
  • ISNULL([ColumnName]) ? [replace value] : [ColumnName]

Date and time

Date from datetime

If you want to remove the time element in a datetime object, you should cast it to DT_DBDATE. But because that datatype is very inconvenient to use, you should cast it back to the original datatype. That will set the time to 0:00.

  • (DT_DATE)(DT_DBDATE)@[User::datetimeVariable]
  • (DT_DATE)(DT_DBDATE)[datetimeColumn]
  • (DT_DBTIMESTAMP)(DT_DBDATE)GETDATE()

Time from datetime

If you want to remove the date element in a datetime object, you should cast it to DT_DBTIME. And optional cast it to a string.

  • (DT_STR,8,1252)(DT_DBTIME)@[User::datetimeVariable]
  • (DT_STR,8,1252)(DT_DBTIME)[datetimeColumn]
  • (DT_STR,8,1252)(DT_DBTIME)GETDATE()

First day of the current month

If you want to get the first day of the current month, you take the current datetime and deduct the current day number (minus 1). Optional you can remove the time part:

  • DATEADD(“d”, -DAY(GETDATE()) + 1, GETDATE())
  • (DT_DBTIMESTAMP)(DT_DBDATE)DATEADD(“d”, -DAY(GETDATE()) + 1, GETDATE())

Last day of the current month

If you want to get the last day of the current month, you add 1 month and deduct the current day number. Optional you can remove the time part:

  • DATEADD(“d”, -DAY(GETDATE()), DATEADD(“m”, 1, GETDATE()))
  • (DT_DBTIMESTAMP)(DT_DBDATE)DATEADD(“d”, -DAY(GETDATE()), DATEADD(“m”, 1, GETDATE()))

And if you realy want the last second of the current month 30-06-2011 23:59:59

  • DATEADD(“s”, -1,DATEADD(“d”, -DAY(GETDATE()) + 1, DATEADD(“m”, 1, (DT_DBTIMESTAMP)(DT_DBDATE)GETDATE())))

Weeknumber of the month

1-june-2012 is weeknumber 23 in the year, but weeknumber 1 of the month june 2012.

  • (DATEPART(“ww”,[YourDate]) – DATEPART(“ww”,DATEADD(“d”, -DAY([YourDate]) + 1, [YourDate]))) + 1

Datetime as concatenated string

  • (DT_STR, 4, 1252)DATEPART(“yyyy”, @[System::StartTime]) +
    RIGHT(“0” + (DT_STR, 2, 1252)DATEPART(“mm”, @[System::StartTime]), 2) +
    RIGHT(“0” + (DT_STR, 2, 1252)DATEPART(“dd”, @[System::StartTime]), 2) +
    RIGHT(“0” + (DT_STR, 2, 1252)DATEPART(“hh”, @[System::StartTime]), 2) +
    RIGHT(“0” + (DT_STR, 2, 1252)DATEPART(“mi”, @[System::StartTime]), 2) +
    RIGHT(“0” + (DT_STR, 2, 1252)DATEPART(“ss”, @[System::StartTime]), 2)

Fully automate the BIML expansion

Mist 4.0

With all the BIML code done and the production scenarios start to emerge, it would be great to have the BIML code automatic expanded and create new packages if there are changes to the underlying metadata. Either configured manually or directly from the sources. Also known as BIML expansion.

With the MIST application from Varigence – this is possible through the command line util that ships with the installation.

When the installation of MIST has finished, you’ll find a new .exe-file in the installation folder called bimlc.exe.

This file is the core engine of the command line util.

According to the online documentation found here – the command line util is pretty straight forward.
The observant reader will notice that the link points to a documentation for hadron.exe – this is the old name for the same tool. See this documentation from Varigence.

The syntax is


bimlc.exe -s="<sourcefile>" -t="<targetfolder>"

I’ve made a quick demo biml-project with below code:


<# var meta_connectionstring = "Provider=SQLNCLI11;Server=[myServer];Initial Catalog=[myDatabase];Integrated Security=SSPI;"; #>
<#
DataTable tables;
tables = ExternalDataAccess.GetDataTable(meta_connectionstring, "[dbo].[storedProcedureThatGivesMeTheData]");
#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<OleDbConnection Name="BIMLMETA" CreateInProject="true" ConnectionString="Provider=SQLNCLI11;Data Source=[myServer];Integrated Security=SSPI;Initial Catalog=[myDatabase]" />
</Connections>
<Packages>
<# foreach(DataRow table in tables.Rows) { #>
<Package Name="SSIS package <#=table["schema"] #>_<#=table["table"] #>">
</Package>
<# } #>
</Packages>
</Biml>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Linq"#>

This file is called AutoCompile.biml, and I want to put the generated files in c:\AutoCompilePackages. Then the commandline would be:

bimlc.exe -s="c:\AutoCompile\AutoCompile.biml" -t="c:\AutoCompilePackages"

When I then press ENTER the compiler starts up and does it’s thing:

BIMLAutoCompileCommand

The output tells us that there are 9 packages created in each of its own projects.

I can find the compiled projects and packages in my defined output folder.

BIMLAutoCompileOutput

And the content of each folder.

BIMLAutoCompileOutputContent

Thats it. Easy job 🙂 thanks to Varigence and their MIST product.

Referencing the same custom script task in SSIS with BIML

Biml

In these times of data warehouse automation and fast track SSIS, BIML has become a huge contributor to my daily work.
On one of my most recent projects we had a custom script task in SSIS that we needed to implement in the same package several times.

The code of the custom script is in its own biml-file in the project and referenced from the main biml-file.

We kept getting a mysterious error when executing the package in debug mode from Visual Studio. (No warning upon building the packages).

After alot of debugging and hair pulling hours, we finally got our arms around the bug.

The error was that the ProjectCoreName of the new 3 script tasks in the same package had the same name.

My learning from this, and yes, I’m not a .NET developer, is that upon executing the custom script task, the SSIS engine must compile the code and store it for usage upon handling the data. This storage is shared with the whole package, and therefore cannot contain the same .NET project name. [Written from my personal understading – correct me if I’m wrong].

So for any future BIML users who wants to add the same custom script task to the BIML project – remember to add a variable to the ProjectCoreName of your biml-script.

This can be done fairly easy, as your biml-scripts can relate to the variables calling the biml-file. Just add the standard

<#=variablename#>

to he ProjectCoreName-tag of your biml-file containing the custom script.

Happy BIML-ing…

I’m speaking at SQL Saturday Copenhagen and Edinburgh

sqlsat275_web

With a little push from some of my colleagues, I have submitted a speaksersession to SQL Saturday in Copenhagen and Edinburgh.

To my big surprise both sessions (with the same subject) was chosen as a part of the sessions to be held. I’m very excited and thrilled that these possibilities was given to me.

sqlsat281_web

The SQL Saturday in Copenhagen is going to be my virgin session, where I’ll speak about a dynamic approach to partition kubes – bassed on the multidimensional model. The Tabular model’s dynamic partitioning can be read in a blogpost HERE.

I hope to see you at the events – it’s free. You can sign up here: Copenhagen Edinburgh.

SSISDB crash – Certificate, asymmetric key or private key data invalid

ferrarif1crash

One of my longterm customers had a database-server crash a while ago. The server would not spin up after reboot. The SSISDB crash also gave problems.

The whole installation including tweeks and other stuff had to be re-attached to a new and fresh installation of SQL Server 2012.

It was s fairly young BI-server, so the damage was not that big after all.

Untill today…

SSISDB crash

The development of SSIS project to maintain the BI sollution had to be deployed to the SSISDB catalog.

I got an error like below:

The certificate, asymmetric key, or private key data is invalid. Changed database context to ‘SSISDB’.
(Microsoft SQL Server, Error: 15297)

SSISDB key invalid

Microsoft has defined the error but not yet documentet it – this link is displayed when I press the copy button in the dialog from above and paste into notepad:

http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=15297

I found this blogpost – which guided me to the sollution.
A place in his guide – he mentions this code:

ALTER SERVICE MASTER KEY FORCE REGENERATE;

But that this not do the trick – I got an error telling me that I could not force regenerate the key.
I tried to remove the ‘FORCE’ command and things started to happen upon execution.

I got a ‘command completed successfully’ and thought that now I could carry on with Gilberts guide. But but – again I ran into troubles.

I ran the above script but another error apeared. The login was allready existing on the server. But when browsing the login folder I then came to a halt.
I do not know what caused this, me scripting or the guy reinstating the server from the crash. But the login as mentioned above had changed name – not much – but still enough for me not to notice at first glanse.

The prefix and suffix with ‘##’ were gone from the login. The login had been changed from the correct ‘[##MS_SQLEnableSystemAssemblyLoadingUser##]’ to ‘[MS_SQLEnableSystemAssemblyLoadingUser]’.

I manually changed the login name and added the two ‘##’ in each end of the name – and voila!

Everything is now up and running again.

Conclusion:

I got a good guide from Gilbert Quevauvilliers – thank you so much. I may have found out myself – but the time to come to the thought that the login had changed name somehow – that would have been the last thing I would check on the list of – well – alot of things.

I would hope that Microsoft could be a little more explicit in their product help – but as allways it’s a hard prioritized list of things for them to do, and they cannot do it all at once.

I’ve learned a but from this – and hope that You the reader could use this help.

en_USEnglish
da_DKDanish en_USEnglish