DEV Community: John Baker

Let ChatGPT Index Your LaTeX Documents

John Baker — Fri, 22 Sep 2023 21:16:55 +0000

Cross posted from my blog: Analyze the Data not the Drivel

Indexing documents is an ongoing chore. You would think picking out "good" book or article index words would be a trivial pseudo-sampling-sorting task, but that is not the case. Building a useful index requires something like "comprehending" the text. You must read the document, understand what's in it, and then judiciously select words that elucidate, navigate, and summarize the contents. Until recently, coding "reading comprehension" has been beyond my meager means. Then the other day, I had a brain fart; maybe ChatGPT can index LaTeX documents.

As a test, I decided to index my blog post Sympathy for Ptolemaic Epicyclers. For years I have been converting my WordPress blog posts to LaTeX with my J-based hack WordPress to LaTeX with Pandoc and J and storing the generated LaTeX code on GitHub. Here is the LaTeX for Ptolemaic Epicyclers. With code in hand, I logged into ChatGPT 3.5 and had this chat.

I first asked ChatGPT to summarize Sympathy for Ptolemaic Epicyclers.

Me: Please summarize the following text.

... upload of LaTeX code for Ptolemaic Epicyclers ...

ChatGPT: The provided text discusses the historical perspective of astronomy and the Ptolemaic system. It highlights that often, older theories are unfairly portrayed as ignorant and backward, whereas in reality, they were based on empirical observations and provided reasonable approximations. The text points out that the transition to new theories typically happens when the new model demonstrates its superiority in making predictions.

The author also mentions their recent work on a J script called "riseset," used for calculating when International Astronomical Union (IAU) stars culminate in their backyard. They explain that astronomical calculations, both in the past and today, are complex and require constant adjustments.

In conclusion, the text offers sympathy and respect for ancient astronomers who worked with limited tools and still made valuable contributions to understanding the cosmos. It emphasizes that these astronomers were not morons but rather practical philosophers trying to make sense of the observable universe.

If you read my blog post, you'll see that ChatGPT's summary is dead on. Also, ChatGPT's concluding remarks are better than mine. No wonder ChatGPT is scaring credentialed pseudo-intellectuals everywhere. I then asked.

Me: Please pick out good words to index from the text that you just summarized.

ChatGPT: Here are some good words or phrases to index from the summarized text:

Ptolemaic system, Old theory holdouts, Copernicus, Heliocentric model, Aristarchus, Sun-centered models, International Astronomical Union (IAU), GitHub, Astronomical calculations, Empirical observations, Modern calculations, N-body celestial mechanics, Gravitational theories, Backyard amateur astronomer, Ancient epicyclers

You can use these words or phrases as entries in the index of your LaTeX document to help readers easily find relevant information.

You'll notice that ChatGPT didn't just pick out words, it synthesized useful phrases that eerily sum up the contents of my post. I have no objections to the generated phrases even though I doubt I would have come up with them myself. I was impressed. This thing is a great
sounding board for writers.

Feeling lucky I decided to go for broke and ask ChatGPT to insert LaTeX indexing codes into my document.

Me: Please insert the latex index codes for the words you selected in the text I provided.

ChatGPT: Certainly! Here are the LaTeX index codes inserted into the provided text for the selected words:

... download of modified LaTeX code with \index{} commands inserted
...

ChatGPT effortlessly inserted the index codes into my document, and we concluded our chat with:

Me: Thank you

ChatGPT: You’re welcome! If you have any more questions or need further assistance, feel free to ask. Good luck with your LaTeX document!

Ethanol is Excrement in the Gas Tank

John Baker — Sat, 21 Jan 2023 18:40:18 +0000

Cross posted from my blog: Analyze the Data not the Drivel

Like many U.S. government programs, the blended corn ethanol mandate has done little to address its avowed goals, i.e., strengthen energy security, reduce carbon emissions, and lower prices for consumers. By some
accounts it, consumes more energy than it produces, increases consumer prices, distorts food production, wastes vast tracts of arable land, and contributes to groundwater and aquifer depletion. What's not to like?

The corn ethanol program has been called a boondoggle, by just about anyone that isn't directly cashing ethanol checks, but instead of shutting this abomination down, the idiots in Washington are being pushed by their donors (yes there is a big ethanol lobby) to expand corn ethanol production, increase the amount of ethanol blended into gasoline, and keep on screwing the public in perpetuity. As it's currently constipated,¹ the ethanol mandate mainly buys votes in the Midwest and funnels billions of government dollars to ethanol refiners and their investors. The ethanol program is pure squealing pork. You almost have to admire the rapacious cabal that's sold this stupid program to an even dumber public.

Like many beleaguered citizens, I do my best to ignore government
stupidity. I simply don't have the time, energy, or patience, to engage with the mental, moral, and cosmetic pygmies that infest government. Unfortunately for me, and you too, the ethanol mandate is hard to ignore because of the "mandate" bit. If you doubt this go out and try and buy some ethanol-free gasoline. You will quickly discover that ethanol-free gas is not always available, and even when it is; it's more expensive and comes in fewer octane ratings than ethanol-polluted gas. Some gas stations sell ethanol-free gas, but hardly any sell premium (octane 91 and above) ethanol-free gas.

You may ask, "How about letting gas stations sell whatever gasoline blends their customers prefer? If the public prefers pure to ethanol-contaminated gas, then so be it."

Oh, you poor naïve dolt. If you think we're living in a "free market" just pour gas, pure or ethanol, over yourself and strike a match! Virtually everything on sale is regulated in one way or another. The "free market" is as real as LGBTQ werewolves. In tiny regulator minds, consumer choice must be constrained because unfettered consumers may not buy your crap! Big ethanol knows consumers prefer comparably priced pure gas to their poisoned alternative because:

Ethanol-free gas gets better mileage.
It burns at lower temperatures in engines reducing wear and tear.
Is less corrosive and damaging to engine parts.

If corn ethanol met its promises, you could make a case that it's worth damaging engines, draining aquifers, and restricting consumer choice but it doesn't. As I said the program is pure pork. It's not designed to benefit the public and it doesn't. And, barring an electoral miracle that tosses all the corrupt congress critters that voted for this affront out of office, we're going to be saddled with ethanol in our tanks forever.

So, what can you do to limit the ethanol excrement in your gas tank?

If money isn't an issue buy an electric car or always fill up with ethanol-free gas. If money is an issue remember; dilution is the solution to pollution. I alternate between pure and ethanol gas when I fill up. This is cheaper than pure gas and it keeps the ethanol concentration down.

The Python function ethanol_concentration (see below) estimates
my average ethanol gas tank concentration assuming:

