CData Sync

CData Workday Drivers and Connectors now allow users to create tables that execute specific Workday Query Language (WQL) statements for Workday reports. The new stored procedure, CreateWQLSchema, processes the output from Workday's "Convert Report to WQL" task and retrieves the same report data using the WQL statement. Read more about this Workday Driver enhancement here:Workday: How to Access Live Data from the WQL for a Workday ReportIf you have additional questions, please let us know!

Hi all,Our team is using CData Sync to extract and load data from SQL Server to Snowflake. The source MSSQL table is CDC-enabled, and we have created a CData Job with Change Data Capture enabled. We started testing the task with smaller tables (containing 100K - 300K records), and the task was successful without any issues. However, when trying with larger tables (containing 10M - 35M records), we faced failures every time.I have attached the error output for your reference. Has anyone come across this scenario? Any replies or help would be much appreciated. Thanks in advance.Error output: "2024-11-14T02:25:19.555+0000 2 [32|Q-Id] Failed to cache row [501097] The query was canceled."We tried running the same task with a few manipulations, like reducing the source table records to 1M over 10M but had no luck. We are not facing any other issues besides "Failed to cache row." What does caching mean in a CData

Hi Team, kindly help me on Jira custom fields are not visible in Cdata connector. ThanksChari

I’ve successfully created dozens of tasks to sync certain Salesforce objects to an Oracle destination.  However there is a group of Salesforce objects that are missing when I go to create a new task.  All are created/used by the same 3rd-part ISV app, but the account I am using for connecting to Salesforce is admin and I have no issue interacting with those objects outside of CData Sync.  I’ve read where this could be a cache issue, but I have not been able to find a cache refresh anywhere in CData Sync.  Would anyone have suggestions?  Thanks.

Hey All,I am trying to adhere to the principle of least permissions. I created a SQL Server Authenticated Login for cdata to use as a service account to SQL Server. I’m not sure what permissions it needs at the database level though? I wasn’t able to find any documentation on the site about minimum permissions cdata sync service account for SQL Server would need.I allow the cdata sync service account to drop and create tables. My guess is that I only need to add the cdata sync service account user to the following database level roles in my data lake.ddl_admindb_datareaderdb_datawriterWhat I am not sure about is whether or not Cdata Sync Service Account needs to have permissions to create Schemas.Thanks

What are the suggestion in how to load tables over 15 millions rows using CData Sync? The data source is ServiceNow and the destination is SQL Server. I changed the sync.exe.config to<?xml version="1.0" encoding="utf-8" ?><configuration>  <startup>    <supportedRuntime version="v4.0" />   </startup>  <!-- You can add some Java JVM parameters here. This is for Java application only.   <appSettings>    <add key="JAVA_OPTS" value="" />  </appSettings>  --><appSettings>     <add key="JAVA_OPTS" value="-Xms2g -Xmx50g"/> </appSettings>    </configuration>Also, I changed the timeout of the jobs to 6200.

We're delighted to spread some holiday cheer with the latest enhancements to CData Sync, designed to elevate your data integration and management capabilities this festive season: 🔐 Custom Roles for Enhanced Security: Gain greater control over user access and permissions with our new Custom Roles feature. Tailor roles to fit your organization's needs and restrict the usage of customer-created objects for improved security. 🔄 Dynamics 365 Reverse ETL Destination: Seamlessly sync data back into Dynamics 365, enhancing your CRM and ERP workflows during this busy holiday period. ⚡ Enhanced CDC: Experience faster and more reliable Change Data Capture, ensuring your data is always up-to-date with improved efficiency—just in time for year-end reporting. ❄️ Snowflake Performance Improvements: Enjoy significant performance boosts in our Snowflake integration for quicker data processing and analytics, helping you unwrap insights faster. 📚 Full Feature Docume

Is it possible to create multiple schedules for a single Sync job?The use case here is that I'd like to run a job only once per day on some days of a month but then multiple times per day for some other days.One idea that comes to my mind is that I can create two jobs with different schedules, but the problem here is that if a destination table is incrementally synced (which is required for large tables), two jobs will not be able to coordinate.

How do I use the REST API to enable the automatic job retry option? I’m not seeing that option in the API REST documentation.I am using CData Sync cross platform.

