Thursday, August 30, 2012

SSIS Date Error Going From SSIS Variable to Stored Procedure using OLE DB

Quick Description:  An SSIS package which populates an SSIS variable of type DateTime and passes the variable as a parameter to a stored procedure (expecting DateTime) using OLE DB blows up with the error below.
[Execute SQL Task] Error: Executing the query "exec <procedure>..." failed with the following error: "Invalid time format". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
One Solution:  In my situation it turned out to be another run-in with my nemesis, the deeply confusing datatype mappings in SSIS (2012 in my case).  According to  this Microsoft article the mapping for SQL Server DateTime  is DT_DBTIMESTAMP.  For an Execute SQL task calling a stored procedure (using OLE DB) expecting a DateTime parameter, however, this blows up with the error above.  Setting the parameter to type DATE (not DT_DATE), fixed the problem for me, and the parameter is populated with a full Date/Time (including fractional seconds).

Tuesday, August 21, 2012

Quick C# Code to get the Current Windows User in a WebApp and do Something (like a lookup against a Database) with it.

I (not actually that recently anymore) changed positions from IT to development, and since it will probably be years before I'm an expert, I haven’t had much to post, lest I get another “Duh” comment.  I have no pride, so I’m going to start posting what I’m sure are ridiculously basic snippets in case they're helpful.

This isn’t particularly advanced.  I wanted to capture the windows username of the current user minus the domain, and look it up against an employee database to find employee info.  Basic, but can be cannibalized for a lot of basic operations on a username.

Things not to do:  For a WebApp, don’t get  WindowsIdentity.GetCurrent().Name;  this will appear to work while you’re debugging, but once it’s running, it will pull the User Account of the application pool.   I haven’t been able/didn’t try to test kerberos/pass-through authentication with it, but I’m guessing in that case the app id would return the current user id – still seems like a lot more work than getting it at the front door.

Things I don’t understand:  So many things, but in particular… How come Microsoft recommend using “using” specifically excluding IDisposable.Dispose and try…catch, but when I run code analysis it complains

Warning    7    CA2000 : Microsoft.Reliability : In method 'mymethod' call System.IDisposable.Dispose on object 'connectionobject' before all references to it are out of scope.

Weirdness notwithstanding, In the example below, I'm calling a stored procedure to look up employeeid in a database table, but any operation could be substituted.

using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Security.Principal;

      protected string GetEmployeeIDfromUname()  //returns a string, if you don't want to return anything use protected void
{
//Do not get the string below unless you want to know who the application is running as
                   //String empuname = WindowsIdentity.GetCurrent().Name; 
                   //Get Windows UserName of current user
string strEmpUname = HttpContext.Current.User.Identity.Name.ToString();
string strEmpUname parsed = strEmpUname.Substring(strEmpUname.LastIndexOf('\\') + 1); //Parse username to remove the domain name (remove everything up to and including the \)
string strEmployeeID; //variable to hold the return value;

//This section can be replaced by whatever action you want to do.
                      using (SqlConnection connectionEmpID = new SqlConnection("Your Connection String")) //Instantiate the connection and build the command as executable code and parameters 
{
SqlCommand cmdEmpID = new SqlCommand();
cmdEmpID = new SqlCommand("YourProc", connectionEmpID);
cmdEmpID.CommandType = CommandType.StoredProcedure;
//Pass the parsed username to the procedure
cmdEmpID.Parameters.Add("LOGINNAME", SqlDbType.VarChar).Value = strEmpUnameParsed ;
SqlParameter empidret = cmdEmpID.Parameters.Add("@EMPIDRET", SqlDbType.Int);
//define the output (Employee Number)
empidret.Direction = ParameterDirection.Output;
cmdEmpID.Connection.Open();
cmdEmpID.ExecuteNonQuery();
strEmployeeID = cmdEmpID.Parameters["@EMPIDRET"].Value.ToString();
}

return strEmployeeID;
}

C# simple mailto: link set to return value of a method

 