I always fill up when the tank is half-empty (I'm a pessimist).
I start with a full tank of ethanol E10 (10%) gas.
I alternate between pure and ethanol gas.

If spreadsheets are more your bag this sheet carries out the same calculations.

When you run this function for the dilution schemes:

t = 1 always use pure gas
t = 2 alternate between ethanol and pure
t = 3 one pure for every two ethanol
t = 4 one pure for every three ethanol

You get:

(1, 10, 0.2, 10, 1.5777218104420236e-29)
(2, 10, 5.066666666666666, 10, 3.3333333333333335)
(3, 10, 6.728571428571428, 10, 4.285714285714286)
(4, 10, 7.553333333333333, 10, 4.666666666666667)

Observe that alternating, as expected, cuts the ethanol concentration in half and that the concentration creeps up as you put less and less pure gas in your tank. There's still excrement in your tank --- just less of it.

def ethanol_concentration(F, E, T):
    r"""
    Compute average ethanol concentration over:
      F = number of fill ups
      E = ethanol concentration
      T = tank filling 

    example:
      ethanol_concentration(100, 10, 2)
    """

    # initial tank concentrations - start with ethanol gas
    c = [0] * F
    c[0] = E

    # gas ups - 1 ethanol, 0 ethanol free
    g = [min(1, (1 + i) % T) for i in range(F)] 
    g[0] = 0

    # calculate concentrations
    for i in range(len(c)-1):
        c[i+1] = 1/2*(c[i] + E*g[i+1])

    return (T, E, sum(c)/len(c), max(c), min(c))

And I mean constipated. Government only serves the interests of
the donor class. If you aren't punting serious moola to your
"representative," he, she, or trans-it, doesn't give a ding-dong
damn about your tiresome little problems. ↩

"Managing" a SQLite Database with J (Part 2)

John Baker — Tue, 09 Aug 2022 16:07:00 +0000

Cross posted from my blog: Analyze the Data not the Drivel

When faced with unfamiliar program code Roger Hui, the cocreator of the J Programming Language would sometimes wipe out all the code comments. He told me this forced him to concentrate on the actual code and not the opinions of previous developers. Roger was an exceptional programmer; he knew that program comments are often dated, misleading and wrong. And, the only way to "correct the comments" is to read the code; so why not just read the code and skip the philosophy? Roger's comment purges underscore an important point. However you document your code, it must be better than the null or empty document.

With this in mind, I wrote a few versions of this post, but I hated them all. Then I realized that jodliterate PDF documents mostly do what I want. So, instead of rewriting MirrorXref.pdf, I will make a few comments about jodliterate group documents in general. If you're interested in using SQLite with J, download the self-contained GitHub files MirrorXref.ijs and MirrorXref.pdf and have a look.

jodliterate Group Documents

jodliterate group documents are directly generated from JOD
dictionaries using J, pandoc, and LaTeX. Group documents have three major parts.

An overview.
Complete typeset source code.
A global word index.

The source code and index sections are self-explanatory, but a few
remarks about overviews are warranted.

The overview might have several subsections. There's usually a
hyperlinked Interface subsection. J doesn't have formal interfaces like many programming languages. The words listed in the Interface section are words you will use when running the script. The Interface section highlights where to get started.

For complex scripts, there is usually a Using subsection MirrorXref.pdf's Using subsection is typical.

Finally, there might be a Code Hints subsection. Code Hints are lists of hyperlinked words that you should heed.

For more, take Roger's advice and read the code.

"Managing" a SQLite Database with J (Part 1)

John Baker — Tue, 09 Aug 2022 15:55:39 +0000

Cross posted from my blog: Analyze the Data not the Drivel

SQLite, by many estimates, is the most widely deployed SQL database system on Earth. It's everywhere. It's in your phone, your laptop, your cameras, your car, your cloud, and your breakfast cereal. SQLite's global triumph is a gratifying testament to the virtues of technical excellence and the philosophy of "less is more."

Readers of this blog will know that I admire the set-theoretic basis of SQL while despising the IT administrative state that has grown up, like septic tank scum, around it. You'll know what I'm talking about if you have ever:

Requested access to a particular corporate database.
Waited for DBAs to sign off on a query.
Asked for a new index.
Had the gall to install "unauthorized" software tools in
"production."

There is so much administrative shit covering big Oracle, SQLServer, or DB2 systems that it's almost impossible to get anything done without constantly fellating administrative dick!

SQLite saves SQL

The creators of SQLite had another idea. Let's drop the administrative crap and provide a simple stand-alone implementation of SQL. Let's also structure our system as a single highly portable C source file that can be compiled anywhere. Furthermore, let's test the shit out of our code with comprehensive test suites that hit every line. Finally, let's fix
our damn bugs. The SQLite developers did all this so well that SQLite slowly, then all at once, conquered the whole damn world. The SQLiters earned their dominance.

So, what does this have to do with J?

J provides a useful SQLite addon that lets J programmers navigate the vast cosmos of SQLite database files. I mainly use J's SQLite addon to access ThumbsPlus and Darktable image databases, but it's surprisingly handy in many contexts simply because SQLite database files are everywhere. SQLite database files are so pervasive that they've become a de facto binary data exchange format. If you have data in dire need of distribution, stuff them in a SQLite database file.

It's still SQL

SQLite is a superb tool, but it's still SQL, and SQL has an ancient deficiency. Loading data into SQL systems has always been a pain. So much so that a plethora of grisly, kludge-infested hacks, charitably labeled ETL tools (Extract Transform and Load), have taken root in the SQLverse. ETL tools, no matter how polished or optimized, all seem like afterthoughts.

"Hey, this set-theoretic SQL shit
would be so much better if there was
some data in our database!"

If you "manage" SQL systems, you must slay ETL dragons. SQLite hasn't fixed this, but its almost universal adoption means you can slay ETL dragons with your favorite programming spears. One of my favorite spearsis the J programming language.

Toy or tool

When I started writing this post, I considered composing a short
tutorial on how to create, load, and query a simple SQLite database with the J addon. Then I realized the Internet drips with simple SQLite toytutorials. Do we really need another one? Simple SQL tutorials help when getting started, but they invariably gloss over the ETL problem. The ETL problem never goes away and it's always more complex than expected. A realistic introduction to using SQLite, or any SQL system, would keep this nasty fact front and center. So, in a following post, I will outline a small but nontrivial SQLite-based tool I use to manage messy data I care about.

Stupid J Jupyter Tricks

John Baker — Mon, 25 Jul 2022 17:48:00 +0000

Cross posted from my blog Analyze the Data not the Drivel

Continuing with my software irritant dispatching theme: today's
irritant - including syntax colored code in blogs, LaTeX, Word, and other documents. This one is a persistent pain in the ass; especially for users of idiosyncratic programming languages. We all have our favorite hacks and workarounds; today I am sharing one of mine: using jupyter to syntax color J code.

A few years ago Martin Saurer made an outstanding contribution to the J programming language community. He created a jupyter kernel for J. If you don't know what jupyter is please crawl out from under your rock and join the rest of us in the 21^st century.

As I have said before jupyter notebooks are simply the correct way to present complex "executable" technical documents that contain heavy doses of mathematics, program code, and graphics. I'm not the only one that holds this opinion: many concur. It would be an understatement to say that jupyter has been a success. It's now a dominant tool in many disciplines. If you're not using it you are missing out on one of the most useful open-source programming tools available.

I came to jupyter from the pythonverse. The JSON based ipynb
notebook format originated there and initially stood for Interactive PYthon Note*Book, but in one of the best software design decisions ever made, the creators of jupyter decided to make it *programming language agnostic. This means any programming language can plug into jupyter if it implements a well-defined kernel interface. This is what Martin implemented. Now, many programming languages support jupyter. It's become an expected facility, and if it's missing, well that's a good reason to use another programming language.

Martin's J kernel works very well and I use it all the time, but there are a few ways the J kernel, and jupyter kernels in general, could be improved.

Debugging notebook code

Debugging notebook code is not as polished as it could be. Kernels are typically "invisible" executables that respond to requests sent from the notebook server. Most kernels stay in the background and do not provide standard language-specific programming environments. Usually, this isn't much of a problem, but when debugging complex programs it would be handy to push a button and pop up a standard debugger. In J's case, a JQt or JHS session would appear containing all the code loaded or created by
the notebook. You could then debug and modify your code, then pop over to the notebook session to try it out.

Right now the J kernel does not "pop up" visible J sessions but you can achieve something similar by extracting J notebook code as a J script and executing it in another JQt or JHS session. jupyter makes this easy!

Stupid J Jupyter Trick #1 - export notebook code as a J script

jupyter can download notebooks in a variety of formats. One of the
formats provided by the J kernel is ijs or J script. Go to the
File menu, select Download as, then pick J (.ijs). jupyter will extract all the J code cells in the notebook in cell order and write a script file to wherever you want it. I usually save such scripts in the J temp directory jpath ~temp where they can be conveniently loaded and run.

When you run a downloaded J notebook script in another J session it essentially¹ recreates the notebook kernel state.

Inserting J code into notebooks

Extracting J code from notebooks for debugging is useful but oddly the inverse, inserting J code into notebooks, is even more useful. As previously noted, jupyter can export notebooks in many formats. In particular, it can generate HTML, LaTeX, Markdown, and PDF versions of notebooks. I've found the generated LaTeX, HTML, and Markdown formats extremely handy.

Stupid J Jupyter Trick #2 - J code notebook insertion

If you're looking for a quick and dirty way to syntax color J code do the following:

Create a blank jupyter notebook.
Open a J script in your favorite editor.
Cut and Paste the script into a jupyter code cell.
Download the notebook as HTML or PDF.

jupyter will nicely syntax color your code. It even does a decent job of breaking lines. The GitHub files brandxmp.ijs.pdf and brandxmp.ijs.html are examples of what you get right out of the box.

Stupid J Jupyter Trick #3 - smarter J code notebook insertion

The quick and dirty method is simple and handy but it's of limited use when preparing complex LaTeX documents. With LaTeX, you want more than just code. You want cross-references, web links, footnotes, mathematical expressions, graphics, indexes, appendices, and other literate programming goodies. It's much easier to achieve such ends if your J code is split over many cells. The following verb does just that.

ipynbfrjod=:3 : 0

NB.*ipynbfrjod v-- extract J  words from JOD  and insert in blank
NB. jupyter notebook.
NB.
NB. monad:  clIpynb =. ipynbfrjod blclNames
NB.
NB.   NB. examples use docs and utils
NB.   require 'general/jod'
NB.   od ;:'docs utils'
NB.
NB.   nbj=: ipynbfrjod ;:'sha1 sha1dir'
NB.   nbj write 'C:\Users\baker\jupyter_notebooks\test0.ipynb'
NB.
NB.   nbj=: ipynbfrjod }. grp 'ipynb'
NB.   nbj write 'C:\Users\baker\jupyter_notebooks\ipynb_onself.ipynb'

NB. require 'general/jod' !(*)=. disp
jc=. disp&.> y

NB. markdown sections with word name
sec=. dblquote (<MDSECTION,JWORDMARK) ,&.> y
sec=. (<NBJCELLBEGst) ,&.> sec ,&.> <NBJCELLBEGen

NB. j code to quoted list of python strings notebook format
nbj=. <;._2@(REVPYESCAPECHRS&changestr)@tlf&.> jc
nbj=. ;&.> '"' , L: 0 (<'\n",',LF) ,~ L: 0 nbj
nbj=. ,&'"'&.> '\n",'&beforelaststr&.> nbj
nbj=. sec ,&.> nbj ,&.> <NBJCELLEND
toJ NBHEADER , (LF ,~ ','&beforelaststr ;nbj) , NBTRAILER
)

After loading J words into cells all the jupyter export facilities are immediately available. Examples of jupyter outputs exploiting this hack are available on GitHub: see ipynb_onself.ipynb and ipynb_onself.pdf.

To use ipynbfrjod download ipynb.ijs and the corresponding document ipynb.pdf. All these files are on GitHub. Help yourself!

Postscript: If you already have a recent version of J installed you can directly install this script as an addon by typing the following command in a JQt or JHS session.

install 'github:bakerjd99/jackshacks'

NB. load script
load '~addons/jacks/ipynb.ijs'

Some care must be taken when developing J jupyter notebooks to guarantee exported scripts accurately recreate kernel states. ↩

Branding XMP Sidecar Files with J

John Baker — Tue, 19 Jul 2022 21:18:00 +0000

Cross posted from my blog Analyze the Data not the Drivel

During my long and not-so-storied IT career I covertly dispatched
"irritants" with the J programming language. An irritant is an annoying software problem that does not merit a full-blown project-style approach. The last thing you want when dealing with irritants is to drag in IT bureaucrats. You know of whom I speak: the niggling little project-managing creatures that always turn little problems into bigger problems. Do you remember "Agile" when it was actually Agile? Now that I am tentatively retired¹ I no longer deal with IT bureaucrats but irritants still abound and I still use J to dispatch them. In my next three posts, I will show J solutions to some irritants that may be annoying you.

Today's irritant comes from my photography hobby. I have been taking pictures since I was eight years old. To give you a sense of how anal-retentive I am, I still have film negatives from childhood and I'm nearly seventy! Since the year 2000, I have been editing and uploading digital images. You can see many of them on my SmugMug site. My editing process uses a veritable software zoo. As I work on an image, I flip from one program to another. Some programs respect and maintain image metadata and others do not. At the end of my pipeline, I rename the image file to something meaningful² even though renaming makes it difficult to go back to the original file later.

If you edit images, you know the job is never done. You can always
"improve" a picture or cast it in a new light. I resist the urge to revamp old renderings but sometimes, maybe years later, I will go back to the original to "fix" things. Unfortunately, by renaming files it's not always easy to find the original image.

This is a common DAM (Digital Asset Management) problem!

There are scores of image database programs, and you would think that image databases would generate stable image keys. Most of them do, but their keys are typically useful only in the context of the database. You need a key tied to the unique bit pattern of the original image file. You need a proper hash.

Once you recognize the need for proper hashes the question becomes, "Where do you store them?" You might think a file's EXIF or ITPC metadata would be the ideal location. I tried this approach. Using the superb exiftool, I inserted hashes in images only to watch various editors strip or mangle them later on. The XMP (e*Xtensible **Metadata **P*latform) sidecar³ file was created to address this and other irritants. The XMP sidecar file provides a safe home for image metadata with the added bonus that you never need to touch or alter the original image file. If you don't treat your original RAWs like sacred bytes there's something wrong with you.

Darktable, my current favorite RAW developer, creates and maintains XMPs when you add images to its library or apply non-destructive edits. Darktable's edits are stored in sidecar XMPs leaving the originals unmolested. When you export a developed image Darktable creates a copy of the original, applies all the edits stored in the sidecar file, and then inserts Dublin Core metadata elements in the copy's IPTC metadata. Most image processors leave Dublin Core elements alone so they make a nice home for hash hacks.

This following J verb modifies the title element of a sidecar XMP file. It inserts the original file name and the SHA256 hash of the associated image file. The title element is typically left intact by image editors. So, at the end of your edits, you retain the name of the original file and its stable unique hash.

titbrandxmp=:3 : 0

NB.*titbrandxmp v--  brand xmp  sidecar file  with file name  and
NB. hash of associated image.
NB.
NB. monad:  clXmp =. titbrandxmp blImageXmpFiles
NB.
NB.   xmp=. 'c:/pictures/2022/Idaho/07_jul/d7500/_DSC8496.NEF.xmp'
NB.   ps=. xmp ;~ (-#'.xmp') }. xmp
NB.   titbrandxmp ps
NB.
NB.   ds=. sidecars 'c:/pictures/2022/North Rim Monument Valley/06_jun/d7500'
NB.   xmps=. titbrandxmp&.> <"1 ds

xmp=. read xmp [ 'raw xmp'=. y

NB. single Dubin Core publisher and creator 
NB. elements must exist to safely brand
dcp=. '</dc:publisher>';'</dc:creator>'
if. -.1 1 -: +/"1 dcp E.&> <xmp do. xmp return. end.

NB. file name and sha256 brand
tit=. dlf ('/[~(fhash)~]/',shabrand raw) changestr XMPTITLEFRAG-.CR

NB. replace or insert title element
'idx cxmp'=. (tags 'dc:title') cutnestidx xmp
if. #idx do. ;(<tit) idx} cxmp
else.
  (pt ,~ pt beforestr xmp),LF,tit,pt afterstr xmp [ pt=. ;0{dcp
end.
)

For details on how to use titbrandxmp refer to the J script brandxmp.ijs and the associated document brandxmp.pdf. Both of these files are available on GitHub. Enjoy!

Postscript: If you already have a recent version of J installed you can directly install this script as an addon by typing the following command in a JQt or JHS session.

install 'github:bakerjd99/jackshacks'

NB. load script
load '~addons/jacks/brandxmp.ijs'

I am hoping that rampaging inflation (2022) is checked so I don't
have to come out of retirement again! ↩
Directories filled with hundreds of names like _DSC8007.NEF and
IMG_6666.jpg are useless without thumbnail software. ↩
Sidecar files are generated and maintained by many imaging
processing programs. The XMP format is an XML text file. It can be
easily parsed and changed. ↩

JETL - J Extract Transform and Load

John Baker — Thu, 12 Aug 2021 18:28:37 +0000

Cross posted from my blog Analyze the Data not the Drivel

I have been surreptitiously using the J programming language on the job for many years. With rare exceptions, I haven't advertised my clandestine use of J simply because it's strange and scary and nonstandard, and IT departments are filled with cowardly little bedwetters that run away like Brave Sir Robin when confronted with something really different! At first, I tried to convince the bedwetters that J is a fabulous tool: a spear in a world of bent spoons! But, if you've ever tried convincing an academic Marxist that, yes "communism has been tried", or a ufologist that funny lights in the sky are almost certainly not aliens, or a 9-11 troofer that the twin towers were absolutely not brought down by controlled demolition, then you have a pretty good idea what it's like getting IT departments to buy into unsanctioned technologies.

It's sad, but some of the most pig-headed and closed-minded people I have ever run into are fellow programmers and IT professionals. As a self-anointed elite, ITites (IT people), can be insufferably sure of themselves. Hubris is still very much a thing! As a general rule, always consider the possibility that you are full of it!

With that said, what's so great about J and why should anyone use it for database ETL (Extract Transform and Load) jobs?

ETL unavoidable grunt work and an IT Achilles heel

ETL jobs are never glamorous! You're not going to win any Turing Awards for converting dumpster-fire data into well-structured database tables. Nor will you garner accolades and "We are not worthies" from peers. Most ITites simply assume the ETL will be done while they dream of all the shiny things the polished data will enable. Precisely because ETL is dull, any moron can do it, grunt work, it's frequently discounted. I've watched many IT projects sink after being torpedoed by incomplete and crappy ETL. ETL is an IT Achilles heel; it can and has brought the mighty down.

Standard ETL jobs always consist of odd combinations of:

Gather data in all its unstructured messy glory.
Pick and choose the good bits.
Hack and standardize your selection into rows and tables.
Load the tables into mostly SQL databases.
Finish the job with SQL transformations.

Every single item on this list can drain your precious bodily fluids.

This is where J can help!

Slay the Annoyances and Sack the Irritants with J

It's not an exaggeration to say that ETL is a long tiresome sequence of annoyances and irritants. It's easy to see where you need to go, and it's just N, where N is an arbitrarily large number of picayune and irritating steps between you and the pure database of your dreams.

Anything you can do to quickly slay the annoyances and sack the irritants will move you closer to your dream. The last thing you want is to write a lot of code to dispatch trivialities, but sadly, this is often the fate of ETL programmers. Like Sisyphus, they laboriously push one damn rock up the hill only to watch another roll down and take its place. When it comes to moving rocks, you want a bulldozer, not a garden spade. J is a bulldozer!

You can install Windows, MacOS or Linux versions of J from jsoftware.com. Install J with all the standard addons.

Let's take the J bulldozer for a spin.

Pimping your J bulldozer with JOD

J scripts are simple ASCII text files with an .ijs file extension. Edit them with any standard editor. Two of J's standard development environments JQT and JHS have built-in editors that provide expected goodies like syntax highlighting. Many editor extensions provide J syntax highlighting. One of my favorites is the J Language Support Visual Studio Code extension. It can be found in GitHub here and installed from the standard Visual Studio Code Marketplace.

Despite the ubiquity of J oriented text editors and script handling tools I store all my J code, test cases, and documentation in a coding tool I developed called JOD. JOD is a refactoring tool. It lets you easily reuse J words in arbitrary contexts without error prone cut-and-pasting or rampant over-inclusion. I use JOD from three J environments, jconsole, JHS, and JQT. JOD is one of many J addons. It can be installed with the J pacman utility. JOD has been entirely programmed in J and is a good example of using J to build larger systems. JOD source code is on Github. See The JOD Page for more about JOD.

In the following examples I am going to assume JOD is installed and the utilities dictionary utils is available. The latest version of utils is on GitHub here.

load 'general/jod'
get 'portchars' [ od 'utils' [ 3 od ''
NB. use ASCII box characters - simplifies blog formatting
portchars 0

Start your JETL engine

Let's slay some annoyances.

Annoyance #1: For reasons best not discussed in civil society, many websites, point-of-sale systems, spreadsheets, and so forth encourage users to compose not-so-great novels in text entry fields. When these "novels" land in data feeds you see crap like this:

textfield =: 0 : 0
  Leading and trailing
       white     space, tabs,
 line    feeds,
and bytes @*!!
      of crap.
)

It's easy to fix crap once its loaded into a SQL table but it's crap like this that get's in the way of loading the table in the first place. The following J expression cleans textfield.

(] #~ [: -. '  '&E.) textfield -. CRLF,TAB,'@#*!'

 Leading and trailing white space, tabs, line feeds, and bytes of crap.

The utils dictionary contains many character list verbs.

get ;:'reb allwhitetrim'
allwhitetrim reb textfield -. CRLF,TAB,'@#*!'

Leading and trailing white space, tabs, line feeds, and bytes of crap.

Annoyance #2: Here's another way too common Excel enabled irritant.

quotecomma=: 0 : 0
"how often",1,2,"have quoted , commas","screwed ,,, line parsing?"
)

getrx 'parsecsv'  NB. load parsecsv and words it calls
',' parsecsv quotecomma

+-----------+-+-+----------------------+---------------------------+
|"how often"|1|2|"have quoted , commas"|"screwed ,,, line parsing?"|
+-----------+-+-+----------------------+---------------------------+

For example this 63MB csv file contains dreaded commas in quotes.

smoutput dir '\\jfsdev04\Shares\SwiftIQPreprocess\SwiftZipCsv\Item*.csv'
smoutput fi=: ;1 dir '\\jfsdev04\Shares\SwiftIQPreprocess\SwiftZipCsv\Item*.csv'

ItemSales-0810202163523.csv    63958716 10-Aug-21 18:37:42
//jfsdev04/Shares/SwiftIQPreprocess/SwiftZipCsv/ItemSales-0810202163523.csv

A simple J verb can flip the offenders to semi-colons.

getrx ;:'repdqchars read'
#txt=: ',;' repdqchars read fi NB. flip commas - return byte count

63958716

Here's the definition of repdqchars.

repdqchars=:4 : 0

NB.*repdqchars v-- replace double quoted (0{x) characters with (1{x).
NB.
NB. dyad:  cl =. clPair repdqchars cl
NB.
NB.   s=. '"go ahead, replace","quoted commas,,,,"'
NB.   ',;' repdqchars s
NB.   s -: ',,' repdqchars s

if. 2 <: #y do.
  msg=. 'unbalanced quotes'
  msg assert 0 = 2 | +/b=. '"'=y

  NB. mask of quoted characters
  q=. +/\ _1 (1 { |: _2 ]\ I. b)} b
  msg assert 0 1 -: (<./ , >./) q

  NB. replace quoted (0{x) with (1{x)
  (1{x) (I. q #^:_1 (0{x) = q#y) } y
else.
  y
end.
)

Much of repdqchars consists of comments and assertions. Not having to write a lot of code is a big plus when slaying annoyances and sacking irritants.

Annoyance #3: When defining database tables, it helps to understand how data is distributed --- duh! Before creating tables, it's helpful to run quick-and-dirty analyses. One useful summary is column cardinality, the number of unique column items.

NB. read and parse TAB delimited text - first row column names
get 'readtd2'
f=: '\\jfsdev04\Shares\DailyXMLPreprocess\DailyTsv\raw_RetailItemGroup_RMI.txt'
d=: readtd2 f
NB. comput column cardinalties
smoutput 'row count: ',":#d
(>0{d) ;~ ,. #@~.&> <"1 |: }. d

row count: 68604
+-----+------------------------+
| 2975|retailItemGrpID         |
| 2784|retailItemGrpName       |
|  316|retailItemGrpDescription|
|  283|orgUnitOwnerID          |
|22260|rmiID                   |
|21930|rmiName                 |
|  255|rmiExtID                |
|    1|ZipFileName             |
|    1|ZipRunNumber            |
|    1|XMLFileName             |
|    1|XMLFileDate             |
|    1|CreateDatetime          |
+-----+------------------------+

J is hardly unique in its ability to subdue ETL irritants. I've personally used Python, Powershell, and SQL scripts as well but for me J administers the biggest bang per byte! Adding J to your coding arsenal will make you a more versatile programmer and a better person. Do it!

The Joys of JOD and other code databases

One of the many advantages of storing code in tools like JOD is that it's easy to run ad hoc code analysis.

load 'general/jod'
od ;:'jacksons utils' [ 3 od '' NB. open JOD dictionaries

+-+-----------------+--------+-----+
|1|opened (rw/ro) ->|jacksons|utils|
+-+-----------------+--------+-----+

Here's some basic information about the JOD group swiftprep. J scripts are easily generated from groups with JOD's mls, (make load script) verb.

smoutput (":#}. grp 'swiftprep'),': words in script'
smoutput (":#;{:2 mls 'swiftprep'),': size of commented script'
smoutput (":#;{:compj }.2 grp 'swiftprep'),': size of minimized script'
NB. a single static "noun" of tables and column names
tcl=: (}.2 grp 'swiftprep') -. <'SwiftLandTableHeaders'
smoutput (":#;{:compj tcl),': minimized script without largest noun'
smoutput (":(+/ % #);1{0 15 get }. grp 'swiftprep'),': mean bytes per word'
smoutput (":#;: ;1{2 mls 'swiftprep'),': total commented J token count'

106: words in script
42221: size of commented script
23403: size of minimized script
16492: minimized script without largest noun
529.91509: mean bytes per word
5945: total commented J token count

As you can see there isn't a lot of code, roughly 17K.

As nice as off-the-cuff code statistics are, the biggest advantage of code databases is how they simplify code recycling and document
generation. The ETL swiftprep group currently contains 106 J words. 69 of those words are specific to swiftprep. The rest are common utilities and recycled words. When swiftprep.ijs is generated with JOD's mls verb all of swiftprep\'s words are collected into a single stand-alone script. For such stand-alone scripts, I include an interface noun that lists group words you should be aware of.

get 'IFACEWORDSswiftprep'
hlpnl IFACEWORDSswiftprep

+-------------------+-------------------------------------------------------+
|dailyswift         |daily SwiftIQ zip loading and maintenance tasks        |
|fullheaderscan     |scan SwiftIQ zips and build complete table headers     |
|loadswiftcsvs      |copies zip to working directory and extracts csvs      |
|loadswifttabs      |convert SwiftIQ csvs to TAB delimited table files      |
|procqueuezips      |processes queued zips from file                        |
|procswiftzip       |process a SwiftIQ zip                                  |
|queueswiftSizes    |file sizes: queueswiftSizes SwiftAuxDir,'swift2.txt'   |
|queueswiftZips     |write (y) line file of queued zips                     |
|runswiftssis       |runs SwiftIQ SSIS batch scripts                        |
|showswiftUnload    |list never loaded zips in load order                   |
|showswiftlog       |show SwiftIQ log entries                               |
|swiftLoaded        |loaded swift zips with return code: swiftLoaded '<1000'|
|swiftMissingColumns|list missing SwiftIQ landing table columns             |
|swiftZips          |sorted SwiftIQ zip files                               |
+-------------------+-------------------------------------------------------+

Finally, code databases typically contain far more than code! Documentation is also stored. Stored documentation can be used to generate literate programming documents. In JOD's case jodliterate applied to swiftprep creates this indexed and annoted PDF. Enjoy!

Let J tell SSIS where to get off

I hope I've convinced you that J can help with ETL irritants. It can help, but it cannot do the entire job. At some point you will have to bring in tools like bcp, SSIS or Talend. SSIS and Talend are typically the drivers of ETL processes; they call other specialized tools as needed, but I've found that it's easier to use scripting tools like J as drivers. In this blog post I outline how easy it is to control SSIS with Python. You can use the same trick with J.

The swiftprep ETL script does:

Finds a large daily FTP'ed zip file containing CSVs.
Unzips the CSVs to working directories.
Converts unruly CSVs to standard TAB delimited text files.
Runs an SSIS package to load TAB files to SQLServer staging tables.
Runs another SSIS package to transform staging tables to a reporting tables.

This reads worse than it is. The main jumping off word in swiftprep is dailyswift. Here's dailyswift:

dailyswift=:3 : 0

NB.*dailyswift v-- daily SwiftIQ zip loading and maintenance tasks.
NB.
NB. monad:  (ilRc ; clGuid) =. dailyswift uuIgnore
NB. dyad:  (ilRc ; clGuid) =. iaDayCode dailyswift uuIgnore
NB.
NB.   _1 dailyswift 0  NB. update stage only
NB.   dailyswift~ _1
NB.
NB.   NB. full table reload
NB.   42 dailyswift 0

(weekday 3 {. 6!:0 '') dailyswift y
:
NB. stage latest zip
'rc tg'=. procswiftzip ;0{swiftZips 0
if. 0~:0{rc do. rc;tg return. end.

if. _1=x do.
  rc=. SwiftLog 'only updating staging'
else.
  rcnts=. cntswiftrows 0  NB. report/stage item counts

  NB. update reporting
  rc=. logswiftReportBegin tg;rcnts
  if. 0=x do.
    SwiftLog 'rowcnts::pidx: ',rcnts
    SwiftLog 'reindexing - partial table update'
    rc=. runswiftssis 'swiftreindex.bat'
    rc=. runswiftssis 'swiftreport.bat'

  elseif. 42=x do.
    NB. full updates are best babysat - trigger with
    NB. code 42 - life the universe and everything
    SwiftLog 'rowcnts::full: ',rcnts
    SwiftLog 'full table refresh'
    rc=. runswiftssis 'swiftreportfull.bat'

  elseif.do.
    SwiftLog 'rowcnts:: ',rcnts
    SwiftLog 'partial table update'
    rc=. runswiftssis 'swiftreport.bat'
  end.
  rc=. logswiftReportEnd rc;tg
end.
)

Maybe I'm an old jaded programmer, but dailyswift doesn't look very scary. Obfuscated J can be phenomenally impenetrable. Look at the definition of readtd2. Sadly, it's easy to create unreadable code in any programming language. If program code was edited like novel manuscripts, we'd seldom see incomprehensible crap but it isn't, so we must exercise personal discretion. My goal when coding is to make things so clear that that they're dismissed as trivial! And, let's face it, ETL, as tedious and time-consuming as it typically is, is trivial!

Running SSIS Packages with Python

John Baker — Thu, 08 Jul 2021 14:54:22 +0000

Cross posted from my blog Analyze the Data not the Drivel

Microsoft's SSIS (SQL Server Integration Services)
is a ubiquitous ETL (Extract Transform and Load) tool. Despite its
widespread use, SSIS is not loved! At best, it's tolerated for its
undeniable utility, but SSIS's "utility" comes with a host of hideous
warts with the ugliest being its file handling.

You would think that an ETL tool would excel at file manipulation; how
else would you "extract" data? But, for reasons lost to posterity, SSIS
totally screws the file handling pooch. Now there are "sanctioned" ways
to unscrew the pooch. Use "script tasks" is a common retort. With script
tasks, you code your file handling bits in something like C# and then
use SSIS to compile and incorporate the C# parts into the larger ETL
process. Script tasks work but they're a pain to debug and maintain.
There are other pooch unscrewers. Many code everything in SQL stored procedures, others build GAC dlls, some use command tasks
to shell out to file utilities. Sadly, the abundance of SSIS file
handling work-a-arounds just affirms that SSIS blows hard here!

I could bitch about SSIS file handling for fortnights but, bitching does not solve problems. Luckily, there is an easy way to use SSIS's good bits without getting sucked into the script task swamp.

SSIS typically calls helper processes like script tasks, but if you
flip things around and call SSIS from helper processes you can easily
leverage the strengths of both SSIS and helpers.

The following Python snippet, taken from a recent project, illustrates
this technique.

all_cube_zips = etl.daily_zip_files(working_dirs=rtp.working_dirs, 
                                    runtime_parms=rtp.runtime_parms)

for cnt, zipfile in enumerate(all_cube_zips):

   etl.clear_working_dirs(working_dirs=rtp.working_dirs, 
                       runtime_parms=rtp.runtime_parms, 
                       name_prefix="DailyRetails")

   dailyretail_xml_files = etl.unzip_prefix_xml(name_prefix="DailyRetails", 
                       daily_zip_file=zipfile, 
                       working_dirs=rtp.working_dirs,
                       runtime_parms=rtp.runtime_parms)

   tsv_files = dcx.write_dailyretail_tsvs(xml_files=dailyretail_xml_files, 
                       daily_zip_file=os.path.basename(zipfile), 
                       column_defaults=rcd.DailyRetails_column_defaults, 
                       working_dirs=rtp.working_dirs,
                       runtime_parms=rtp.runtime_parms,
                       append_only=False)

   os.system(r"\\Shares\DailyCode\bats\SelectReload.bat")

This Python program unzips hundreds of XML files to a working directory and then "flattens" them to a simple TAB delimited text file. This is typical ETL stuff. After writing the TAB delimited file Python runs a simple batch script to execute an SSIS package. The batch script is called with:

 os.system(r"\\Shares\DailyCode\bats\SelectReload.bat")

The following script lines from SelectReload.bat show how to set up and run a package with dtexec.exe.

rem change to your dtexec directory
cd "c:\Program Files\Microsoft SQL Server\150\DTS\Binn"
rem execute an SSIS package - use fully qualified paths
dtexec /Project "\\Shares\DailyCode\ispacs\flat.ispac" /Package Reloads.dtsx
if %ERRORLEVEL% NEQ 0 goto Error18

Here, all the irritating file hacking is done with Python leaving
streamlined ETL jobs for SSIS. Both processes are simplified. Both are easy to test and debug. With software, it's always wise to try simple first.

As a final warning, before you adopt this approach be aware that you need full "Administrative Rights" to run dtexec.com like this. Many anally retentive IT outfits will simply outlaw such renegade scripting. If you're incarcerated in Administrative Rights jail I'd suggest taking advantage of the current labor shortage and find another job. Squabbling over administrative rights is a demeaning waste of IT talent that can no longer be tolerated.

NumPy another Iverson Ghost

John Baker — Tue, 14 Jul 2020 19:10:24 +0000

Cross posted from my blog Analyze the Data not the Drivel

During my recent SmugMug API and Python adventures I was haunted
by an Iverson ghost: NumPy

An Iverson ghost is an embedding of APL like array programming
features in nonAPL languages and tools.

You would be surprised at how often Iverson ghosts appear. Whenever
programmers are challenged with processing large numeric arrays they rediscover bits of APL. Often they’re unaware of the rich heritage of array processing languages but in NumPy's case, they indirectly acknowledged the debt. In Numerical Python the authors wrote:

The languages which were used to guide the development of NumPy
include the infamous APL family of languages, Basis, MATLAB, FORTRAN, S and S+, and others.

I consider “infamous” an upgrade from “a mistake carried through to perfection.”

Not only do developers frequently conjure up Iverson ghosts they also invariably turn into little apostles of array programming that won’t shut up about how cutting down on all those goddamn loops clarifies and simplifies algorithms. How learning to think about operating on entire arrays, versus one dinky number at a time, frees the mind. Why it’s almost as if array programming is a tool of thought.

Where have I heard this before?

Ahh, I’ve got it, when I first encountered APL almost fifty years ago.

Yes, I am an old programmer, a fossil, a living relic. My brain is a putrid pool of punky programming languages. Python is just the latest in a longish line of languages. Some people collect stamps. I collect programming languages. And, just like stamp collectors have favorite stamps, I find some programming languages more attractive than others. For example, I recognize the undeniable utility of C/C++, for many tasks they are the only serious options, yet as useful and pervasive as C/C++ are they have never tickled my fancy. The notation is ugly! Yeah, I said it; suck on it C people. Similarly, the world’s most commonly used programming language JavaScript is equally ugly. Again, JavaScript is so damn useful that programmers put up with its many warts. Some have even made a few bucks writing books about its meager good parts.

I have similar inflammatory opinions about other widely used languages. The one that is making me miserable now is SQL, particularly Microsoft’s variant T-SQL. On purely aesthetic grounds I find well-formed SQL queries less appalling than your average C pointer fest. Core SQL is fairly elegant but the macro programming features that have grown up around it are depraved. I feel dirty when forced to use them which is just about every other day.

At the end of my programming day, I want to look on something that is beautiful. I don’t particularly care about how useful a chunk of code is or how much money it might make, or what silly little business problem it solves. If the damn code is ugly I don’t want to see it.

People keep rediscovering array programming, best described in Ken
Iverson’s 1962 book A Programming Language, for two basic
reasons:

It’s an efficient way to handle an important class of problems.
It’s a step away from the ugly and back towards the beautiful.

Both of these reasons manifest in NumPy’s resounding success in the Python world.

As usual, efficiency led the way. The authors of Numerical Python
note:

Why are these extensions needed? The core reason is a very prosaic
one, and that is that manipulating a set of a million numbers in
Python with the standard data structures such as lists, tuples or
classes is much too slow and uses too much space.

Faced with a “does not compute” situation you can either try something else or fix what you have. The Python people fixed Python with NumPy.
Pythonistas reluctantly embraced NumPy but quickly went apostolic! Now books like Elegant SciPy and the entire SciPy toolset that been built on NumPy take it for granted.

Is there anything in NumPy for programmers that have been drinking the array processing cool aid for decades? The answer is yes! J programmers, in particular, are in for a treat with the new Python3 addon that’s been released with the latest J 8.07 beta. This addon directly supports NumPy arrays making it easy to swap data in and out of the J/Python environments. It’s one of those best of both worlds things.

The following NumPy examples are from the SciPy.org NumPy quick start tutorial. For each NumPy statement, I have provided a J equivalent. J is a descendant of APL. It was largely designed by the same man: Ken Iverson. A scumbag lawyer or greedy patent troll might consider suing NumPy’s creators after looking at these examples. APL’s influence is obvious. Fortunately, Ken Iverson was more interested in promoting good ideas than profiting from them. I suspect he would be flattered that APL has mutated and colonized strange new worlds and I think even zealous Pythonistas will agree that Python is a delightfully strange world.

Some Numpy and J examples

Selected Examples from https://numpy.org/doc/1.19/user/quickstart.html Output has been suppressed here. For a more detailed look at these examples browse the Jupyter notebook: NumPy and J Make Sweet Array Love.

Creating simple arrays

# numpy
a = np.arange(15).reshape(3, 5)

NB. J 
a =. 3 5 $ i. 15

# numpy 
a = np.array([2,3,4])

NB. J 
a =. 2 3 4

# numpy 
b = np.array([(1.5,2,3), (4,5,6)])

NB. J 
b =. 1.5 2 3 ,: 4 5 6

# numpy 
c = np.array( [ [1,2], [3,4] ], dtype=complex )

NB. J 
0 j.~ 1 2 ,: 3 4

# numpy - make complex numbers with nonzero real and imaginary parts
c + (0+4.7j)

NB. J - also for J
c + 0j4.7

# numpy 
np.zeros( (3,4) )

NB. J 
3 4 $ 0

# numpy - allocates array with whatever is in memory 
np.empty( (2,3) )

NB. J - uses fill - safer but slower than numpy's trust memory method 
2 3 $ 0.0001

Basic operations

# numpy 
a = np.array( [20,30,40,50] ) 
b = np.arange( 4 ) 
c = a - b

NB. J 
a =. 20 30 40 50 
b =. i. 4 
c =. a - b

# numpy - uses previously defined (b) 
b ** 2

NB. J 
b ^ 2

# numpy - uses previously defined (a) 
10 * np.sin(a)

NB. J 
10 * 1 o. a

# numpy - booleans are True and False 
a < 35

NB. J - booleans are 1 and 0 
a < 35

Array processing

# numpy 
a = np.array( [[1,1], [0,1]] ) 
b = np.array( [[2,0], [3,4]] ) 
# elementwise product 
a * b

NB. J 
a =. 1 1 ,: 0 1 
b =. 2 0 ,: 3 4 
a * b

# numpy - matrix product 
np.dot(a, b)

NB. J - matrix product 
a +/ . * b

# numpy - uniform pseudo random 
a = np.random.random( (2,3) )

NB. J - uniform pseudo random 
a =. ? 2 3 $ 0

# numpy - sum all array elements - implicit ravel 
a.sum(a)

NB. J - sum all array elements - explicit ravel 
+/ , a

# numpy 
b = np.arange(12).reshape(3,4) 
# sum of each column 
b.sum(axis=0) 
# min of each row 
b.min(axis=1) 
# cumulative sum along each row 
b.cumsum(axis=1) 
# transpose 
b.T

NB. J  
b =. 3 4 $ i. 12 
NB. sum of each column 
+/ b 
NB. min of each row 
<./"1 b 
NB. cumulative sum along each row 
+/\"0 1 b 
NB. transpose 
|: b

Indexing and slicing

# numpy  
a = np.arange(10) ** 3  
a[2] 
a[2:5] 
a[ : :-1]   # reversal

NB. J 
a =. (i. 10) ^ 3 
2 { a 
(2 + i. 3) { a
|. a

Extracting SQL code from SSIS dtsx packages with Python lxml

John Baker — Tue, 14 Jul 2020 15:59:49 +0000

Cross posted from my blog Analyze the Data not the Drivel

Lately, I’ve been refactoring a sprawling SSIS (SQL Server Integration Services) package that ineffectually wrestles with large XML files. In this programmer’s opinion using SSIS for heavy-duty XML parsing is geeky self-abuse so I’ve opted to replace an eye-ball straining¹ SSIS package with half a dozen, “as simple as possible but no simpler”, Python scripts. If the Python is fast enough for production great! If not the scripts will serve as a clear model² for something faster.

I’m only refactoring³ part of a larger ETL process so whatever I do it must mesh with the rest of the mess.

So where is the rest of the SSIS mess?

SSIS’s visual editor does a wonderful job of hiding the damn code!

This is a problem!

If only there was a simple way to troll through large sprawling SSIS spider-webby packages and extract the good bits. Fortunately, Python’s XML parsing tools can be easily applied to SSIS dtsx files. SSIS dtsx files are XML files. The following code snippets illustrate how to hack these files.

First import the required Python modules. lxml is not always included in Python distributions. Use the pip or conda tools to install this module.

# imports
import os
from lxml import etree

Set an output directory. I’m running on a Windows machine. If you’re on a Mac or Linux machine adjust the path.

# set sql output directory
sql_out = r"C:\temp\dtsxsql"
if not os.path.isdir(sql_out):
    os.makedirs(sql_out)

Point to the dtsx package you want to extract code from.

# dtsx files
dtsx_path = r'C:\Users\john\AnacondaProjects\testfolder\bixml'
ssis_dtsx = dtsx_path + r'\ParseXML.dtsx'

Read and parse the SSIS package.

tree = etree.parse(ssis_dtsx)
root = tree.getroot()

lxml renders XML namespace tags like <DTS:Executable as
www.microsoft.com/SqlServer/Dts\}Executable. The following gathers all the transformed element tags in the dtsx package.

# collect unique element tags in dtsx
ele_set = set()
for ele in root.xpath(".//*"):
    ele_set.add(ele.tag)    
print(ele_set)
print(len(ele_set))

Using transformed element tags of interest blast over the dtsx and suck out the bits of interest.

# extract sql code in source statements and write to *.sql files 
total_bytes = 0
package_name = root.attrib['{www.microsoft.com/SqlServer/Dts}ObjectName'].replace(" ","")
for cnt, ele in enumerate(root.xpath(".//*")):
  if ele.tag == "{www.microsoft.com/SqlServer/Dts}Executable":
    attr = ele.attrib
    for child0 in ele:
      if child0.tag == "{www.microsoft.com/SqlServer/Dts}ObjectData":
        for child1 in child0:
          sql_comment = attr["{www.microsoft.com/SqlServer/Dts}ObjectName"].strip()
          if child1.tag == "{www.microsoft.com/sqlserver/dts/tasks/sqltask}SqlTaskData":
            dtsx_sql = child1.attrib["{www.microsoft.com/sqlserver/dts/tasks/sqltask}SqlStatementSource"]
            dtsx_sql = "-- " + sql_comment + "\n" + dtsx_sql
            sql_file = sql_out + "\\" + package_name + str(cnt) + ".sql"
            total_bytes += len(dtsx_sql)
            print((len(dtsx_sql), sql_comment, sql_file))
            with open(sql_file, "w") as file:
              file.write(dtsx_sql)
print(('total sql code bytes',total_bytes))

The code snippets in this post are available in this Jupyter notebook: Extracting SQL code from SSIS dtsx packages with Python lxml. Download and tweak for your dtsx nightmare!

I frequently run into SSIS packages that cannot be viewed on 4K
monitors when fully zoomed out. ↩
Python’s readability is a major asset when disentangling
mess-ware. ↩
Yes, I’ve railed about the word “refactoring” in the past but I’ve moved on and so should you. “A foolish consistency is the hobgoblin of little minds.” ↩