I’m trying to do a transformation after a dataload to a SQL server.The query used is: (the query is tested and works on the SQL server)MERGE INTO [S_Material_Data] AS tgtUSING ( SELECT HASHBYTES('SHA2_256', mat.MatNr) AS HSH_Material, CR_date AS Creation_Date, CASE WHEN mat.CH_Date IS NOT NULL THEN mat.CH_Date ELSE mat.CR_date END AS Changed_On, mat.Mat_Type AS Material_Type, mat.Mat_Grp AS Material_Group, mat.PRD_Hier AS Product_Hierarchy, Mat_Disc AS Material_Description, Mat_DiscU AS Material_DescriptionUp FROM Materials mat LEFT OUTER JOIN Descriptions dsc ON mat.MatNr = dsc.MatNr AND dsc.Lang = 'E' ) AS src ON tgt.HSH_Material = src.HSH_MaterialWHEN MATCHED THEN UPDATE SET tgt.Creation_Date = src.Creation_Date, tgt.Changed_On = src.Changed_On, tgt.Material_Type = src.Material_Type,

Hi CDATA community, I’m Jelle from Belgium and very new to CDATA Sync. I’m very excited about all what CDATA Sync has to offer. I’m currently working to get our Amazon Marketplace data loaded in our Snowflake instance via Sync.Are there other Sync users who are working with the Amazon Marketplace connector and are willing to discuss and share their experiences?Thanks!RegardsJelle

Is there a way to set a default Schema to default to when saving information into SQL server?  I know how to do it by each table but find my self forgetting from time to time.

