Just a heads-up as I noticed today that the import behaviour of escaped double-quotes in a string is different in DTS and SSIS.

If we have the following single entry in a text file:

"xxx""999""xxx"

Using a Transform Data task in DTS, the resulting database entry will be:

xxx"999"xxx

Using a Data Flow task in SSIS we will get:

xxx""999""xxx

In both packages the double quote is defined as a text delimiter and apart from that there don't seem to be any relevant configuration possibilities to finetune the behaviour.

It seems DTS interpretets this as an intentionally escaped double-quote, and removes the extra characters automatically. SSIS on the other hand assumes that this is a valid string entry.

I'm currently converting DTS packages to SSIS at the moment and as the import behaviour has changed in this way, my solution is to run an immediate update (ExecuteSQL task) on the destination data and replace
adjacent double quotes with a single one - not nice. I could alternatively strip them out in the data flow, using a derived column transformation - also not nice. Hopefully this'll be exposed as a property of the Flat File connection manager some time.

I've become a bit too much of an enthusiast about using SSIS for everything recently and I came unstuck with a simple file iteration problem........

I simply wanted to iterate through a set of network directories and do some file copying. Basically I needed to sync up the UAT and live directories of source files, so that the testing in the UAT environment could be a true parallel test of live. So, if the process came across a recent file in a live directory that wasn't in the corresponding test
subdirectory, it should be copied over.

I agree that this sounds like a straightforward issue. It's the sort of thing I can easily do in VBScript or VB6.0 but I'm trying to do everything in SSIS at the moment to centralise the code for maintenance, so I decided to do it that way.

In the designer we see that there is a task which seems custom-made for this type of problem - "Foreach Loops based on File Enumerator" so away I went and created the package below:



In the SSIS package above, the outer Foreach_FeedDirectory loop iterates through a recordset of directories and stores the directory path in a local variable. The inner ForeachFeedSourceFile loop iterates over the files in the specific directory.

Here's where you begin to come unstuck - all the usual file attributes you'd expect to see are simply not exposed to the Foreach File Enumerator. In the SSIS loop we can get the filename with or without the full path / extension and that's about it - no "Created Date", no "Last Modified Date", no "Size" etc etc. To get any of these useful attributes you have resort to scripting!

So in the package shown above, there is an inner script task which uses System.IO.FileInfo, System.IO.File.Exists, System.IO.File.Copy etc to decide whether to do the copy or not and applies to the file name found in the inner Foreach File Enumerator.

Let's recap: I am looping through each directory. Once I find a diectory I loop through the files. Once I find a file I use the FileInfo method to get the file info in script, then copy over if it is recent and if it doesn't exist in the destination directories.

On my 33 directories, some of which contain 1000s of files, this takes approx  1.5 to 2 hours to run in SSIS

As this is a process I need to run often, this is a complete disaster . I can see that the iteration over the files is the bottleneck and this gives precious little useful info in the task, so as a trial, I start thinking of c# as a replacement. Now I'm definitely not a c# developer (and never will be ), but I managed to knock together the console application below to replace the entire package in an hour or so using my pal Google. Amazingly it c# does the same task but takes just 70secs!!!!!!

So - if you're just doing iterative directory / file manipulation and have to incorporate scripting, I'd definitely recommend resorting to c# rather than SSIS.

C# code:

        private static void CheckDirectory(String sLiveDirectory, String sTestDirectory)
        {
            Console.WriteLine("Processing: " + sLiveDirectory);
            DirectoryInfo dirCustom = new DirectoryInfo(sLiveDirectory);
            FileInfo[] filCustom;
            filCustom = dirCustom.GetFiles();
            foreach (FileInfo filFile in filCustom)
            {
                TimeSpan ts = DateTime.Now - filFile.CreationTime;
                if (ts.Days < 2)
                {
                    if (!File.Exists(sTestDirectory + filFile.Name))
                    {
                        File.Copy(filFile.FullName, sTestDirectory + filFile.Name);
                        Console.WriteLine("Copying: " + filFile.FullName);
                    }
                }
            }
        }

 

There may come a time when you are not in control of the input file format and it is decidely non-standard. I recently had to process such a feed file which had a metadata row at the start and the end of the file. The file could be represented something like this, with only the x,y,z records being of importance (along with the column headers if we can get this info).

Col1	Col2	Col3
xxx start
x	y	z
x	y	z
x	y	z
xxx end

The problem is that for the red rows there is only one column: "xxx start" and no comma separators, while the second row would be "x,y,z".

This is not an easy problem to describe but I tried various keyword combinations on Googling to see how others were coping with this issue.

Some people advise using the script task: http://www.sql-server-performance.com/article_print.aspx?id=1056&type=art
Some use the Conditional Split: http://www.sqlis.com/54.aspx
I've also seen packages which call vbscript code to open the text file, read all the lines and then write them back into the text file minus the problemmatic rows.

All of these are interesting technical solutions, however they weren't suitable for a lazy guy like me

1. I didn't want to write any code
2. in my case there are dozens of columns, so I wanted the designer to define the columns for me by looking at the text file, rather than defining them one-by-one by hand
3. finally I didn't want to use script outside the package which would make maintenance more difficult.

In short, my aim was to have the entire process encapsulated in a single package with no calls being made to outside processes, and have it as simple as possible to build and maintain.

So, how to do this?

The way I do it now is quite simple (below):

 

Initially I import the entire file into a staging table. The Flat File source is defined as "Ragged right" and has one column which is 8000 chars long. This is imported into a staging table which has one column - defined as varchar(8000). This way the entire file is always imported successfully, along with the 2 problem rows.

Next we remove the extra rows. In my case this is a simple delete statement as the length of the problem rows is constant and is significantly shorter than a data row. You'll need some way of distinguish this row(s) from the others and if you're lucky it'll be as simple as my case. If not, you might have to filter using the column delimiters or use some other similar logic.

Next I export to a temporary file. This is a Flat File defined as "Ragged right", again with one column. The staging table above is the source and data is exported from it to the staging Flat File.

Finally, this staging file is treated as a standard Flat File and imported as per usual in another data flow task. This means that the column names can be determined from the source file by the designer.

It's all really simple and transparent. If / when a recordset source can be used, we can do away with the persisted staging Flat File to make it even neater in the future.