|
(Almost a programming question.)
Most of my current work involves ETLs moving data from place to place (currently in SSIS ).
Often that means copying files from place to place -- primarily copying from NAS to local disk, but also extracting from a ZIP or TARBALL to local disk.
Sometimes it means determining which of a group of files is newest and giving it a generic name when it's copied -- e.g. foo_2017-12-10.csv ==> foo.csv
I find myself on the brink of defining a file management scripting language so we can describe the desired action in a high-level way.
Among the various techniques we've used so far include:
0) Using SSIS' built-in file handling component which is very limited in its abilities.
1) Writing a C# script and either:
1.1) Using System.IO.File.Copy and its ilk.
1.2) Using a System.Diagnostics.Process to execute COPY and 7ZIP commands .
What I've come up with so far is beginning to resemble LISP.
EXTRACT ( COPY ( NEWEST ( LIST ( DIR ( "\\serverx\widgetco\" ) , "foo*.zip" ) ) , "foo.zip" ) , "foo.csv" )
There are several other features I am considering, particularly only copying a source file when it is newer than the file currently at the destination and raising an alarn if it's older.
I need to be either pushed over the edge or pulled back.
Do I need to proceed to develop this thing?
Does anyone out there know of some existing tool (with API so I can integrate it into SSIS) that already does this sort of thing?
|
|
|
|
|
I vaguely remember a Microsoft tool called BizTalk that could be used for developing routing for files. The idea IIRC was to enter the CRM market.
They used to have a trial edition for this, so testing it should not cost anything out of pocket.
If you have an important point to make, don't try to be subtle or clever. Use a pile driver. Hit the point once. Then come back and hit it again. Then hit it a third time - a tremendous whack.
--Winston Churchill
|
|
|
|
|
You could call PowerShell,if you are on 2012r2/2016 windows server...
Best option for working with Files in SSIS - SQLServerCentral[^]
You could store the processed file names in a db or log the errors to a txt file...
Caveat Emptor.
"Progress doesn't come from early risers – progress is made by lazy men looking for easier ways to do things." Lazarus Long
modified 10-Dec-17 14:18pm.
|
|
|
|
|
Sure, but I'd still have to implement a higher-level language, and I'm not about to do that in PowerShell.
And we already log to a database.
To be clear, I have very little experience with PowerShell. However, the idea of using PowerShell as the front-end might be worth investigation.
Edit:
Looking into developing cmdlets now -- it may be the way to go.
modified 10-Dec-17 15:25pm.
|
|
|
|
|
Whoa man you're over thinking this thing. Get a grip man this [^] is what you need!
Someone's therapist knows all about you!
|
|
|
|
|
I began working on PIEBALDBASIC a few years back, before common sense called a halt.
|
|
|
|
|
Common sense or hair lose?
Someone's therapist knows all about you!
|
|
|
|
|
I don't anticipate hair loss. My father still has his in his 80s and his father had his into his 90s.
|
|
|
|
|
I was thinking more like pulling it out due to frustration?
Someone's therapist knows all about you!
|
|
|
|
|
Nah, I give up pretty easily.
|
|
|
|
|
PIEBALDconsult wrote: What I've come up with so far is beginning to resemble LISP.
Ew, no! Consider using a fluent syntax:
Dir("\serverx\widgetco\").List("foo*.zip").Newest().Copy("foo.zip").Extract("foo.csv");
Consider that your nudge over the edge.
|
|
|
|
|
Not a bad idea, I thought about it and thought maybe it would be harder to parse, but maybe it would be easier. So maybe FLINQ ?
On the other hand, my fevered imagination has spewed forth the possibility of merging two pipelines together:
COPY ( MERGE ( LIST ( DIR ( "\\serverx\widgetco\" ) , "foo*.csv" ) , LIST ( DIR ( "\\servery\acme\" ) , "bar*.csv" ) ) )
But that rabbit hole leads to implementing UNION and INTERSECTION , and my eyes glaze over. YAGNI!!
|
|
|
|
|
PIEBALDconsult wrote: maybe FLINQ ?
Exactly: LINQ and File Directories (C#) | Microsoft Docs
Though I haven't looked into any of their linqs (harhar) it might be a start, throw in some extension methods, and voila.
PIEBALDconsult wrote: But that rabbit hole leads to implementing UNION and INTERSECTION , and my eyes glaze over.
Heck, why not -- if you stick with IEnumerable<File> or something like that, then Union, Join, and Intersect should "just work".
This could turn into a cool little library. A quick google on "file linq" did not discover any existing implementations other than that first link which seems to be cloned here.
|
|
|
|
|
Marc Clifton wrote: throw in some extension methods, and voila.
Hmmm, interesting idea.
Allow the "script" to be actual calls to the Extension Methods and compile to a .net asembly -- Compiling Source Code from a String[^] -- sounds dangerous. It doesn't require parsing on my part at all. I like it.
Edit:
Further thought leads to considering a class with the required methods and then tack the "script" onto it in a dynamically-compiled class:
using ( Flinq f = new Flinq() ) { try { f.DIR("...").LIST("...") ... ; } catch { f.LOG ( ... ) } }
Need to protect against Little Bobby Tables, but otherwise what could possibly go wrong?
modified 10-Dec-17 23:31pm.
|
|
|
|
|
PIEBALDconsult wrote: but otherwise what could possibly go wrong?
Looks good to me!
|
|
|
|
|
Dropping back and punting...
What I got working today has the scripts looking like:
DIR("C:\temp\") LIST("foo*.csv) NEWEST () DIR ("D:\data\") COPY ( "foo.csv")
This is much simpler to implement by using a regular expression to split up the segments and keep Little Bobby Tables from attacking.
|
|
|
|
|
Upstate New York - outside Albany. approx. 2 inches.
Kids are looking forward to enough snow this season to make forts and snow men with. Good times.
|
|
|
|
|
Friday - us too here in Atlanta Hopefully, it will be the last.
Charlie Gilley
<italic>Stuck in a dysfunctional matrix from which I must escape...
"Where liberty dwells, there is my country." B. Franklin, 1783
“They who can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety.” BF, 1759
|
|
|
|
|
Which side of Atlanta? I'm in the northwest metro area and we got 8 inches of snow.
Only time I can remember more was the blizzard of 1993, just after my daughter's first birthday.
|
|
|
|
|
Even the Netherlands has snow.
I hope it goes away soon or I can prepare for five hour traffic jams
|
|
|
|
|
Confirmed, and as you probably know it's alarm code orange.
Keep on jamming
|
|
|
|
|
Half of the year is covered in codes here.
Not that we're a country with unpredictable and harsh storms..
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
About a foot outside my door, here in mid-Wales... the dog loves it!
|
|
|
|
|
About an inch here - the cat loathes it!
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Is it just a coincidence ... A and P so close together?
|
|
|
|