Notepad++ Column Editing

Notepad++ logo.

Spoiler alert! Column editing is super simple in Notepad++ πŸ€“ All you have to do is press the Alt key while selecting text. You can use either Alt+Mouse Dragging or Alt+Shift+Arrows. This works perfectly on text that is already aligned. But what if your text is not formatted as columns already? You can quickly fix that by using the TextFX plugin.

Animation showing column editing in Notepad++.

#SQLFamily - Pay It Forward (T-SQL Tuesday #57)

T-SQL Tuesday logo.

T-SQL Tuesday #57 is hosted by Jeffrey Verheul (@DevJef) and the topic is #SQLFamily. This is a topic close to my heart and even while writing this I’m all excited to read other stories. I can go on for ages about #SQLFamily, but I’ve decided to focus on one thing: pay it forward.

One year ago I had never heard about #SQLFamily and I didn’t even know the SQL community existed. I knew people wrote blogs and forum posts, I knew some taught classes and others published books, but I was your average employee: I did my job, learned what I had to learn to do my job well, and searched online to find solutions and better ways to do things.

Then I went to PASS Summit 2013 and the experience turned my life upside-down.

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