Data Integration

Hi all, I’m trying to create a mapping to translate a CSV format with parent-child rows into a flat CSV format. My source file is structured like this:Row Type Order number Customer name Total Cost Header 12345 John Smith $15.00 Row Type Item code

What are Lookups?Lookups describe a situation where you need to find a target piece of data, like an Account ID, but you only have a related public piece of data, like an email domain. You can reference some data store, like a database or SaaS platform, to “look up” the Account ID based on the email domain. If you’ve ever “looked up” the definition of a word in a dictionary, then you already understand the concept -- the spelling of the word is your starting public data (equivalent to the email domain), the definition of the word is the target data (equivalent to the Account ID), and the dictionary itself is the data store.Why are Lookups useful?Lookups solve critical problems that may arise when integrating external data (like EDI documents) into internal systems (like CRM or ERP platforms). When you integrate data into an ERP system, you may need to

Hello CDATA Community,I hope this message finds you well. I am currently facing an issue with the CDATA Sync tool and would appreciate any insights or assistance from the community.Problem Description: I have configured a job with a single task, where the source is Salesforce, and the destination is an SQL database. Initially, I ran the task without incremental replication, and it successfully synchronized all records for a specific Salesforce object. Later, I implemented incremental replication and verified that it worked as expected. However, when I decided to remove incremental replication and attempted to sync the data again, the job seems to persist in using incremental replication, and not all data is being synchronized. Steps Performed:Created a job with Salesforce as the source and SQL database as the destination. Ran the task without incremental replication, successfully syncing all

We have a lot of manufacturers that we send X12 850 purchase orders to.  We don’t always receive a 997 and I am wondering if its possible to read and parse the MDN and extract the response confirming delivery.My goal is to confirm AS2 delivery and add an Email send connector to the flow and notify our purchasing department that the PO was received. Thanks

When bulk loading to Snowflake, data is uploaded to a temp table (named by CData) and then insert/merged into the final target table.Is there a way to specify, either at the Connection level, or the Job level, what Database or Schema should be used to store those tables?  By default it just puts them in the target schema, but it would be nice to split those out so we can more quickly clean anything up after an aborted job.

I now use CdataSync to transfer ActianZen database data to Bigquery through Btrieve Connector.1. Even after the data transfer is completed, if you view it in ActianZen's Monitor, it still exists in ActiveFile, that is to say, the resources have not been released.2. The open mode of the file whose resources have not been released is not the readonly mode, but the normal mode.Because of the above problems, table lock occurs on the system operation page using the ActianZen database.If you know how to solve it, or how I should try to solve it, please tell me, thank you very much.

is it possible for us to configure CData Sync to use more RAM of the VM? For example, when I do a initial replication of 1mil records from Redshift (RA3.16xlarge) to Snowflake (medium wh).. it takes roughly 3 mins.. I noticed Cdata is using only ¼ of the total 32GB RAM available. 

Hello,When creating temp files to be bulk loaded up to Snowflake, CData Sync appears to be creating the files in this folder on our Windows server:file://C:\\Windows\\SERVIC~1\\NETWOR~1\\AppData\\Local\\Temp\\*Is there a way to direct CData to use a different folder, globally, for all temporary workspace?Thank you!

Hi Everyone,I have a scenario to use Azure Managed SQL Instances and these are not supported for DBAmp because they do not allow the installation of 3rd-party linked servers on the Instance. So I am trying to install dbamp remotely - DBAmp - Running DBAmp Remotely (cdata.com)Client have created 2 servers one where data will be stored - dbSeverTwo where dbamp will be configured as linked server - dbampServerData Scenario - Data will be storing in Ddrive so I am using SSIS to push the data into SQL server I have created package and deploying that package to both dbserver and dbamp ServerSo my question is in the article it says to create remote database  “Salesforce Backups” in remote server instance to hold the salesforce data but can’t we store the same data in dbamp server? Is there any security concern or limitation if we

Hi,We have a client who is interested in using the new Salesforce custom address fields. (https://help.salesforce.com/s/articleView?id=sf.fields_caf_requirements.htm&type=5) . Because it looks like data can only be imported using Bulk Api 2.0 does anyone know if DBAmp can support import into these new fields? Thanks!Eve

Why DBAmp V22 SF_MirrorAll takes much more time (couple hours) than V5.1.9 SF_RefreshAll? even I skipped many tables, like %History, %Share... Also there are many error messages: Error occurred creating primary key for table.

Hello,Has anyone experimented with different batch sizes while sending data into Snowflake?  It looks like the default is 100000 rows.  I am wondering if there are any downsides to trying a higher number.Thanks!

I have following script: <arc:set attr="Files#1">[FilePath]</arc:set><arc:call op="xmlDOMSearch?uri=[FilePath]&xpath=/Items/File"> <arc:set attr="Files#[_index | add(1)]">SOME_FILEPATH\[xpath('FileName')]</arc:set></arc:call><arc:enum attr="archiveFiles" expand="true"> <arc:set attr="_log.info">([_index]) [_attr] -> [_value]</arc:set></arc:enum> Whatever I try, the enumeration only shows the first item.  If I add a arc:set="_log.info” in the arc:call, each item is logged, but for some reason it is not added in the array. The reason I want to add these files in an array is that I want to compress them all in 1 ZIP file.

One of the trickiest types of documents to map against in the EDI 856, which makes frequent use of a segment loop called HL (Hierarchical Loop). HLLoops represent multiple elements that are part of a hierarchical structure at once. From the EDI standpoint, each HLLoop is treated like a sibling element, but the HLLoop is a representation of an element in a hierachy so two neighboring HLLoops can mean different things based on the values of HL01, HL02, and HL03:HL01 is the index of the loop from the top of the documentHL02 is the index of the immediate parent to this loopHL03 is the type of the loopLet's look at a simplified 856: HL*1**S~  &l

I was wondering if anyone had a good solution for purging files from all flow connectors.  Maybe a custom script where I could pass in WorkspaceId and a ConnectorId that would delete files from the Send/Receive directories.My initial thought, is it possible to delete files across all connectors within any given flow?I have a few CSV connectors that are used to download 5K+ rows, split them into their own file and pass the data into a SQL stored procedure.Because of the split, that single CSV is now a new XML file for each line.  As it travels down the flow I end up with 5K+ XML files in my Send/Receive directories.  Usually I get an Arc notification that the size of X directory has exceeded the recommended size..Right now, I use the interface to click Delete All from both Input/Output tabs to keep folders clean. 

I don’t know if this is possible but I have a standard 850 map that I am able to use with all of our trading partners.  I am using the SQL connector to return the data of a simple Header/Detail table relationship.  Up until now, the data from the Detail table has always been sufficient for all of our current connections, but I have a new connection i’m setting up and they require additional data.Currently, I am creating POLoop1 > PO1and sending the following:PO101 linenumberPO102 QuantityPO103 Identifier (EA)PO104 PricePO105 (empty)PO106 Identifier (N4)PO107 UniqueIDThis new setup requires PID (which I have added) but they only need PID05 (item description).My question is: Is is possible to add some arc script within my loop where I can call a SQL stored proc and pass it the value of PO107 and map the result to PID05?Thanks

Just received an error on the Input of my AS2:The Message Integrity Check returned by the server is incorrect. Expected: iPPnthXZ7ofwn1v8b6lzhhPhE+2ZYHp+aHDfRgJsI8c=; Received : hrROR/jpYl1Q7PKbG2tY4AHImdCALRS37NPEDiH+5go=This is the first time I’ve seen this error.  What I don’t know is - is this on my side or the trading partner?Anyone familiar?Thanks.

​Setting the Batch Input Size in a database connection can increase the performance of that connection (provided the data source itself supports batches), because instead of sending requests one at a time, a batch insert or batch update can commit multiple records in a single request.  Ordinarily, this cannot be used with the default UPSERT action for database connections. An UPSERT involves a query that first determines if the record exists so that new records can be INSERTed and existing records can be UPDATEd, but UPSERTs alternate SELECTs and INSERT/UPDATEs and cannot batched. If you were to be able to separate the requests into INSERTs and UPATEs, however, you could batch each types, and it so happens that there is a Lookup action for most database connectors. https://cdn.cdata.com/help/AZJ/mft/Database-Lookup.h

Hi guys, I cannot get the companies and deals table to execute in the data ingestion phase. Anyone has experienced this before? The error I keep getting is:Executing table [HubSpot].[Companies]:failed with error:System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out 

📢 Exciting Updates from CData Sync! Q4 2023 Release Highlights 🌟 We're thrilled to announce the latest features in the Q4 2023 CData Sync release! Here's a quick snapshot:  API Connector - Seamlessly connect with any API without coding. Perfect for users of all technical levels.   Continuous Replication - Quicker, point-and-click replication options for near real-time data access. No more complex setups!  Integration with Databricks Unity Catalog - Embrace unified data governance by managing assets across all platforms efficiently.  🔗 Dive deeper into these updates in our Q4-2023 Sync Release Blog. 🎥 Want a visual guide on the API connector? Check out our instructional

Hello, couple of CSV source related questions…How do I indicate, for a CSV-based source is, what the primary key column is for each table, so that an upsert can be properly done?   Is it possible to move the files to another folder (in the operating system) after Sync has ingested them?  We would want to archive the files so they don’t get re-processed the next time.  I can do this outside of Sync if necessary, but it would be cleaner if Sync could do this somehow (or call out to a batch file on the OS and run that).   In my case, I have many source files with a timestamp, like MYFILE_XYZ_20231006.TXT.  I would like to use the file Aggregation so that I can just pull them all into the same target table.  However when I do that, is there a way to pass along the individual filename being processed, into a column in the target table?Thank you for any assistance you can provide! 

Open Source helps to collaborate, build networks, help a wide range of people, develop skills, and gain real-world experience. But what makes open source so popular and trendy today? It is the technology community that is willing to share knowledge and allow people across the world to contribute. We are here to talk about 5 popular open-source tools that are widely used in the world of data science, data engineering, and analytics and how we are utilizing them in the CData Sync app. DBT is an open-source data build tool used to transform data where it lives, and since the CData Sync app version 22 you can directly run transformation models within the app making sure that users have an enhanced experience. PostgreSQL is an advanced open-source RDBMS. It is one of the most popular RDBMS among data engineers due to its stability, features, and how it is constantly improving its performance. In the

PurposeSteps to create Oracle Destination Connection, which is the native Oracle Managed Data Access Client (Oracle.ManagedDataAccess.dll) in CData Sync .Net Edition.  Step 1: Download ODP.NET_Managed_ODAC.zip from 64-bit Oracle Data Access Components (ODAC) for Windows  Step 2: Copy the Oracle.ManagedDataAccess.dll from D:\Downloads\ODP.NET_Managed_ODAC122cR1.zip\odp.net\mana

Hello, Snowflake seems to be case-sensitive with table names, sort of.  If the incoming table name from Sync gets created with lower or mixed case, it gets created that way in Snowflake, and you have to reference them with quotes later on when querying.  Like    select * from “my_table".  Whereas if it were created as MY_TABLE, you wouldn’t need to bother adding quotes.Some data sources (a SQL server database we have, Active Directory, etc.) have lower or mixed case tables as exposed in CData.Is there a way to uppercase these either on the source as the metadata is read, or, when created in the destination?thank you!

Hi, I have a case where receiving  XML files with base64 (e.g. PDF)files inside the file which ones do I want to send as attachments in an email with Email Send connector (Template mode)maybe someone knows what script could be used?XML example<?xml version="1.0" encoding="utf-8"?><ForwardingInstructions xmlns="urn:oasis:names:specification:ubl:schema:xsd:ForwardingInstructions-2" xmlns:dtd="http://dtd.riege.com/scope/shippingorder" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2">  <cac:DocumentReference>    <cbc:ID>523651</cbc:ID>    <cbc:DocumentType>comment</cbc:DocumentType>    <cbc:DocumentDescription> Transporter: Agile Logistics AS - BIL - Bil (Vei transport)</cbc:DocumentDescription>  </cac: