Tag: ETL | IT Nota

ESP Work Automation: Empowering Enterprises with Streamlined Workflows and Operational Efficiency

In the ever-evolving world of business, organizations are constantly seeking ways to optimize their processes, improve productivity, and enhance operational efficiency. One solution that has gained significant prominence is ESP Work Automation. ESP Work Automation is a comprehensive software suite designed to streamline and automate diverse tasks and workflows within an organization. By reducing manual intervention, minimizing errors, and maximizing productivity, ESP Work Automation enables businesses to focus on core activities, drive innovation, and achieve higher levels of efficiency. This article provides an overview of ESP Work Automation, its key features, benefits, and applications.

ESP Work Automation is a robust software platform that encompasses a range of capabilities such as job scheduling, workload automation, and event-driven automation. The platform provides a centralized solution for managing and automating complex business processes across various systems, applications, and environments. With its intuitive interface and powerful features, ESP Work Automation enables organizations to optimize resource allocation, execute tasks seamlessly, and deliver exceptional results.

One of the primary components of ESP Work Automation is its advanced job scheduling functionality. This feature allows organizations to define, schedule, and manage batch jobs across multiple platforms and applications. Job scheduling automates the execution of routine tasks, ensuring timely completion and reducing manual effort. It enables businesses to coordinate complex workflows, manage dependencies, and allocate resources efficiently, thereby improving overall productivity and minimizing operational costs.

ESP Work Automation goes beyond job scheduling by offering comprehensive workload automation capabilities. Workload automation focuses on orchestrating and automating end-to-end business processes, including both technical and business tasks. By seamlessly integrating diverse systems and applications, ESP Work Automation facilitates smooth data flow, real-time monitoring, and exception handling. This feature streamlines complex workflows, reduces latency, and enhances collaboration across teams, departments, and geographical locations.

Event-Driven Automation

ESP Work Automation excels in event-driven automation, where processes are initiated based on specific triggers or events. It allows organizations to define rules and conditions that trigger automated actions when specific events occur. For example, a new customer registration may trigger an automated workflow that includes sending a welcome email, updating customer records, and assigning tasks to relevant team members. Event-driven automation in ESP Work Automation enhances responsiveness, improves agility, and enables organizations to proactively address critical business needs.

Benefits of ESP Work Automation

1. Increased Efficiency: ESP Work Automation eliminates manual intervention, reduces human error, and accelerates process execution. By automating tasks, organizations can accomplish more in less time, resulting in improved efficiency and overall productivity.

2. Cost Optimization: By automating routine tasks and optimizing resource allocation, ESP Work Automation reduces operational costs. It frees up valuable human resources from repetitive activities, enabling organizations to focus on strategic initiatives and achieve cost savings.

3. Enhanced Accuracy: Manual processes are prone to errors, but ESP Work Automation ensures consistent and accurate execution of tasks. By eliminating human error, organizations can maintain data integrity, enhance compliance, and reduce rework.

4. Scalability and Flexibility: ESP Work Automation supports dynamic business requirements and seamlessly scales to accommodate growth. It enables organizations to adapt workflows, integrate new systems, and respond swiftly to changing market needs.

5. Real-time Monitoring and Analytics: ESP Work Automation provides comprehensive monitoring and reporting capabilities, allowing organizations to track task execution, identify bottlenecks, and analyze performance metrics in real-time. This empowers data-driven decision-making and continuous process improvement.

Applications of ESP Work Automation

1. IT Operations: ESP Work Automation simplifies IT operations by automating tasks such as server provisioning, software deployment, and infrastructure management. It ensures consistent and reliable execution, reduces downtime, and enhances IT service delivery.

2. Data Integration and ETL: ESP

Work Automation facilitates data integration and extract, transform, load (ETL) processes by automating data movement, transformation, and validation tasks. It enables organizations to streamline data pipelines, ensure data accuracy, and support analytics initiatives.

3. Business Process Automation: ESP Work Automation enables end-to-end automation of complex business processes, including order management, invoice processing, and customer onboarding. It eliminates manual handoffs, improves process visibility, and enhances overall efficiency.

4. DevOps and Continuous Delivery: ESP Work Automation supports DevOps practices by automating build and deployment processes, version control, and continuous integration/continuous delivery (CI/CD). It enables organizations to accelerate software delivery, ensure consistency, and improve collaboration between development and operations teams.

