Stop assuming wrongly and start assuming responsibility

T-SQL TuesdayThis month’s T-SQL Tuesday is hosted by Dev Nambi (@DevNambi) and the topic is assumptions: Your assignment for this month is to write about a big assumption you encounter at work, one that people are uncomfortable talking about. Every team has an elephant in the room. What happens if these big guesses aren’t true?

Stop assuming wrongly
“If you make an assumption, you suppose that something is true, sometimes wrongly.”

We’ve all assumed wrongly at some point. While it’s not always a big deal, sometimes the result can be disastrous. I’ve accidentally deleted all the weekly data in our production data warehouse because I assumed wrongly. (Thank goodness my assumption that we had working backups was correct!)

Most of the time I’m not aware that I make assumptions until something goes wrong, like when I realized I had deleted all that data. That’s when I stop and ask myself why I didn’t ask more questions, why I didn’t do more research, why I didn’t triple-check the logic?

The answer to why I assume wrongly is usually time. In the world of business intelligence there are just not enough hours in a day. When a business user asks for new data or a new report, their answer to “when do you need it?” is usually “yesterday”. We all want to deliver as much as possible in the shortest amount of time, which often leads to everyone making some kind of assumption without actually being aware of it. Business users assume IT knows all the business rules (“that’s supposed to be a negative amount”), IT assumes the business users have specified all requirements in detail (“that’s not in the requirements”), and we don’t take the time to sit down and go through it together.

Which leads me to my next point:

Start assuming responsibility
“If someone assumes responsibility, they begin to have responsibility.”

We need to take our time to collaborate, to ask those questions, to do that research and to triple-check that logic. Don’t assume that everyone else knows what you know, but share your knowledge. Don’t just assume that things work, but see how you can improve them. Work together.

I’ll start with me and make this a goal for me at work :)

SQLHangout #22 – Blabbering about Biml

When Boris (b | t) 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 (b | t) and André Kamman (b | t) for inspiring me to learn more Biml in their SQLSaturday sessions in Tampa and Copenhagen! :)

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.

For the examples in this post I have created a small Integration Services Project in Visual Studio 2013 that uses the package deployment model. It contains one SSIS package called SSISPackageConfigurations.dtsx and a Biml File called BimlPackageConfigurations.biml that will generate another SSIS package called BimlPackageConfigurations.dtsx. I also use a database called Configurations with the [SSIS Configurations] table, a database called BimlSource and a database called BimlDestination:
BimlPackageConfigurations Project

Create an XML configuration file Package Configuration
In all our environments (development / test / production) and on our development computers we have an XML configuration file located at C:\SSIS\Configuration.dtsConfig:

<?xml version="1.0"?>
<DTSConfiguration>
  <DTSConfigurationHeading><DTSConfigurationFileInfo/></DTSConfigurationHeading>
  <Configuration ConfiguredType="Property" Path="\Package.Connections[Configurations].Properties[ConnectionString]" ValueType="String">
    <ConfiguredValue>Data Source=.\SQL2012;Initial Catalog=Configurations;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue>
  </Configuration>
</DTSConfiguration>

In each environment it contains the Configuration for the Connection Manager to the server and database with the [SSIS Configurations] table where all other configurations are stored. First we need to add the Package Configuration pointing to this XML configuration file.

In SSIS I would open Package Configurations Organizer (SSIS → Package Configurations / right-click in Control Flow → Package Configurations), enable package configurations, click add, choose Configuration type XML configuration file, click Browse, browse to my XML configuration file, click Next, click Reuse Existing, call it Configuration, click Finish and click Close:
SSIS XML configuration file Package Configuration

In Biml I would add a PackageConfiguration to the Package:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="BimlPackageConfigurations" ConstraintMode="Linear">
 
      <!-- Added: PackageConfigurations -->
      <PackageConfigurations>
        <PackageConfiguration Name="Configuration">
          <ExternalFileInput ExternalFilePath="C:\SSIS\Configuration.dtsConfig" />
        </PackageConfiguration>
      </PackageConfigurations>
      <!-------------->
 
    </Package>
  </Packages>
</Biml>

The PackageConfiguration element named Configuration has an ExternalFileInput element with the ExternalFilePath attribute pointing to the location of my XML configuration file.

