$ GetFileInfo [file name]
$ SetFile -c "" -t "" [file name]
Informatica does not have an equivalent of SSIS’s UNION component . This is a big problem for what I’m trying to do because a lot of the logs that I’m trying to load are in different folders (to represent the different web servers). Informatica requires 2 pipelines (see #3 below) to extract this data whereas SSIS can just have 2 source adapters in the same data-flow and UNION the data together.
SSIS’s method of loading multiple files (i.e. the "Multiple Flat Files Source Adapter") is a lot better than Informatica's. With Informatica you have to, externally to Informatica, build a list of files to process and then pass that list back into Informatica. To make this dynamic at runtime you would have to shell out to an external process to produce the file list. This is not pleasant – especially compared to SSIS’s very simple method of just specifying “*.log” in the source adapter.
I have a pipeline (built in both SSIS & INFA) that filters out all comment lines from the web logs, extracts all the individual fields (e.g. Timestamp, ClientIP address, URI, Referrer etc...), and inserts into a SQL Server table. The SSIS pipeline is working on ~150000 rows and completes in ~23 seconds. The Informatica method (which uses 2 pipelines cos of #1 above) takes ~45 seconds. Even 1 Informatica pipeline on its own (working on about half the records) took ~27 seconds. Bear in mind also that the SSIS pipeline was run from BIDS and as I have previously mentioned, BIDS places a large overhead on the execution of a package. I would suppose that Informatica does not have the same restriction. In short, SSIS seems a lot quicker!
SSIS’s method of dynamically setting the destination at runtime (i.e. configurations) is a lot better than the Informatica equivalent. With Informatica you have to configure each task to use the dynamic value, and setting up the value itself is a manual process because you have to manually handcraft what is termed a parameter list. SSIS does this using the configuration wizard. Let me say again, EVERY Informatica task that uses an external connection has to have a dynamic configuration set up using this method; with SSIS you do it in one place, on the Connection Manager.
Having being frustrated on lack of duplicate filtering in SSIS, I decided to take a work around, first dump the data in a temporary table and pick it up with "select distinct … “ in the next task. This work around works though but fails in instances that the filter had to be applied on some of the columns selected unlike “select distinct ..” that applies across all the selected columns.
Alas!!! SSIS provides this in the Sort Task and can be applied just as I want it in my project. Simply select the columns you want to filter on as the sort keys and check “Remove rows with duplicate soft values”. Voila! problem solved.
IMHO, I consider the sort task an overkill for this purpose but no other alternative unless the developer will not mind building a dedicated filtering task for SSIS. SQL Central provides samples on how to build SSIS task.
A client walked up to me after a recent presentation asking how to go about having a collaborative multi-mouse based application. I pulled back a bit before responding, quickly kicking around in my head. What will happen if I got multiple input device (mouse devices) in Windows? It should just work. Notebooks has had this features for years and it just work but then I gotta see what happens if I got more mouse.
Secondly, if it does work, how do I differentiate which device is in control in an application. They both most likely will generate same Windows event. – brain dead lock.
“Ehmm“ I fumbled to respond. “It’s possible but I can’t give you a solution straight up. Can we talk about this again in a couple of weeks.”
I bought an extra USB mouse device (already got a Mogo mouse) on my way home. Connected this up to a notebook and it all worked. All controlled same mouse as expected which left me with the second question. “How do I differentiate this in an application”.
Of course, Google to the rescue. After hours of google(ing) it with B’ing, I came across this article by P. Opdahl from on CodeProject.
It’s quite an interesting Read and done with C++(Not so happy about that, so quick to understand C#, VB and Python these days). I search some more and and more and came across Microsoft Multipoint SDK. How could I have missed this?
SDK is installed with sample application of which I find the Quiz sample most interesting.
The Microsoft Multipoint SDK can be downloaded here. Note: This won’t install on Windows 7 unless maybe you install in XP-Mode. I don’t have Intel-VT so this is no option for me.
More Resources
I blogged last week about a problem on Record Value Frequency in SSIS. I suggested in the blog post on how to go about taking care of this.
“The only way I could see out of this is to write a CLR function to achieve this which I am feeling so lazy to do but still has to be done.”
This I finally had time to do and pasted below is the simple CLR User Defined Functions (UDF)
static readonly Dictionary<SqlString, SqlInt32> myDic = new Dictionary<SqlString, SqlInt32>();
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString SvSequence(SqlString colValue)
{SqlInt32 nextValue = 1;
if (myDic.ContainsKey(colValue)) {myDic[colValue] += 1;
nextValue = myDic[colValue];
}
elsemyDic.Add(colValue, 1);
// Put your code here return nextValue.ToSqlString();}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString RestartSequence()
{myDic.Clear();
return new SqlString();
}
Notice the readonly used in the static declaration for the dictionary. This is enforced by the compiler for static class member as a requirement. I also had to create a reset function which IMHO is not that cool but I need a way to reset this as the value kept increasing each time the UDF is called.
dbo.RestartSequence ;
select AccountId, dbo.SvSequence(AccountId) from dump
I have always known that PDC event will be in November but not sure if the date has been fixed. Today, I had to start writing plans for November and realized I have no idea of the actual date.
After some Googling with ‘Bing over the net, found the dates to be from Tuesday, November 17th through Thursday, November 19th. Not so excited that it’s going to be in LA (LAX Convention Centre) again!. Would have preferred some other city like San Francisco or any other cool place Microsoft may think up.
Do join the wave @pdc09 and search for the tag name #pdc09 for the excitement buildup. Also register for the mailing list.
Mike Swanson had so much to say about it in his blog.
A dear friend I met at the summit earlier in the year.
He had this as cover of his presentation in China just after the summit. Hi Pal!
First I’d like to quote my recent tweet on this issue
Have always considered SSIS to be extremely flexible but lately found wrong. Nevertheless, yet to find any ETL too that can beat it
I think I may have been wrong to blame this on SSIS but rather on limitations of TSQL itself. My situation is a need to have frequency of a record value indicated in a column as indices as below
| Value | SEQNR |
| 1 | 1 |
| 2 | 1 |
| 2 | 2 |
| 4 | 1 |
| 4 | 2 |
| 4 | 3 |
I tried to use Rank() with partition as shown in snippet below but got a different result. Also ‘Bing(ed) this but came up void of a similar issue.
select value, Rank() over (partition by Value order by Value)This is like to use and inject into SSIS package. Doing this with combination of SSIS task is not quite straightforward.
The only way I could see out of this is to write a CLR function to achieve this which I am feeling so lazy to do but still has to be done.
Recently, I came across a need to copy all csv files generated from an SSIS process to a Unix server. FTP became handy for this purpose. The FTP SSIS task will not work with folder/directories – came up with error message
File Usage type of connection "xxxxxx" should be "FileExists" for operation Send
This error message is quite misleading. The interpretation is that once you the File Connection Manager being used in the FTP task has a property Usage Type and this Usage Type must be set to “File exists”. This automatically limits the usage of the FTP task to files rather than folder/directories.
The solution is to use the Foreach Loop Container task and place the FTP task within it. Literarily, the loop container enumerates all the files and place them in a variable. This variable is then used in the FTP task.
Foreach Loop Container with FTP task embedded
Loop Container configuration
FTP task configuration
‘TechEd is in its 10th year and is positioned to be the best yet! As promised, this is your pre-notification that the TechEd Africa 2009 website is now live and open for registration!
Be sure to take advantage of the Super Early Bird offer and forward this invite to your Colleagues and friends’
Be part of the experience. So much to discover. Hundreds of ways to learn.
Dates: 2-5 August 2009
Venue: Albert Luthuli International Convention Centre, Durban
For all Microsoft Tech•Ed registration and event-related questions,
e-mail us at: info@tech-ed.co.za
 |  |
| SUPER EARLY BIRD SPECIAL Offer expires 29th of May 2009 | Who should attend? Any and all technology professionals interested in exploring a broad set of current and upcoming Microsoft technologies, tools, platforms and services |
 |  |
| BOOK NOW. |
Picture from some of the Developer Events I attended recently. Just thought I should share this.
With Anders Hejlsberg (MVP Summit ‘09)
with Don Box (PDC ‘08)
With Scott Guthrie (Mr. Red Shirt – PDC ‘08)
with Ingo Rammer (PDC ‘08)
with Ken Spann (PDC ‘08)
Recently has a need to program authentication for an application using ADAM. This is something I have done in the past so got a good existing code I could leverage on.
Alas! ADAM would not install on Windows 7. I It kept returning “Out of Storage” error!
This has been a known problem in Windows Vista as can be seen in the blog post Shawn Cicoria – CedarLogic, Extemporaneous Mumblings
Tried out these tricks and still got another error message on execution of the adaminstall.exe