SSIS Best Practice with Naming Conventions

In 2006, Jamie Thomson originally came up with naming conventions for SSIS tasks and data flow components which makes packages and logs more readable and easier to find.

Koen Verbeeck and André Kamman extended the existing list with almost 40 tasks/components since then.

This list should be used just as a reference if you want to use standard naming convention in SSIS.

The point is whether or not you want to follow this naming standard, it’s always better to use a standard for your package maintainability and readibility.

Task

The acronyms below can be used at the beginning of the names of tasks to identify what type of task it is.

Task	Prefix
For Loop Container	FLC
Foreach Loop Container	FELC
Sequence Container	SEQC
ActiveX Script	AXS
Analysis Services Execute DDL	ASE
Analysis Services Processing	ASP
Azure Blob Download	ADT
Azure Blob Upload	AUT
Azure HDInsight Create Cluster	ACCT
Azure HDInsight Delete Cluster	ACDT
Azure HDInsight Hive	AHT
Azure HDInsight Pig	APT
Back Up Database	BACKUP
Bulk Insert	BLK
CDC Control	CDC
Check Database Integrity	CHECKDB
Data Flow	DFT
Data Mining Query	DMQ
Data Profiling	DPT
Execute DTS 2000 Package	EDPT
Execute Package	EPT
Execute Process	EPR
Execute SQL Server Agent Job	AGENT
Execute SQL	SQL
Execute T-SQL Statement	TSQL
File System	FSYS
FTP	FTP
Hadoop File System	HFSYS
Hadoop Hive	HIVE
Hadoop Pig	PIG
History Cleanup	HISTCT
Maintenance Cleanup	MAINCT
Message Queue	MSMQ
Notify Operator	NOT
Rebuild Index	REBIT
Reorganize Index	REOIT
Script	SCR
Send Mail	SMT
Shrink Database	SHRINKDB
Transfer Database	TDB
Transfer Error Messages	TEM
Transfer Jobs	TJT
Transfer Logins	TLT
Transfer Master Stored Procedures	TSP
Transfer SQL Server Objects	TSO
Update Statistics	STAT
Web Service	WST
WMI Data Reader	WMID
WMI Event Watcher	WMIE
XML	XML
Expression	EXPR

Components

These acronyms should be used at the beginning of the names of components to identify what type of component it is.

Component	Prefix
ADO NET Source	ADO_SRC
Azure Blob Source	AB_SRC
CDC Source	CDC_SRC
DataReader Source	DR_SRC
Excel Source	EX_SRC
Flat File Source	FF_SRC
HDFS File Source	HDFS_SRC
OData Source	ODATA_SRC
ODBC Source	ODBC_SRC
OLE DB Source	OLE_SRC
Raw File Source	RF_SRC
XML Source	XML_SRC
Aggregate	AGG
Audit	AUD
Balanced Data Distributor	BDD
Cache Transform	CCH
CDC Splitter	CDCS
Character Map	CHM
Conditional Split	CSPL
Copy Column	CPYC
Data Conversion	DCNV
Data Mining Query	DMQ
Derived Column	DER
DQS Cleansing	DQSC
Export Column	EXPC
Fuzzy Grouping	FZG
Fuzzy Lookup	FZL
Import Column	IMPC
Lookup	LKP
Merge	MRG
Merge Join	MRGJ
Multicast	MLT
OLE DB Command	CMD
Percentage Sampling	PSMP
Pivot	PVT
Row Count	CNT
Row Sampling	RSMP
Script Component	SCR
Slowly Changing Dimension	SCD
Sort	SRT
Term Extraction	TEX
Term Lookup	TEL
Union All	ALL
Unpivot	UPVT
ADO NET Destination	ADO_DST
Azure Blob Destination	AB_DST
Data Mining Model Training	DMMT_DST
Data Streaming Destination	DS_DST
DataReader Destination	DR_DST
Dimension Processing	DP_DST
Excel Destination	EX_DST
Flat File Destination	FF_DST
HDFS File Destination	HDFS_DST
ODBC Destination	ODBC_DST
OLE DB Destination	OLE_DST
Partition Processing	PP_DST
Raw File Destination	RF_DST
Recordset Destination	RS_DST
SharePoint List Destination	SPL_DST
SQL Server Compact Destination	SSC_DST
SQL Server Destination	SS_DST
SQL Server Mobile Destination	SSM_DST