If you right-click on BimlPackageConfigurations.biml in the Visual Studio Project and click Check Biml for Errors, you will not get any warnings and you can right-click and click Generate SSIS Packages. However, when you open the new BimlPackageConfigurations.dtsx package (or close and re-open the SSISPackageConfigurations.dtsx package) you will get the error “The connection “Configurations” is not found. This error is thrown by Connections collection when the specific connection element is not found.” This is because we haven’t yet created the actual Connection Manager.

Create the Connection Manager specified in the XML configuration file
Next in SSIS I would right-click in Connection Managers, click New OLE DB Connection, click New, use the server and database specified in the XML configuration file, click OK, click OK again and rename the newly created Connection Manager to Configurations. The Connection Manager will then get the aqua triangle icon on it that indicates that a Package Configuration is used:
SSIS Connection Manager

In Biml I would add the Connections element below the Packages element:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="BimlPackageConfigurations" ConstraintMode="Linear">
      <PackageConfigurations>
        <PackageConfiguration Name="Configuration">
          <ExternalFileInput ExternalFilePath="C:\SSIS\Configuration.dtsConfig" />
        </PackageConfiguration>
      </PackageConfigurations>
    </Package>
  </Packages>
 
  <!-- Added: Connections -->
  <Connections>
    <OleDbConnection Name="Configurations" ConnectionString="Data Source=.\SQL2012;Initial Catalog=Configurations;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
  </Connections>
  <!-------------->
 
</Biml>

I like to add the Connections element after the Packages element simply because in BIDS / SSDT-BI the Connection Managers are located below the Control Flow, you can add this above the Packages element if you prefer. The attribute Name in the OleDbConnection element is the name we want to use for the Connection Manager, and the ConnectionString attribute has the same value as specified in the XML configuration file.

Again, if you Check Biml for Errors, you will not get any warnings and you can Generate SSIS Packages. However, when you reopen the BimlPackageConfigurations.dtsx package you will still get the same error: “The connection “Configurations” is not found. This error is thrown by Connections collection when the specific connection element is not found.” This is because Biml only creates the tasks and components necessary in the SSIS package. Since we haven’t yet created any tasks that use the Connection Manager we must force Biml to create the Connection Manager by adding a Connection inside the Packages element as well:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="BimlPackageConfigurations" ConstraintMode="Linear">
      <PackageConfigurations>
        <PackageConfiguration Name="Configuration">
          <ExternalFileInput ExternalFilePath="C:\SSIS\Configuration.dtsConfig" />
        </PackageConfiguration>
      </PackageConfigurations>
 
      <!-- Added: Connections -->
      <Connections>
        <Connection ConnectionName="Configurations" />
      </Connections>
      <!-------------->
 
    </Package>
  </Packages>
  <Connections>
    <OleDbConnection Name="Configurations" ConnectionString="Data Source=.\SQL2012;Initial Catalog=Configurations;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
  </Connections>
</Biml>

The ConnectionName attribute in the Connection element refers to the OleDbConnection attribute Name. When you Generate SSIS Packages and reopen the BimlPackageConfigurations.dtsx package you will now see the Connection Manager named Configurations with the aqua triangle icon on it that indicates that a Package Configuration is used:
SSIS Connection Manager

Create SQL Server Package Configurations that use the Connection Manager specified in the XML configuration file
Next I want to create the Connection Managers that will be used in the data flow source and destination, and these Connection Managers should get their values from the [SSIS Configurations] table.

In SSIS I would open Package Configurations Organizer (SSIS → Package Configurations / right-click in Control Flow → Package Configurations), click add, choose the SQL Server configuration type, choose the Configurations connection, choose the [dbo].[SSIS Configurations] table, choose the BimlSource filter, click Next, click Reuse Existing, call it BimlSource, click Finish, repeat all the steps for BimlDestination and click Close:
SSIS SQL Server Package Configurations

