Failed to get schema error via SQL Job

[yonto]

I have run into an issue with an SSIS package that loads order data into Salesforce. The package runs fine in Visual Studio (2010). It runs fine when I execute the SSIS package directly on the SQL server (Sql 2012). When I try to run a sql job to execute the SSIS package (via the sql agent) I receive the following errors:

Code: 0x00000000
Source: Merge and Load Data Devart Salesforce Destination
Description: Failed to get the schema from data source. Detailed error messages are: Unknown table 'SALES_HISTORY_DETAIL'.
End Error

Code: 0xC004706B
Source: Merge and Load Data SSIS.Pipeline
Description: "Devart Salesforce Destination" failed validation and returned validation status "VS_ISBROKEN".
End Error

Any assistance would be appreciated.

Thanks,

Dominic

[Shalex]

1. Are you running your package on the same workstation in both cases?

2. Try adding the "Refresh Metadata = True;" option in your connection string. Does this help?
JIC: the "Refresh Metadata=true;" connection string parameter causes a refresh of Salesforce metadata each time when the connection is opened. Leads to the performance loses. So, remove this parameter after the issue is fixed.

[yonto]

1 - Yes. The SSIS package is always called from the same workstation.

2. Refresh Metadata=True allowed the package to run when called from a SQL job. I had tried manually refreshing the metadata in the Devart Salesforce Connection Manager in Visual Studio, but the package still failed when called from a SQL Job. Can you explain why I wouldn't want to leave this fix in place? You mentioned removing the parameter in your response and I am not sure why I would need to do that as it got things to work.

I appreciate your help.

Dominic

[Shalex]

The "Refresh Metadata=true;" connection string parameter causes a refresh of Salesforce metadata each time when the connection is opened. Leads to the performance loses.

Does the issue persist when you remove "Refresh Metadata=true;" from your connection string now?

[yonto]

I have left the "Refresh Metadata=true;" for now. The SQL job is failing every other time it is executed.

[Shalex]

If the performance of your package with "Refresh Metadata=true;" doesn't suit your task, please perform the following test so that we can identify the reason why it fails without this option:

1) are you using exactly the same connection string when running package in Visual Studio and in SQL job?

2) in addition to "Refresh Metadata=true;", also initialize the Metadata Cache and Data Cache connection string parameters (for example: "Refresh Metadata=true;Data Cache=D:\\1.db;Metadata Cache=D:\\1_metadata.db;"). Run your package within Visual Studio to create valid metadata. The created *.db files are SQLite databases. Make sure that there is the record with TABLE_NAME=SALES_HISTORY_DETAIL in the SYS_TABLES table within 1_metadata.db.

3) change
"Refresh Metadata=true;Data Cache=D:\\1.db;Metadata Cache=D:\\1_metadata.db;"
to
"Refresh Metadata=false;Data Cache=D:\\1.db;Metadata Cache=D:\\1_metadata.db;"
and run your package in SQL job.
Does it work now?