etl | R package to facilitate ETL operations | Data Migration library

my lambda function triggers glue job by boto3 glue.start_job_run

and here is my glue job script

I have a requirement to build a SSIS package that sends HTML formatted emails and then saves the emails as tiff files. I have created a script task that processes the necessary records and then coverts the HTML code to the tiff. I have split the process into separate packages, the email send works fine the converting HTML to tiff is causing the issue.

When running the package manually it will process all files without any issues. my test currently is about 315 files this needs to be able to process at least 1,000 when finished with the ability to send up to 10,000 at one time. The problem is when I set the package to execute using SQL Server Agent it stops at 207 files. The package is deployed to SQL Server 2019 in the SSIS Catalog

What I have tried so far

I started with the script being placed in a SSIS package and deployed to the server and calling the package from a step (works 99.999999% of the time with all packages) tried both 32 and 64 bit runtime. Never any error messages just Unexpected Termination when looking at the execution reports. When clicking in the catalog and executing package it will process all the files. The SQL Server Agent is using a proxy and I also created another proxy account with my admin credentials to test for any issues with the account.

Created another package to call the package and used the Execute Package Task to call the first package, same result 207 files. Changed the execute Process task to an Execute SQL Task and tried the script that is created to manually start a package in the catalog 207 files. Tried executing the script from the command line both through the other SSIS package and the SQL Server Agent directly same results 207 files. If I try any of those methods directly outside SQL Server Agent the process runs no issues.

I converted the script task to a console application and it works processing all the files. When calling the executable file from any method from the SQL Server Agent it once again stops at the 207 files.

I have consulted with the companies DBA and Systems teams and they have not found anything that could be causing this error. There seems to be some type of limit that no matter the method of execution SQL Server Agent will not allow. I have mentioned looking at third-party applications but have been told no.

I have included the code below that I have been able to piece together. I am a SQL developer so C# is outside my knowledge base. Is there a way to optimize the code so it only uses one thread or does a cleanup between each letter. There may be a need for this to create over ten thousand letters at certain times.

Update

I have replaced the code with the new updated code. The email and image creation are all included as this is what the final product must do. When sending the emails there is a primary and secondary email address and depending on what email address is used it will change what the body of the email contains. When looking at the code there is a section of try catch that sends to primary when indicated to and if that fails it send to secondary instead. I am guessing there is a much cleaner way of doing that section but this is my first program as I work in SQL for everything else.

Thank You for all the suggestions and help.

Updated Code

(My first StackFlow question)

My goal is to improve the ETL process for identifying which NetApp file shares are related to which AD permission distribution groups. Currently an application named 'TreeSize' scans a number of volumes and outputs a number of large .CSV files (35mb+). I want to merge this data and remove all permission information where each group (or named user) doesn't start with a capital G or D ('^[GD]'). With over 700,000 rows to process, it's currently taking me over 24hr to run. I hope there is a better way to process this data more efficiently to drastically cut that time down.

Here is test data which resembles actual data once all files have been merged. Use rownum to adjust size of data. (Real data 700000+)

Test Data

We have recently been parachuted to a new ETL project with very bad code. I have in my hands a query with 700 rows and all sort of update.

I would like to debug it with SET XACT_ABORT ON; and the goal is to rollback everything if only one transaction fails.

But I find several way to archive it on StackOverflow like this:

I am working on Glue in AWS and trying to test and debug in local dev. I follow the instruction here https://aws.amazon.com/blogs/big-data/developing-aws-glue-etl-jobs-locally-using-a-container/ to develop Glue job locally. On that post, they use Glue 1.0 image for testing and it works as it should be. However when I load and try to dev by Glue 3.0 version; I follow the guidance steps but, I can't open Jupyter notebook on :8888 like the post said even every step seems correct.

here my cmd to start a Jupyter notebook on Glue 3.0 container

I am working on a large java web-based project that is based on some older versions of Spring and Hibernate, and uses log4j 1.2x. Due to recent vulnerabilities found in log4j2, we have been directed to upgrade to the latest version of log4j2. I am trying to implement the log4j2 log4j1 bridge, so that I don't have to update all our logging code in the application. It is all working fine, except I cannot specify where to store the log files, because the log4j1 bridge appears to not support system properties. I am passing in a ${catalina.base} property when starting my tomcat server, but the log4j1 bridge uses the literal text rather than substituting the propery value.

My maven pom.xml

I'm trying to get a message that file not found when it doesn't match the file_name*.txt pattern in specific directory.

When calling the script with file_name*.txt argument, all works fine. While entering invalid name file_*.txt throws:

On the ETL server I have a DW user table.

On the prod OLTP server I have the sales database. I want to pull the sales only for users that are present in the user table on the ETL server.

Presently I am using an execute SQL task to fetch the DW users into a SSIS System.Object variable. Then using a for each loop to loop through each item (userid) in this variable and via a data flow task fetch the OLTP sales table for each user and dump it into the DW staging table. The for each is taking long time to run.

I want to be able to do an inner join so that the response is quicker, but I cant do this since they are on separate servers. Neither can I use a global temp table to make the inner join, for the same reason.

I tried to collect the DW users into a comma separated string variable and then using it (via string_split) to query into OLTP, but this is also taking more time at the pre-execute phase (not sure why exactly) even for small number of users.

I also am aware of lookup transform but that too will result in all oltp rows to be brought into the dw etl server to test the lookup condition.

Is there any alternate approach to be able to do an inner join by taking the list of users into the source?

Note: I do not have write permissions on the OLTP db.

My colleagues and I routinely create ad hoc scripts in R, to perform ETL on proprietary data and generate automated reports for clients. I am attempting to standardize our approach, for the sake of consistency, modularity, and reusability.

In particular, I want to consolidate our most commonly used functions in a central directory, and to access them as if they were functions from a proprietary R package. However, I am quite raw as an R developer, and my teammates are even less experienced in R development. As such, the development of a formal package is unfeasible for the moment.

Fortunately, the box package, by Stack Overflow's very own Konrad Rudolph, provides (among other modularity) an accessible approach to approximate the behavior of an R package. Unlike the rigorous development process outlined by the RStudio team, box requires only that one create a regular .R file, in a meaningful location, with roxygen2 documentation (#') and explicit @exports:

Writing modules

The module bio/seq, which we have used in the previous section, is implemented in the file bio/seq.r. The file seq.r is, by and large, a normal R source file, which happens to live in a directory named bio.

In fact, there are only three things worth mentioning:

1. Documentation. Functions in the module file can be documented using ‘roxygen2’ syntax. It works the same as for packages. The ‘box’ package parses the
   documentation and makes it available via box::help. Displaying module help requires that ‘roxygen2’ is installed.

2. Export declarations. Similar to packages, modules explicitly need to declare which names they export; they do this using the annotation comment #' @export in front of the name. Again, this works similarly to ‘roxygen2’ (but does not require having that package installed).

⋮

At the moment, I am tinkering around with a particular module, as "imported" into a script. While the "import" itself works seamlessly, I cannot seem to access the documentation for my functions.

I am experimenting with box on a Lenovo ThinkPad running Windows 10 Enterprise. I have created a script, aptly titled Script.R, whose location serves as my working directory. My module exists in the relative subdirectory ./Resources/Modules as the humble file time.R, reproduced here: