I don’t know about you, but just occasionally I stumble across something totally unknown to me in a product that I have been using in various incarnations for years and think “How did I not know about that?” I do DTS or SSIS or hell, just data migrations A LOT, seemingly at the moment more than I do ‘real development work’ although I endeavour on a daily basis to correct that imbalance. It therefore came as a shock to me to find that your ‘Data Sources’ in the connection manager windows have more properties than you would think, if you double click on a data source its a case of:-
Yep… you can set the source and….. Well, that’s about it.
Mostly this is all you need but of late I’ve been doing some more advanced things, much changing of the connection strings at run time using scripts as to be honest I didn’t not realise there was a better way.
A few weeks ago I came to one of my long running projects and tried to open the .log file as there was a query come in from the client about the status of the job, ordinarily they get an email and had not received one. I opened the file to check what had occurred only to discover that the file was huge, I mean “biblical” in size but without the strange beardy men and plagues of locusts. Even my editor of choice Sublime struggled to give me any coherent analysis of the file so I decided that I would have to do something about this ongoing problem. I decided that my first plan of attack should be to limit the size of these output files, this I would achieve by log the outputs of a days ETL run(s) to a distinct file for each day. Its not hard, a little bit of script like so and you’re done right?
var cn = Dts.Connections["CompanyDivisions"];
var cdFileName = Dts.Variables["coDivFileName"].Value.ToString();
var cnString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"EXCEL 8.0;HDR=YES\";", cdFileName);
cn.ConnectionString = cnString;
As you can see we:-
- Start off by getting a reference to our connection object
- Obtain the name of the file that we want to use from a variable
- Compile a connection string (Eugh!)
- And assign this into the connection object
Its only four lines of code right? But something jarred with me, anyone who reads my blog regularly is probably aware that :-
- I’m a rambler, not of the walking variety… but of the digression type. I see a point and I make my way toward it, somewhat circuitously!
- I like to experiment, I don’t like to make do with a solution that I feel is not elegant and would prefer to dig a little in the hope of finding a better way.
This was one such time that I decided to dig, I always feel that scripting is the weakest solution especially when I am in ‘Set’ mode which SQL should always put you in. Don’t get me wrong, sometimes it is unavoidable to revert to RBAR solutions but in this instance I felt that I had to dig…
Quite by accident I had my editor set up slightly different to usual, when using Visual Studio for full blooded development I think it’s practically unavoidable to not have the properties tab docked open but when in ‘Set’ mode I rarely have the properties window open. By happy accident I must have had my connection selected and I noticed in the properties window a few more properties than I tend to associate with a connection, this particular one got me all excited:-
OK. So not THAT excited, its not Muse or anything!
So drilling into this ‘Expressions’ property a little deeper I found that we have a traditional name value pairs approach with one column being a ‘Property Name’ and the other being an ‘Expression’. Surely it could not be that simple I thought, but it really was. So instead of my script from above I was able to configure my data source using this expression (clicking on the ellipse takes you through to an expression builder should you find yourself wearing short pants on that day;-P).
Yes, its a different expression to that that I was building in the script, the reason for that is …. I was changing it! Also in my defence the script component only seems to be able to update connections using the connection string properties for some unfathomable reason. There are of course many other properties that can be resolved at run time like this and even better this property is on most objects in the DTS library.
Go forth, awesomeness awaits you….