In Biml I would add two new PackageConfigurations:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="BimlPackageConfigurations" ConstraintMode="Linear">
      <PackageConfigurations>
        <PackageConfiguration Name="Configuration">
          <ExternalFileInput ExternalFilePath="C:\SSIS\Configuration.dtsConfig" />
        </PackageConfiguration>
 
        <!-- Added: PackageConfigurations -->
        <PackageConfiguration Name="BimlSource" ConnectionName="Configurations">
          <ExternalTableInput Table="[dbo].[SSIS Configurations]" />
        </PackageConfiguration>
        <PackageConfiguration Name="BimlDestination" ConnectionName="Configurations">
          <ExternalTableInput Table="[dbo].[SSIS Configurations]" />
        </PackageConfiguration>
        <!-------------->
 
      </PackageConfigurations>
      <Connections>
        <Connection ConnectionName="Configurations" />
      </Connections>
    </Package>
  </Packages>
  <Connections>
    <OleDbConnection Name="Configurations" ConnectionString="Data Source=.\SQL2012;Initial Catalog=Configurations;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
  </Connections>
</Biml>

These PackageConfigurations have ConnectionName attributes that refer to the OleDbConnection named Configurations. They have ExternalTableInput elements with a Table attribute that refers to the [SSIS Configurations] table. The most important thing to notice is that the Name attribute is also used as the filter in the [SSIS Configurations] table:
SSIS and Biml SQL Server Package Configurations

Create Connection Managers specified in the [SSIS Configurations] table in SQL Server
Next we have to repeat the steps of actually creating the Connection Managers.

In SSIS I would right-click in Connection Managers, click New OLE DB Connection, click New, use server .\SQL2012 and database BimlSource, click OK, click OK again and rename the newly created Connection Manager to BimlSource. I would do the same for the BimlDestination Connection Manager.

In Biml I would add two new OleDbConnections and force the creation of the Connection Managers by adding two new Connections to the Package:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="BimlPackageConfigurations" ConstraintMode="Linear">
      <PackageConfigurations>
        <PackageConfiguration Name="Configuration">
          <ExternalFileInput ExternalFilePath="C:\SSIS\Configuration.dtsConfig" />
        </PackageConfiguration>
        <PackageConfiguration Name="BimlSource" ConnectionName="Configurations">
          <ExternalTableInput Table="[dbo].[SSIS Configurations]" />
        </PackageConfiguration>
        <PackageConfiguration Name="BimlDestination" ConnectionName="Configurations">
          <ExternalTableInput Table="[dbo].[SSIS Configurations]" />
        </PackageConfiguration>
      </PackageConfigurations>
      <Connections>
        <Connection ConnectionName="Configurations" />
 
        <!-- Added: Connections -->
        <Connection ConnectionName="BimlSource" />
        <Connection ConnectionName="BimlDestination" />
        <!-------------->
 
      </Connections>
    </Package>
  </Packages>
  <Connections>
    <OleDbConnection Name="Configurations" ConnectionString="Data Source=.\SQL2012;Initial Catalog=Configurations;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
 
    <!-- Added: OleDbConnections -->
    <OleDbConnection Name="BimlSource" ConnectionString="Data Source=.\SQL2012;Initial Catalog=BimlSource;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
    <OleDbConnection Name="BimlDestination" ConnectionString="Data Source=.\SQL2012;Initial Catalog=BimlDestination;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
    <!-------------->
 
  </Connections>
</Biml>

When you Generate SSIS Packages and reopen the BimlPackageConfigurations.dtsx package you will see the two new Connection Managers named BimlSource and BimlDestination with aqua triangle icons on them that indicates that Package Configurations are used:
SSIS Connection Managers

Create a Parent package variable
Finally I would like to add a Parent package variable that gets the PackageID from the parent package and stores it in the variable ParentPackageID.

In SSIS I would first create a variable named ParentPackageID:
Parent Package Variable
Then I would open Package Configurations Organizer (SSIS → Package Configurations / right-click in Control Flow → Package Configurations), click add, choose the Parent package variable configuration type, specify the Parent variable PackageID, click Next, browse to the ParentPackageID Variable and choose the Value property, click Next, call it ParentPackageID, click Finish and click Close:
Parent Package Configurations
Parent Package Configuration
The variable named ParentPackageID will now have the aqua triangle icon on it that indicates that a Package Configuration is used:
Parent Package Variable configured in Package Configurations

In Biml this is a lot easier! You only need to add a Variable named ParentPackageID, set the required attribute DataType to Int32, and then add the attribute InheritFromPackageParentConfigurationString with the value PackageID:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="BimlPackageConfigurations" ConstraintMode="Linear">
      <PackageConfigurations>
        <PackageConfiguration Name="Configuration">
          <ExternalFileInput ExternalFilePath="C:\SSIS\Configuration.dtsConfig" />
        </PackageConfiguration>
        <PackageConfiguration Name="BimlSource" ConnectionName="Configurations">
          <ExternalTableInput Table="[dbo].[SSIS Configurations]" />
        </PackageConfiguration>
        <PackageConfiguration Name="BimlDestination" ConnectionName="Configurations">
          <ExternalTableInput Table="[dbo].[SSIS Configurations]" />
        </PackageConfiguration>
      </PackageConfigurations>
      <Connections>
        <Connection ConnectionName="Configurations" />
        <Connection ConnectionName="BimlSource" />
        <Connection ConnectionName="BimlDestination" />
      </Connections>
 
      <!-- Added: Variables -->
      <Variables>
        <Variable Name="ParentPackageID" DataType="Int32" InheritFromPackageParentConfigurationString="PackageID">0</Variable>
      </Variables>
      <!-------------->
 
    </Package>
  </Packages>
  <Connections>
    <OleDbConnection Name="Configurations" ConnectionString="Data Source=.\SQL2012;Initial Catalog=Configurations;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
    <OleDbConnection Name="BimlSource" ConnectionString="Data Source=.\SQL2012;Initial Catalog=BimlSource;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
    <OleDbConnection Name="BimlDestination" ConnectionString="Data Source=.\SQL2012;Initial Catalog=BimlDestination;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
  </Connections>
</Biml>

When you Generate SSIS Packages and re-open the BimlPackageConfigurations.dtsx package, you will see that you have got a Parent package configuration and the ParentPackageID variable with the aqua triangle, just by adding the attribute InheritFromPackageParentConfigurationString to a Variable.

Conclusion
This post is a basic introduction to Package Configurations and Connection Managers in Biml. It shows how to add an XML configuration file Package Configuration that points to the [SSIS Configurations] table where all other configurations are stored, how to get the other configurations from the [SSIS Configurations] table, and how to create the Connection Managers used in the package. It also shows how to add a Parent package variable. I hope new Biml developers will find it useful!

SQL Server, SSIS and Biml Data Types

The table below shows a simplified mapping between SQL Server, SSIS (SQL Server Integration Services) and Biml (Business Intelligence Markup Language) data types. The current version of the table does not include all type systems, all possible mappings or all data types, but is instead meant as a quick reference while developing and learning Biml. It was mainly made as a cheat sheet for myself, but I hope other new Biml developers will find it useful. All columns are sortable.

SQL Server
SSIS
Biml
bigintDT_I8Int64
binaryDT_BYTESBinary
bitDT_BOOLBoolean
charDT_STRAnsiStringFixedLength
dateDT_DBDATEDate
datetimeDT_DBTIMESTAMPDateTime
datetime2DT_DBTIMESTAMP2DateTime2
datetimeoffsetDT_DBTIMESTAMPOFFSETDateTimeOffset
decimalDT_DECIMALDecimal
floatDT_R8Double
imageDT_IMAGEBinary
intDT_I4Int32
moneyDT_CYCurrency
ncharDT_WSTRStringFixedLength
ntextDT_NTEXTString
numericDT_NUMERICDecimal
nvarcharDT_WSTRString
realDT_R4Single
smalldatetimeDT_DBTIMESTAMPDateTime
smallintDT_I2Int16
smallmoneyDT_CYCurrency
sql_variantDT_WSTRObject
textDT_TEXTAnsiString
timeDT_DBTIME2Time
tinyintDT_UI1Byte
uniqueidentifierDT_GUIDGuid
varbinaryDT_BYTESBinary
varcharDT_STRAnsiString
xmlDT_WSTRXml

References
Matija Lah (b | t) wrote a great and thorough post about SQL Server Integration Services Data Types. I recommend reading this article for a better understanding of data types and type systems in SSIS.

Devin Knight (b | t) created the SSIS to SQL Server Data Type Translations conversion chart that I frequently used while learning SSIS.

Interviews as Learning Experiences

T-SQL TuesdayThis month’s T-SQL Tuesday is hosted by Boris Hristov (@BorisHristov) 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 :)

Page 1 of 6 1 2 3 4 5 ...Last »