Skip to content

Package Configurations and Connection Managers in Biml

Biml (Business Intelligence Markup Language) - Package Configurations and Connection Managers in Biml

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 (b | t) and Getting Started with BimlScript by Paul S. Waters (t) if you’re not already familiar with the basics of SSIS Package Configurations and Biml.

Continue reading →

SQL Server, SSIS and Biml Data Types

Biml (Business Intelligence Markup Language) - SQL Server, SSIS and Biml Data TypesThe 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!

Can’t remember this post’s URL? Neither can I. You can use this short URL instead: cathrinew.net/BimlDataTypes :)

Cheat Sheet for SQL Server, SSIS and Biml Data Types

SQL ServerSSISBiml
bigintDT_I8Int64
binaryDT_BYTESBinary
bitDT_BOOLBoolean
charDT_STRAnsiStringFixedLength
dateDT_DBDATEDate
datetimeDT_DBTIMESTAMPDateTime
datetime2DT_DBTIMESTAMP2DateTime2
datetimeoffsetDT_DBTIMESTAMPOFFSETDateTimeOffset
decimalDT_NUMERICDecimal
floatDT_R8Double
geographyDT_IMAGEObject
geometryDT_IMAGEObject
hierarchyidDT_BYTESObject
image (*)DT_IMAGEBinary
intDT_I4Int32
moneyDT_CYCurrency
ncharDT_WSTRStringFixedLength
ntext (*)DT_NTEXTString
numericDT_NUMERICDecimal
nvarcharDT_WSTRString
nvarchar(max)DT_NTEXTString
realDT_R4Single
rowversionDT_BYTESBinary
smalldatetimeDT_DBTIMESTAMPDateTime
smallintDT_I2Int16
smallmoneyDT_CYCurrency
sql_variantDT_WSTRObject
text (*)DT_TEXTAnsiString
timeDT_DBTIME2Time
timestamp (*)DT_BYTESBinary
tinyintDT_UI1Byte
uniqueidentifierDT_GUIDGuid
varbinaryDT_BYTESBinary
varbinary(max)DT_IMAGEBinary
varcharDT_STRAnsiString
varchar(max)DT_TEXTAnsiString
xmlDT_NTEXTXml

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

Continue reading →

Interviews as Learning Experiences (T-SQL Tuesday #54)

T-SQL TuesdayT-SQL Tuesday #54 is hosted by Boris Hristov (@brshristov) and is all about interviews and hiring.

I have a confession to make: Once in a while I say yes to interviews, not because I’m actually looking for new opportunities, but because they’re both fun and challenging.

For me, it’s a great way to learn and grow. I haven’t walked out of a single interview without having learned something new about myself, a technology or the industry I work in.

Going to interviews forces me out of my comfort zone and makes me feel stressed, nervous and a little scared. Sometimes I get questions that I don’t immediately know the answer to and I get flustered. Sometimes I knock over water glasses or burn my hands on hot coffee and make a complete fool of myself. But every time I go to interviews I get a little better at small talk, calming my nerves, thinking on my feet, handling unexpected situations, structuring my thoughts and formulating answers.

It’s a great opportunity to reflect upon my own situation and where I want to be in a year or two. It gives me a peek into what skills are needed and wanted right now, and if it’s a technical interview I quickly find out which skills I need to improve.

My best interview happened early in my career, it was just supposed to be a first introduction interview for a junior developer position. Halfway through the interview I mentioned that I like to challenge myself to learn from the experience, and the interviewer promptly decided to follow up on that statement: “What would you do if we gave you a challenge right now?” (I couldn’t really say no, could I?)

The challenge was to skip right into the second, technical interview without preparing. I had to look at a screen capture of a website and explain the HTML and CSS I would use to replicate it, I had to draw data models and SQL queries on a whiteboard, and I had to guess my way through some JavaScript – and it was fun. I left the interview feeling proud, not because I did well (I forgot important things and made mistakes), but because I was thrown into a new situation and handled it better than I had feared.

The opposite experience was when I was still a student and had a series of speed interviews in one day. They lasted ten minutes and you had three minutes to introduce yourself, three minutes to listen to the company introduction, and the rest of the time to ask questions. The first speed interviews went really well. I grew more confident and didn’t feel like a complete nervous wreck anymore, but as I approached the next table and saw three very serious men in suits stare at me I could feel my palms getting sweaty again. Thankfully they never noticed that, because none of them even wanted to shake my hand. They told me to sit down, grabbed their pens and stared at me in silence. I took that as my cue to introduce myself and spent the next three minutes telling them about my background and why I had applied. When my three minutes were up they looked at each other, looked at me, looked at each other and finally said: “You know that there are mostly men working in this industry, right? How are you going to handle that?”

My jaw dropped to the floor. Inexperienced and flustered, I answered as best as I could that I was used to working and studying with guys and that it had never even been an issue before. They looked at each other again, sighed, looked at me and said: “Well, we don’t have any more questions, so you can just go.” What? I looked at my watch and saw that we still had more than five minutes left, but it was so uncomfortable to sit there that it was better to leave early. So I got up, utterly embarrassed, and zigzagged my way out of the room trying to ignore the stares from everyone still in the middle of their speed interviews.

It is by far the worst “interview” I have ever been to. I felt small and ashamed, but it was also when I promised myself that I would look at each interview as a learning experience. It also made me bring my “I’ll show them!” attitude to the next interview – and that next interview got me my first job :)

Biml Scandinavian Tour (Oslo May 12th and 13th)

Biml Scandinavian Tour 2014

From May 5th to May 15th Varigence and Rehfeld will visit six cities in four different countries to present Biml and Mist. The highlights of the tour are the four full day Biml Master Classes in each country. The classes will be taught by Scott Currie, the creator of Biml, and Rehfeld Principal BI Architect Peter Hansen.

SQL Server User Group Norway is happy to announce a Biml techie meetup for our members on Monday May 12th! Join us for a technically oriented introduction to Biml and a presentation on the use of Biml and auto generation of ETL in one of the biggest data warehousing development projects currently running in Denmark.

Visit Varigence for the full schedule with registration links for all the cities.

Volunteering at SQLSaturday #275 Copenhagen

SQLSaturday #275 Check-inLast 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.

SQLSaturday #275 SessionI 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 SponsorsDuring 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.

SQLSaturday #275 #SQLBREWThere 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!