Quick Description:  A quick example of some methods to build a dynamic mailto: string for c#

I know mailto: links are not exactly the future, but for my purposes, they're very handy for handling things like adding some error information context to the "Click here to get help with this error" button everyone (right?) puts on their custom error page.  .Net has its own methods for messaging if you want to get emails in the background, but for things like error pages, it's nice not to have to assume the application is in a happy state if a user wants to tell you more about the ugly thing that just happened (hopefully it doesn't happen often, and obviously, the system logging happens in the background).

So, that being said, using the asp:HyperLink allows you to use its server side methods.    Below is a simple example of adding exception information

 
//do this work on the pre-render event of the hyperlink object.
protected void HyperLinkMailToMe_PreRender(Exception ApplicationException)
{
string strMessageBody = CheckNullExceptionInformation(CurrentException.Message, "Exception Message");
string strErrorID = "if you give errors unique ids";
string strMailtoLink = "mailto:myemailaddress?subject=Information regarding error " + strErrorID + "?Body=" + strMessageBody
HyperLinkMailToMe.NavigateUrl = strMailtoLink;
}
//check that the exception property is not null
public static string CheckNullExceptionInformation(object ExceptionProperty, string ExceptionPropertyLabel)
{
if (ExceptionProperty == null)
{
return ExceptionPropertyLabel + " contains no information";
}
else
{
return ExceptionProperty.ToString();
}
}

QlikView Date Headaches

Short Description:  In QlikView, some dates formatted with the date or date# functions don't load as prescribed.

I often run into date display issues with QlikView and wonder how it handles dates internally.  I wonder this because I relatively frequently run into the situation where I run a load script and date fields default to date format in the primary file, but on saving the product of a load script to a qvd file dates go wonky and start displaying as integers, or datetimes display as dates and/or vice versa.  I have some trouble understanding how data types "degrade" between the load and the save when being consumed by the *same application*, but explicitly forcing them usually works.

Queue today's problem:  In loading a relatively simple script with a few joins, all the date fields imported differently (even 'mm/dd/yyyy' in the same column as an int date, so...fun).  No problem; run them through date(field, 'MM/DD/YYYY') in the last operation.  Not so much, but surely date#(field, format) will work?  That was a no.  After much frustration, the culprit appears to have been a datetime field loaded from a query that used the tsql MAX operator.  Forcing it to datetime immediately after loading from the tsql was the only way I found to get around it.  Forcing it later in the script/in a different operation/right before saving to the qvd did no good.  

Your mileage may vary: but in the case of date fields (in QlikView) that just won't behave even when explicitly forced, it may make sense to quickly check if the initial load from the source system contains any aggregation operators.

Thursday, August 2, 2012

Things that SQL Server Data Tools makes easy as compared to BIDS

After all the hoopla about 2012 SSIS (and its dev environment) being so much better than 2008, I was originally a bit disappointed with the new version (other than fixing some of the lineage ID issues which is awesome), but after using it for a while there are several things that just work in the new version, but were painful before.

Microsoft's list of the official enhancements is here and there are some big things fixed and added (love the dashboard), but there a few much smaller improvements that have quietly fixed things that used to make me tear my hair out.

The little things that are a huge deal to me:

  • Ctrl-Z!  I've always been mystified as to why undo didn't exist, and after all this time still find myself attempting ctrl-z in BIDS for 2008 R2 out of habit formed by every other application ever.  Imagine my surprise when I instinctively hit ctrl-Z and got an undo before I could even remember that it's never worked before.  I have missed you so much ctrl-Z, please don't leave me again!
  • Saving the package:  "save a copy of package..." now defaults to the last type of save you did instead of having to change the selections every time.
  • You can hit enter in an annotation, and it expands to a point while entering data.

They're small things, but they make it feel like a grown-up environment.

Things I wish had been fixed:

  • Moving connectors between objects:  If I have a fancy conditional on a connector/precedence constraint and need to add or remove an object/sequence in the flow, it would make my life so much easier if I could re-position the connector rather than delete and re-create.