How to Quickly Query LDAP in Visual Studio SSIS

At times, you need to do a quick query against LDAP, but if your access is quite restricted, sometimes instead of using SQL Server Management Studio, you can also use SSIS Project within Visual Studio to do it.

Steps

Launch Visual Studio.
Create a new project and double-click on Integration Services Project.
Name your Project name (e.g. ITNotaQueryLDAP), then click Create button.
For this exercise, we use the default Package.dtsx. If it’s not opened by default, just double click it on the right pane window.
Drag Data Flow Task from SSIS Toolbox window to the Control Flow window.
Now we want to add a Connection Manager by right-clicking on a blank area under Connection Managers pane and select New ADO.NET Connection….
A smaller window titled Configure ADO.NET Manager will pop up, click on the New button.
In a Connection Manager window, for the Provider, select OLE DB Provider for Microsoft Directory Services (under .Net Providers for OleDb). Then, click OK.
$OLE DB Provider for Microsoft Directory Services - .Net Providers\SqlClient Data Provider in SSIS$
Once you’re back in Connection Manager window, click on the Test Connection button. Make sure you see the Test connection succeeded screen. Click both OK buttons.
After you clicked OK, you can see the configuration summary of your SSIS connection. Click OK again.
At this point, you have a Data Connection in your Connection Managers which you can rename to LDAP Data Connection.
Next, you can either double-click on the Data Flow Task or click on the Data Flow tab and drag ADO NET Source from the left pane, under Other Sources.
Double click on the ADO NET Source box, and select the LDAP Data Connection for ADO.NET connection manager:, SQL command for Data access mode: and use the following SQL command as an example under SQL Command text:.
```
LDAP://DC=ITNota,DC=Corp,DC=com;(&(objectCategory=Person)
(SN=Smith));SN,givenName,displayName,
mailNickName,sAMAccountName,mail,msExchHideFromAddressLists,
homeMDB;subtree
```
This query specifically looks for all records for anyone with a last name of “Smith” in the domain. You need to replace the values of the DCs to your own domain and also the search string (Smith) to make it work in your situation. Click Preview button.
Once you clicked Preview button, You should see the first 200 rows.

That’s all there is to it.

Of course, there’s a caveat of using this as you can only view the first 200 rows of your result. This is not intended to be used as a solution of any deployment other than a workaround when you are limited with what you can access and you need to find a few records from your LDAP/Domain Services.

How to Fix Failed to Decrypt Protected XML Node Error in SSIS

One morning, you discovered your SSIS package failed to run with the following error message:

Started: 5:00:03 AM
Error: 2021-10-08 05:00:04.62
Code: 0xC0016016
Source: SSIS_Package_ITNota
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error

This is typically caused by a simple setting that is too often overlooked during deployment.

In general, you do not want to save any connection ID and password in your package so you can inject the value from variables that you store either in a database or if you use a Package Deployment Model, then in an XML saved in the *.dtsConfig file.

So what you want to do before you build the package for PROD deployment, you want to set the package property Protection Level to DontSaveSensitive and you set all the values for your variables in your configuration file.

Steps to Set SSIS Package Property Level

With your SSIS package opened, under the Control Flow tab, on a blank area, right-click your mouse and select Properties or press ALT+ENTER.
Under Properties, go to Security, and look for ProtectionLevel. Check the value. By default, it’s set to EncryptSensitiveWithUserKey.
The ProtectionLevel should be set to DontSaveSensitive.

Save the package and if the configuration is already setup, it’s ready to be deployed.

Gotcha in Executing SSIS from DTExec Command Line

This issue was found in a troubleshooting session in one batch job with one company. The company runs its SSIS using a third party enterprise software by calling dtexec command prompt utility (dtexec.exe) and passes the arguments from its interface.

The enterprise suite calls the SSIS package using an Active Directory account (DOMAIN\BatchJob) and the SSIS package is comprised of a *.dtsx and *.dtsConfig files (using legacy deployment model).

The *.dtsConfig contains the following:

