SQLHangout #22 - Blabbering about Biml

Logo for SQLHangout #22 - Blabbering about Biml.

When Boris (@brshristov) invited me to a SQLHangout I was really honored and couldn’t say no. It’s such a fun idea and I’ve really enjoyed watching the other SQLHangouts. It’s great to see people’s personalities shine through in videos instead of only reading their blog posts, and I learn something new.

I suggested to chat about Biml, something I’ve only recently begun learning myself that has already saved me many hours of work. I still have so much to learn, but the fact that it took me about 20 hours to learn something new that has saved me and my coworkers hundreds of hours already… That’s worth sharing. If I can do it, you can do it!

Boris named the video “Biml. An introduction.” I thought a more appropriate name was “Blabbering about Biml” πŸ€“

(“Oh hi!")

I forgot to mention their names, but thank you to Julie Smith (@JulieChix) and André Kamman (@AndreKamman) for inspiring me to learn more Biml in their SQLSaturday sessions in Tampa and Copenhagen! 😊

Package Configurations and Connection Managers in Biml

Biml Wheel.

At work we wanted to start using Biml to speed up development in our existing projects without making too many changes at once. I decided to start by writing Biml files that generates SSIS packages exactly like the ones we already have so we can implement changes faster, and step one was to figure out how to create Package Configurations and Connection Managers in Biml:

  • Create an XML configuration file Package Configuration
  • Create the Connection Manager specified in the XML configuration file
  • Create SQL Server Package Configurations that use the Connection Manager specified in the XML configuration file
  • Create Connection Managers specified in the [SSIS Configurations] table in SQL Server
  • Create a Parent package variable

I recommend reading Understanding Integration Services Package Configurations by Rafael Salas (@RafSalas) and Getting Started with BimlScript by Paul S. Waters (@pswaters) if you’re not already familiar with the basics of SSIS Package Configurations and Biml.

SQL Server, SSIS and Biml Data Types

Biml Wheel.

The table below is a simplified mapping between SQL Server, SSIS and Biml Data Types. It does not include all possible mappings or all data types, but is meant as a quick reference while developing and learning Biml. I mainly created it as a cheat sheet for myself, but I hope other Biml developers will find it useful!

Cheat Sheet for SQL Server, SSIS and Biml Data Types

SQL Server SSIS Biml
bigint DT_I8 Int64
binary DT_BYTES Binary
bit DT_BOOL Boolean
char DT_STR AnsiStringFixedLength
date DT_DBDATE Date
datetime DT_DBTIMESTAMP DateTime
datetime2 DT_DBTIMESTAMP2 DateTime2
datetimeoffset DT_DBTIMESTAMPOFFSET DateTimeOffset
decimal DT_NUMERIC Decimal
float DT_R8 Double
geography DT_IMAGE Object
geometry DT_IMAGE Object
hierarchyid DT_BYTES Object
image (*) DT_IMAGE Binary
int DT_I4 Int32
money DT_CY Currency
nchar DT_WSTR StringFixedLength
ntext (*) DT_NTEXT String
numeric DT_NUMERIC Decimal
nvarchar DT_WSTR String
nvarchar(max) DT_NTEXT String
real DT_R4 Single
rowversion DT_BYTES Binary
smalldatetime DT_DBTIMESTAMP DateTime
smallint DT_I2 Int16
smallmoney DT_CY Currency
sql_variant DT_WSTR Object
text (*) DT_TEXT AnsiString
time DT_DBTIME2 Time
timestamp (*) DT_BYTES Binary
tinyint DT_UI1 Byte
uniqueidentifier DT_GUID Guid
varbinary DT_BYTES Binary
varbinary(max) DT_IMAGE Binary
varchar DT_STR AnsiString
varchar(max) DT_TEXT AnsiString
xml DT_NTEXT Xml

(* These data types will be removed in a future version of SQL Server. Avoid using these data types in new projects, and try to change them in current projects.)

Volunteering at SQLSaturday #275 Copenhagen

SQLSaturday #275 check-in table with a group of attendees waiting in line.

Last weekend I was in Copenhagen for SQLSaturday #275 Denmark. What a great event! I had signed up as a volunteer for several reasons. I wanted to help the Danish (and Nordic) SQL community, I wanted to get more experience as a volunteer, I wanted feedback and inspiration that will be useful for when we’re organizing SQLSaturday #317 Oslo, and most importantly I wanted to meet all the great community people I only knew from Twitter.

Planning volunteer tasks started a few weeks before the event when the event organizers and volunteers decided on who would be responsible for each task via e-mails. I felt a bit like Donkey from Shrek going “pick me! pick me!” when I told the event organizers I could volunteer all weekend if needed, but they had done such a great job they didn’t really need much help. The main tasks were volunteering as room attendants and helping out with registrations.

Speaker standing in front of a projector screen.

I was assigned a room after lunch, so I spent the morning listening to the keynote, attending a few sessions, getting to know people and talking to sponsors. (I may have had moments where I ran around like a headless chicken trying to figure out what to do, which usually made me grab some more coffee, which in turn made me run around even more… At least I got to see a lot of the venue this way!)

SQLSaturday #275 sponsor area.

During the end of the lunch I realized that I wasn’t exactly sure what to do as a room attendant, but a quick briefing from the event organizers made it an easy task. The speakers in my room were experienced and had no problems running the show themselves, so all I had to do was agree on when to notify them about the amount of time left, make sure there was enough water, hand out speaker gifts and keep the room clean.

It was a long day, but a great day! What I enjoyed the most was meeting everyone in person and getting to know new people. It was a fun surprise to meet coworkers and members of SQL Server User Group Norway that I didn’t know were attending πŸ˜ƒ I got to attend sessions on topics I usually don’t work on and learned new things that way, and I have to mention the SQL swag I brought back home: volunteer shirt (also known as “shirt dress”), fleece sweater (I drowned in it, but it was lovely and warm), cap, candy, books and lots of other fun items.

Holding a bottle of beer labeled SQLBrew.

There were a few things that could have gone smoother, but I really am being nitpicky when I point those out. The organizers could have sent out instructions on each task before the event, but I could also have made sure to ask earlier what to do. I could have talked more with the speakers in my room, but I didn’t want to disturb them while they were preparing. I could have greeted the attendees as they walked in, but they were there to see the speakers, not the volunteers, so I let the speakers greet everyone. I wish I could have met more people, spent more time with sponsors and attended all the sessions, but there was just not enough time for everything.

Volunteering is a great experience and I definitely won’t hesitate to help out at other events, but it does take time and you could miss out on sessions because you’re volunteering in another room. If that’s the case for you as a volunteer, try to switch rooms with another volunteer. If you’re an event organizer, try to make sure everyone gets to see the sessions they’re most interested in so it’s a great day for everyone.

At the end of the day there were giant jenga matches, raffles, organic hot dogs, #SQLBrew and lots of happy faces. Thank you to the event organizers for a great event and for inspiring us Norwegian organizers. Thank you to all the speakers and the other volunteers, I hope to see some of you in Oslo in August and hopefully at other SQLSaturdays!

Notepad++ Macros Example

Notepad++ logo.

Do you ever find yourself doing the same things over and over and over again when working on your text files? You can save a lot of time by recording and running Notepad++ macros to automate these repetitive steps. Just record your steps once, then run them as many times as you need!

Animation showing how to record and run Notepad++ macros.