:45:17.636-06:00  1  [42|Q-Id]  [META|Schema] Executed  column schema query: [App_History] Success: 4 results (268 ms)2024-09-10T21:45:17.636-06:00  2  [42|Q-Id]  [EXEC|Messag] Executed query: [SELECT *, IsPseudoColumn FROM sys_tablecolumns WHERE  SchemaName = @schema AND  TableName = @table] Success: (268 ms)2024-09-10T21:45:17.636-06:00  2  [42|Q-Id]  [EXEC|Parsed] Executing query: [SELECT TOP 100000 * FROM [dbo].[App_History] AS [WITH] WHERE [ID] > @pk_b0 ORDER BY [ID] ASC]2024-09-10T21:45:17.636-06:00  2  [42|Q-Id]  [EXEC|Parsed] Parameter: pk_b0 = 0f8a8d2f-1266-4f33-86ed-0d94f446125c2024-09-10T21:45:17.636-06:00  5  [42|Q-Id]  [META|Schema] Engine Invalid object name 'App_History'2024-09-10T21:45:17.636-06:00  3  [42|Q-Id]  [TCP |Send  ] Send Begin - Length: 2742024-09-10T21:45:17.636-06:00  5  [42|Q-Id]  [TCP |Send  ] [SQL |Messag] Query Failed: [SELECT TOP 100000 * FROM [dbo].[App_History] AS [WITH] WHERE [ID] > @pk_b0 O

2024-09-10T00:52:03.708-06:00    1    [6|Q-Id]    [SQL |Cache ] Cache failed: [500] Could not execute the specified command: Name Tool ID# not found inside D at [1:97]2024-09-10T00:52:03.708-06:00    0    [6|Q-Id]    [SQL |Cache ] Replication Failed. Error:[500] Could not execute the specified command: Name Tool ID# not found inside D at [1:97]2024-09-10T00:52:03.708-06:00    1    [6|Q-Id]    [RealDB:System.Data.CData.SQL]The connection is disconnected.2024-09-10T00:52:03.708-06:00    1    [6|Q-Id]    [RealDB:GoogleBigQuery]The connection is disconnected.2024-09-10T00:52:03.708-06:00    1    [6|Q-Id]    [RealDB:SQLite]The connection is disconnected.2024-09-10T00:52:03.708-06:00    1    [6|Q-Id]    [SQL |Messag] Query Failed: [REPLICATE [tools_data_testing] WITH _id = '5a2DAIBt+eVwYnh0y2b6zA==' SELECT [Access Method] AS [access_method], [Actively Selling] AS [actively_selling], [Actual Deployment Date] AS [actual_deployment_date], [Additional Languages]

I’m encountering an error in CData Sync when I try to create and test a source connection. The complete error message is Connection open failed: [500] Failed to login 'SERVERNAME', Cannot open database 'DBNAME' requested by the login. The login failed. Login failed for user ‘user’. What is the requirement for the SQL server so that CData can establish a connection? Also, are there any logs in CData we can check that has more details about the error?The documentation does not say anything required from the source other than the server and login credentials.

We are currently implementing CData to sync to a data warehouse. I’m not non-techy guy on the team and we’re trying to figure out the tables and views that are listed in the Sage Intacct Connector for CData Sync - Data Model.Are these the only tables/views available? Also, what’s the different between a table and a view? Why would a Gldetail and Glentry view, which should have the same columns, be different?Thanks!Steven

I’d like to get information if certain job finishes successfully, but 0 rows are replicated. I found snippet that send email, but how can I write an “if query” that catches only  those scheduled jobs that I want? There is snippet template for that, but I don’t understand how it need to be modified <!-- Enumerate Through Query Results --><api:enum prefix="Query#"><api:set attr="temp.index" value="[_index]" /><api:set attr="temp.query" value="[_value]" /><api:set attr="temp.status" value="[QueryStatus#[_index]]" /></api:enum>  

Hi,I have set Additional Options to TransactionSize=100 and Batch Size to 50 in the Job’s advanced section. My understanding is that setting a Transaction Size will commit the rows at whatever number is assigned, so in my case when 100 records have been cached, those are committed to the database.Unfortunately, the job is not saving any records to the Microsoft SQL destination table for one of the source tables. When I turn on verbose logging, I can see in the logs that 10000+ records have been cached, because I can see the data from those records in the log file. There are other tables which are successfully being populated. Just this one is not. When it errors out at some 20kish row the job ends and there is no data in my destination table. How do I make the job save data it is caching to the table before the error is thrown?

🚀 Exciting News from CData! 🚀 We're thrilled to announce the latest enhancements to CData Sync, designed to elevate your data integration and management capabilities: 🔄 New Reverse ETL Catalog Adds: Featuring new sources like Google BigQuery, PostgreSQL, and Amazon Redshift for more dynamic data integration strategies. 💡 Databricks Boost: Major performance upgrades to our Databricks integration for faster, more efficient data processing. 📥 PostgreSQL Enhancements: Introducing the 'Copy Into' function, simplifying data imports and improving database management efficiency. 🔄 Flexible Sync Modes: Easily switch between CDC and batch processing to better manage your data synchronization tasks.  📚 Full Feature Documentation: For a comprehensive understanding of all new features, visit our Current Release Documentation. 

Ran across this while looking at the Snowflake connector for Sync’s documentation.(i.e. help.rst#Snowflake-Source)  C’mon guys, you can do better than this.  ;-)   It’s just an example -- but someone should be taking a pass through all the documentation to look for unhelpful stuff like this, and fix it.

Hi, Our CData installation started as a proof of concept using the default built-in application database. CData is now a critical part of our data infrastructure and we’d like to migrate the application database to a more robust platform.Is there a tool or process available to migrate the default application database to SQL server?

Hello!I know that I can define an out environment variable in pre-job event and use that in the task replication query (documented here and here excellently).I’m probably stretching here but can I define that out-variable to read from a file or any other source and then run the sync task for ALL values of that variable? Kind of a loop? Thanks in advance for you answers!

How can i write to sub-directories in AzureDataLake. I am able to write to parent folder but sub-directories are not working

Hi all,I am receiving an error when I am trying to replicate a table from QuickBooks to a local SQL Server.“[0] Failed to cache row [126934] The iteratorID "{4853b873-e75a-4f1a-a4d6-2c32253237ad}" is not valid.”See the screenshot below. To try and address this I have: Decreased the Pagesize property in my Quickbooks connection to 50 from 500. Enabled Persistent connections and increased the idle timeout in my Quickbooks Remote Connector from 30 to 120.I restarted the services but none of that worked. Any suggestions?  

Hi,When I try to create a SQLServer connection for a local SQL Express install, CData Sync is throwing the following error: System error: UnknownHostException - No such host is known (DATA-DEV2\SQLEXPRESS)I am able to connect to the server with SSMS but for some reason CData cannot find it. CData and SQL Express are on the same server. What am I missing? Thanks.