<?xml version="1.0"?>
<DTSConfiguration>
  <DTSConfigurationHeading>
    <DTSConfigurationFileInfo GeneratedBy="DOMAIN\OriginalCreatorNTID" GeneratedFromPackageName="DTS_Name" GeneratedFromPackageID="{GUID}" GeneratedDate="3/31/2021 10:00:00 AM"/>
  </DTSConfigurationHeading>
  <Configuration ConfiguredType="Property" Path="\Package.Connections[CM_ADO_MyDB].Properties[ConnectionString]" ValueType="String">
    <ConfiguredValue>Data Source=DB-SERVER;Initial Catalog=ITNotaDB;Integrated Security=True;</ConfiguredValue>
  </Configuration>
  ...

Without revealing much of the enterprise application suite (since it’s irrelevant), the application has a box command to run to call on the dtexec.exe with another box to pass in arguments such as the following:

/FILE "D:\Temp\ITNotaSSISJob.dtsx" /CONNECTION "CM_ADO_MyDB";"Data Source=DB-SERVER;User ID=MyNTID;Password=*****;Initial Catalog=ITNotaDB;Application Name=SSIS-DTS_Name-{GUID}CM_ADO_MyDB;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

This can be easily replicated in the Command Prompt by typing the following:

C:\>"D:\Program Files (x86)\Microsoft SQL Server\###\DTS\Binn\dtexec.exe" /FILE "D:\Temp\ITNotaSSISJob.dtsx" /CONNECTION "CM_ADO_MyDB";"Data Source=DB-SERVER;User ID=MyNTID;Password=*****;Initial Catalog=ITNotaDB;Application Name=SSIS-DTS_Name-{GUID}CM_ADO_MyDB;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

What happened then the job could not run because of the following error:

Error: 2021-03-31 11:20:32.54
Code: 0xC0208449
Source: DFT_***** [19]
Description: ADO NET Source has failed to acquire the connection {GUID} with the following error message: "Cannot open database "ITNotaDB" requested by the login. The login failed.
Login failed for user 'DOMAIN\BatchJob'.".
End Error

This is interesting because the assumption was that the user id that was used to login to the database would be MyNTID, which was passed as an argument. But what was really happening is that the service account NTID used to run the dtexec (whether it’s being called from a third party of the command prompt) is the one used as the database login. So what happened?

When you look at the two configuration settings, the assumption is during runtime, the argument that is passed from the command line would be the one that takes precedence over the configuration in the *.dtsConfig file. It turned out that is not the case. The setting from *.dtsConfig file is the one that takes precedence.

For a complete explanation, please read Understanding How Package Configurations Are Applied at Run Time. The most important part is quoted below:

The way in which the dtexec utility applies configurations affects the following command-line options:
You can use the /Connection or /Set option at run time to load package configurations from a location other than the location that you specified at design time.
You can use the /ConfigFile option to load additional configurations that you did not specify at design time.
However, these command-line options do have some restrictions:
You cannot use the /Set or the /Connection option to override single values that are also set by a configuration.
You cannot use the /ConfigFile option to load configurations that replace the configurations that you specified at design time.

So when there are configurations set in the *.dtsConfig file, it takes precedence over the argument passed from the command line.

Once we got this figured out, the client had to decide whether to remove the *.dtsConfig file or remove the injecting of parameters from the argument box (recommended) but grant an appropriate rights (e.g., dbo) to the service account (DOMAIN\BatchJob).

ESP Work Automation: Empowering Enterprises with Streamlined Workflows and Operational Efficiency

Event-Driven Automation

Benefits of ESP Work Automation

Applications of ESP Work Automation

SSIS Best Practice with Naming Conventions

Task

Components

Further Reading

How to Quickly Query LDAP in Visual Studio SSIS

Steps

How to Fix Failed to Decrypt Protected XML Node Error in SSIS

Steps to Set SSIS Package Property Level

Further Reading

Gotcha in Executing SSIS from DTExec Command Line

Further Reading

Recent Posts

Event-Driven Automation

Benefits of ESP Work Automation

Applications of ESP Work Automation

Task

Components

Further Reading

Steps

Steps to Set SSIS Package Property Level

Further Reading

Further Reading

Recent Posts

Tags