ohdsi / etl-cdmbuilder Goto Github PK

ETL-CDMBuilder is a repo containing a .NET Core application to perform ETL to OMOP CDM for multiple databases

License: Apache License 2.0

C# 94.14% TSQL 5.35% Dockerfile 0.08% Perl 0.44%

etl-cdmbuilder's Introduction

The .Net CDM Builder was developed by Janssen Research & Development as a tool to transform its observational databases into the OMOP Common Data Model. The tool was specifically developed for the Janssen environment: MS SQL Server/PostgreSQL/MySQL. Additionally, the builder logic designed is based on the input format of the source data that are loaded in our local environment. We have made the tool open-source as a reference for other researchers in the OHDSI community who may be looking ETL their observational data, but we do not expect the tool would execute successfully for others unless they have similar infrastructure and similar source datasets. The tool was not originally designed to support general purpose ETLs across different platforms, and modifications would be required to apply to other systems. Contributions from the community to advance the tool in that direction are welcome and encouraged, as our team cannot support and test the tool in other environments.

Technology

The CDM Builder was created using the .NET Core 3.1 and is meant to be executed against Microsoft SQL Server 2012+ databases, PostgreSQL and MySQL databases.

System Requirements (for each server that will be running CDM Builder)

.NET Core 3.1
Visual Studio 2019+ is only required if you're looking to re-build the solution from source code
SQL Server builds: MS SQL ODBC Driver
PostgreSQL builds: PostgreSQL ODBC Driver
MySQL builds: MySQL ODBC Driver

Dependencies

A SQL Server or PostgreSQL or MySQL database of the OMOP Vocabulary must be accessible within your network. This will be used to map source concepts into CDM concepts.
For SQL Server, SQL-based accounts are needed, as Trusted Connections via Windows Authentication are not supported.
In the MAN\VOCAB_ADDITIONS folder, you will need to append these files to the OMOP Vocabulary (i.e. add the JNJ_TRU_P_SPCLTY file to the records already in SOURCE_TO_CONCEPT_MAP and do the same for the VOCABULARY table with JNJ_VOCABULARY).

Getting Started

Download from the GitHub CDM Builder https://github.com/OHDSI/ETL-CDMBuilder/tree/master/source.
The application requires the configuration of three databases: Source of non-CDM data, Destination (where to build the CDM data), and Vocabulary (the OMOP Vocabulary)
- Connection strings templates (MS SQL, PostgreSQL and MySQL) and Vendor for CDM Builder database can be configured either in the config file (App.config)
The CDM Builder comes with several pre-written ETLs, each of which corresponds to a specific data vendor and data product. Select the appropriate vendor from the "Vendor" dropdown
- The vendors and products can be found here: https://github.com/OHDSI/ETL-LambdaBuilder/tree/master/docs

Kicking off a Build

Windows environment
- Go to the ETL-CDMBuilder\executable\ folder
- Update org.ohdsi.cdm.presentation.builder.dll.config file (Uncomment necessary rows):
  1. Select necessary vendor ("Vendor", "PersonBuilder")
  2. Select CDM version ("CDM")
  3. Choose connection string templates (MsSQL, PostgreSQL or MySQL) for source, destination and vocabulary databases. ("SourceConnectionStringTemplate", "DestinationConnectionStringTemplate", "VocabularyConnectionStringTemplate")
- Run org.ohdsi.cdm.presentation.builder.exe
- [settins tab] Through UI specify source, destination and vocabulary settings (server, database name, schema name, user and password)
- Go [building tab] click start button
Linux docker (asp .net core web service) git clone https://github.com/SoftwareCountry/ETL-CDMBuilder.git git pull origin master docker build -f "source/org.ohdsi.cdm.presentation.builderwebapi/Dockerfile" -t cdm_builder_service . docker run -d --network host cdm_builder_service -p 8090:9000

docker build -f "source/org.ohdsi.cdm.presentation.builderwebapi/Dockerfile" -t cdm_builder_service_dev . docker run -d --network host cdm_builder_service_dev -p 8090:9000

Getting Involved

Developer questions/comments/feedback: OHDSI Forum
We use the GitHub issue tracker for all bugs/issues/enhancements

License

ETL-CDMBuilder is licensed under Apache License 2.0

Development

###Development status Beta testing, source is not set up properly to deploy an executable this release.

Acknowledgements

Janssen Pharmaceutical Research & Development, LLC

etl-cdmbuilder's People

Contributors

Stargazers

Watchers

etl-cdmbuilder's Issues

Help with ETL CMD Builder

Dear All,

I am a new user of OHDSI tools and I am a little bit confused on the way on how to use it.
I would like to convert observational databases (CPRD in my case) in CDM OMOP.

I have downloaded the ETL CMD builder on Guithub (which seems to be the adapted tool) and try to launch the application (org.ohdsi.cdm.presentation.buildingmanager.exe) as indicated. But a SQL server seems to be mandatory to use this tool, isn’t it?
Is there any way to use ETL CMD builder without SQL server but using flat files for example?

Many thanks for your help.

Pauline

Process Just Stops

Hi Erica:

I setup the converter to do a CDM4 conversion from Truven Medicare data. The tool created the cdm4 db and populated the provider information but care_site and location were all populated with records of 0 for their primary keys. None of the person, condition_occurrence, drug_exposure or visit_occurrence data was populated. There were no errors either. I do have a v4 vocabulary DB setup as well. All my Truven data tables are populated with exception of lab data.

Do you have any insight as to why this maybe happening?

Thanks

Jorge

"Ambiguous column" messages

Hi
While processing my mdcr db I am getting the next errors:
at System.Threading.Tasks.TaskReplicator.Replica.Execute()
at System.Threading.Tasks.TaskReplicator.Replica1.ExecuteAction(Boolean& yieldedBeforeCompletion) at System.Threading.Tasks.Parallel.<>c__DisplayClass19_01.b__1(RangeWorker& currentWorker, Int32 timeout, Boolean& replicationDelegateYieldedBeforeCompletion)
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw(Exception source)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Parallel.<>c__DisplayClass19_01.<ForWorker>b__1(RangeWorker& currentWorker, Int32 timeout, Boolean& replicationDelegateYieldedBeforeCompletion) at org.ohdsi.cdm.presentation.builder.Controllers.BuilderController.<>c__DisplayClass18_0.<Build>b__2(Int32 chunkId, ParallelLoopState state) in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Controllers\BuilderController.cs:line 303 Stacktrace: at org.ohdsi.cdm.presentation.builder.Base.DatabaseChunkBuilder.Process(IDatabaseEngine sourceEngine, String sourceSchemaName, List1 sourceQueryDefinitions, OdbcConnection sourceConnection, String vendor) in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Base\DatabaseChunkBuilder.cs:line 48
Source: sqlncli11.dll
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'ethnicity_concept_id'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'ethnicity_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'race_concept_id'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'race_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'vendor'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'observation_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'observation_concept_id'.
Message: ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'payersource'.
Type: System.Data.Odbc.OdbcException
Inner Exception Found:
at org.ohdsi.cdm.presentation.builder.Controllers.BuilderController.PerformAction(Action act) in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Controllers\BuilderController.cs:line 55
at org.ohdsi.cdm.presentation.builder.Controllers.BuilderController.<>c__DisplayClass18_0.b__0() in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Controllers\BuilderController.cs:line 289
at System.Threading.Tasks.Parallel.For(Int32 fromInclusive, Int32 toExclusive, ParallelOptions parallelOptions, Action2 body) at System.Threading.Tasks.Parallel.ForWorker[TLocal](Int32 fromInclusive, Int32 toExclusive, ParallelOptions parallelOptions, Action1 body, Action2 bodyWithState, Func4 bodyWithLocal, Func1 localInit, Action1 localFinally)
at System.Threading.Tasks.Parallel.ThrowSingleCancellationExceptionOrOtherException(ICollection exceptions, CancellationToken cancelToken, Exception otherException)
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw(Exception source)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Parallel.ForWorker[TLocal](Int32 fromInclusive, Int32 toExclusive, ParallelOptions parallelOptions, Action1 body, Action2 bodyWithState, Func4 bodyWithLocal, Func1 localInit, Action1 localFinally) Stacktrace: at System.Threading.Tasks.TaskReplicator.Run[TState](ReplicatableUserAction1 action, ParallelOptions options, Boolean stopOnFirstFailure)
Source: System.Threading.Tasks.Parallel
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'ethnicity_concept_id'.)
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'ethnicity_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'race_concept_id'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'race_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'vendor'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'observation_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'observation_concept_id'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'ethnicity_concept_id'.) (ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'payersource'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'ethnicity_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'race_concept_id'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'race_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'vendor'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'observation_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'observation_concept_id'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'ethnicity_concept_id'.) (ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'payersource'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'ethnicity_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'race_concept_id'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'race_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'vendor'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'observation_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'observation_concept_id'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'ethnicity_concept_id'.) (ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'payersource'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'ethnicity_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'race_concept_id'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'race_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'vendor'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'observation_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'observation_concept_id'.
Message: One or more errors occurred. (ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'payersource'.
Type: System.AggregateException
9/20/2021 5:48:17 PM| Exception Found:
Stacktrace: at org.ohdsi.cdm.presentation.builder.Base.DatabaseChunkBuilder.Process(IDatabaseEngine sourceEngine, String sourceSchemaName, List`1 sourceQueryDefinitions, OdbcConnection sourceConnection, String vendor) in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Base\DatabaseChunkBuilder.cs:line 48
Source: sqlncli11.dll
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'ethnicity_concept_id'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'ethnicity_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'race_concept_id'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'race_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'vendor'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'observation_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'observation_concept_id'.
Message: ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'payersource'.
Type: System.Data.Odbc.OdbcException
9/20/2021 5:48:17 PM| Exception Found:

Query:
File name=ENROLLMENT_DETAIL
SourceConnectionString=
9/20/2021 5:48:17 PM| SourceEngine=org.ohdsi.cdm.framework.desktop.Databases.MssqlDatabaseEngine
Stacktrace: at org.ohdsi.cdm.presentation.builder.Base.DatabaseChunkBuilder.Process(IDatabaseEngine sourceEngine, String sourceSchemaName, List`1 sourceQueryDefinitions, OdbcConnection sourceConnection, String vendor) in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Base\DatabaseChunkBuilder.cs:line 48
Source: sqlncli11.dll
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'ethnicity_concept_id'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'ethnicity_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'race_concept_id'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'race_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'vendor'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'observation_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'observation_concept_id'.
Message: ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'payersource'.
Type: System.Data.Odbc.OdbcException
9/20/2021 5:45:57 PM| Exception Found:

Query:
File name=ENROLLMENT_DETAIL
SourceConnectionString=
9/20/2021 5:45:57 PM| SourceEngine=org.ohdsi.cdm.framework.desktop.Databases.MssqlDatabaseEngine
Stacktrace: at org.ohdsi.cdm.presentation.builder.Base.DatabaseChunkBuilder.Process(IDatabaseEngine sourceEngine, String sourceSchemaName, List`1 sourceQueryDefinitions, OdbcConnection sourceConnection, String vendor) in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Base\DatabaseChunkBuilder.cs:line 48
Source: sqlncli11.dll
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'ethnicity_concept_id'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'ethnicity_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'race_concept_id'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'race_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'vendor'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'observation_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'observation_concept_id'.
Message: ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'payersource'.
Type: System.Data.Odbc.OdbcException
9/20/2021 4:24:46 PM| Exception Found:

Query:
File name=ENROLLMENT_DETAIL
SourceConnectionString=
9/20/2021 4:24:46 PM| SourceEngine=org.ohdsi.cdm.framework.desktop.Databases.MssqlDatabaseEngine
Stacktrace: at org.ohdsi.cdm.presentation.builder.Base.DatabaseChunkBuilder.Process(IDatabaseEngine sourceEngine, String sourceSchemaName, List`1 sourceQueryDefinitions, OdbcConnection sourceConnection, String vendor) in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Base\DatabaseChunkBuilder.cs:line 48
Source: sqlncli11.dll
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'ethnicity_concept_id'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'ethnicity_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'race_concept_id'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'race_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'vendor'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'observation_source_value'.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'observation_concept_id'.
Message: ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Ambiguous column name 'payersource'.
Type: System.Data.Odbc.OdbcException
9/20/2021 4:06:25 PM| Exception Found:

What could be the problem?

JNJ_OPTUM_EHR_LABNAM duplicates

There are around 7 or so source_concept's that have duplicates (different casing) this fails the PK constraints on MSSQL Server 2016.

ETL CDMBuilder - Errors in 'Conversion from RAW to CDM' step

We are converting Truven MDCR data (2003-2015) to CDM format using ETL CDMBuilder. In 'Conversion from RAW to CDM' step, we got these errors:

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Message: This SqlTransaction has completed; it is no longer usable.

After first error, ETL CDMBuilder didn't stop and it kept processing the data. But it has stopped processing any data after the second error. 'Conversion from RAW to CDM' step has been running for hours to process the last two batches (19123 from 19124). I looked into programs Chunkbuilder.cs and Saver.cs, but didn't get much information about these errors. Could you please suggest me what should be the next step so that ETL CDMBuilder can run successfully.

The details of the errors are as follows:

Builder: HSC-CTSC-TRUVEN
Time: Mar 3 2017 5:09PM
Error: Exception Found:
Type: System.AggregateException
Message: One or more errors occurred.
Source: mscorlib
Stacktrace: at System.Threading.Tasks.Task.WaitAll(Task[] tasks, Int32 millisecondsTimeout, CancellationToken cancellationToken)
at org.ohdsi.cdm.presentation.builderprocess.Program.Main(String[] inputArgs)
Inner Exception Found:
Type: System.InvalidOperationException
Message: This SqlTransaction has completed; it is no longer usable.
Source: System.Data
Stacktrace: at System.Data.SqlClient.SqlTransaction.ZombieCheck()
at System.Data.SqlClient.SqlTransaction.Rollback()
at org.ohdsi.cdm.framework.core.Savers.Saver.SaveV5(ChunkData chunk, Boolean lastSave)
at org.ohdsi.cdm.framework.core.Base.ChunkBuilder.SaveData1()
at System.Threading.Tasks.Task.Execute()

Builder: HSC-CTSC-TRUVEN
Time: Mar 2 2017 9:26PM
Error: Exception Found:
Type: System.Data.SqlClient.SqlException
Message: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Source: .Net SqlClient Data Provider
Stacktrace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlInternalTransaction.Commit()
at System.Data.SqlClient.SqlTransaction.Commit()
at org.ohdsi.cdm.framework.core.Savers.MSSqlSaver.Commit()
at org.ohdsi.cdm.framework.core.Savers.Saver.SaveV5(ChunkData chunk, Boolean lastSave)
Inner Exception Found:
Type: System.ComponentModel.Win32Exception
Message: The wait operation timed out
Source:
Stacktrace:

Source code no longer on github?

Has the source code for ETL-CDMBuilder been removed from github? I only see the dll and exe files. Does this mean I no longer have to build it ?

Exception while Copying Vocabulary

Hi,

I am at the step of Copying Vocabulary part (5th step) and I am getting the following exception

Builder: oedmt-vm
Time: Jan 11 2019 1:58PM
Error: Exception Found:
Type: System.FormatException
Message: Input string was not in a correct format.
Source: mscorlib
Stacktrace: at System.Text.StringBuilder.FormatError()
at System.Text.StringBuilder.AppendFormatHelper(IFormatProvider provider, String format, ParamsArray args)
at System.String.FormatHelper(IFormatProvider provider, String format, ParamsArray args)
at System.String.Format(String format, Object arg0, Object arg1)
at org.ohdsi.cdm.framework.data.DbLayer.DbDestination.CopyVocabulary(String query, String vocabularyConnectionString) in C:\Users\vmadmin\Documents\ETL-CDMBuilder-master\source\Framework\org.ohdsi.cdm.framework.data\DbLayer\DbDestination.cs:line 174
at org.ohdsi.cdm.framework.core.Savers.MSSqlSaver.CopyVocabulary() in C:\Users\vmadmin\Documents\ETL-CDMBuilder-master\source\Framework\org.ohdsi.cdm.framework.core\Savers\MSSqlSaver.cs:line 66
at org.ohdsi.cdm.framework.core.Controllers.BuilderController.<>c.b__30_0() in C:\Users\vmadmin\Documents\ETL-CDMBuilder-master\source\Framework\org.ohdsi.cdm.framework.core\Controllers\BuilderController.cs:line 213
at org.ohdsi.cdm.framework.core.Controllers.BuilderController.PerformAction(Action act) in C:\Users\vmadmin\Documents\ETL-CDMBuilder-master\source\Framework\org.ohdsi.cdm.framework.core\Controllers\BuilderController.cs:line 79

Following is the code snippet that the exception is referring to

  public void CopyVocabulary(string query, string vocabularyConnectionString)
  {
     if (string.IsNullOrEmpty(query.Trim())) return;

     var vocab = new OdbcConnectionStringBuilder(vocabularyConnectionString);
     using (var connection = SqlConnectionHelper.OpenOdbcConnection(connectionString))
     {
        query = string.Format(query, vocab["server"], vocab["database"]);
        query = query.Replace("{sc}", schemaName);
        using (var command = new OdbcCommand(query, connection))
        {
           command.CommandTimeout = 0;
           command.ExecuteNonQuery();
        }
     }
  }

Demo Issue For Testing Git

Demo Issue

VOCABULARY_ADDITIONS questions

I am setting off the ETL-CDMBuilder to convert Truven data to CDMv5, however, I am wondering which of the vocabulary additions are needed to be appended to the source_to_concept. I am assuming both JNJ_TRU_HRA_QUESTION.txt and JNJ_TRU_P_SPCLTY.txt are needed, but they have different formats (one has 8 columns the other one has 9). Also, the text reads: "add the JNJ_TRU_P_SPCLTY file to the records already in SOURCE_TO_CONCEPT_MAP" however, this table is empty when you load the vocabulary, am I missing something that should be here?

Thanks in advance!

OMOP Common Data Version

The reference manual posted at (in 2016):

https://github.com/OHDSI/ETL-CDMBuilder/blob/master/man/ETL-CDMBuilder.docx

The CDM Builder can be used to transform observational datasets from their native formats and schemas into the OMOP Common Data Model (version 4)

Is this still its latest version? Is there a way to update this code to support the OMOP v5.3?
Thanks
Jose

Indices for the input Truven data tables

I created tables to load the Truven data (MDCR) and now I am running CDM Builder to convert MDCR data to CDM5 format. Should I create the indices for the input tables (e.g. facility_header, inpatient_services, etc.) before running the CDMBuilder.

Process hangs on massive dataset

Any documentation on how to handle extremely large datasource? We are trying to process IBM Truven CCAE Data residing on MSSQL. We have had success with two other data sets that are a tenth of the size we need to process now. The process hangs on initialization.

Any advice is welcomed.

Error messages I do not understand

Hi again
I started the latest version of the ETL-CDMBuilder and I got the next error messages. Can someone tell me what to look at? I am trying to convert a Truven mdcr database and my configuration file is at:

org.ohdsi.cdm.presentation.builder.dll.config.zip

Could this be related to the number of chunks or "Degree of Parallelism"?

My truven_mdcr database has an enrollment_detail table that is descried as:

Truven_mdcr dbo enrollment_detail age 5 smallint 5 2 0 10 1 NULL NULL 5 NULL NULL 1 YES 38
Truven_mdcr dbo enrollment_detail agegrp -8 nchar 1 2 NULL NULL 1 NULL NULL -8 NULL 2 2 YES 39
Truven_mdcr dbo enrollment_detail datatyp 5 smallint 5 2 0 10 1 NULL NULL 5 NULL NULL 3 YES 38
Truven_mdcr dbo enrollment_detail dobyr 5 smallint 5 2 0 10 1 NULL NULL 5 NULL NULL 4 YES 38
Truven_mdcr dbo enrollment_detail dtend -9 date 10 20 NULL NULL 1 NULL NULL -9 NULL NULL 5 YES 0
Truven_mdcr dbo enrollment_detail dtstart -9 date 10 20 NULL NULL 1 NULL NULL -9 NULL NULL 6 YES 0
Truven_mdcr dbo enrollment_detail eeclass -8 nchar 1 2 NULL NULL 1 NULL NULL -8 NULL 2 7 YES 39
Truven_mdcr dbo enrollment_detail eestatu -8 nchar 1 2 NULL NULL 1 NULL NULL -8 NULL 2 8 YES 39
Truven_mdcr dbo enrollment_detail efamid 4 int 10 4 0 10 1 NULL NULL 4 NULL NULL 9 YES 38
Truven_mdcr dbo enrollment_detail egeoloc -8 nchar 2 4 NULL NULL 1 NULL NULL -8 NULL 4 10 YES 39
Truven_mdcr dbo enrollment_detail empcty 4 int 10 4 0 10 1 NULL NULL 4 NULL NULL 11 YES 38
Truven_mdcr dbo enrollment_detail emprel -8 nchar 1 2 NULL NULL 1 NULL NULL -8 NULL 2 12 YES 39
Truven_mdcr dbo enrollment_detail empzip 5 smallint 5 2 0 10 1 NULL NULL 5 NULL NULL 13 YES 38
Truven_mdcr dbo enrollment_detail enrolid -5 bigint 19 8 0 10 1 NULL NULL -5 NULL NULL 14 YES 108
Truven_mdcr dbo enrollment_detail hlthplan -8 nchar 1 2 NULL NULL 1 NULL NULL -8 NULL 2 15 YES 39
Truven_mdcr dbo enrollment_detail indstry -8 nchar 1 2 NULL NULL 1 NULL NULL -8 NULL 2 16 YES 39
Truven_mdcr dbo enrollment_detail memdays 5 smallint 5 2 0 10 1 NULL NULL 5 NULL NULL 17 YES 38
Truven_mdcr dbo enrollment_detail mhsacovg -8 nchar 1 2 NULL NULL 1 NULL NULL -8 NULL 2 18 YES 39
Truven_mdcr dbo enrollment_detail msa 4 int 10 4 0 10 1 NULL NULL 4 NULL NULL 19 YES 38
Truven_mdcr dbo enrollment_detail phyflag -8 nchar 1 2 NULL NULL 1 NULL NULL -8 NULL 2 20 YES 39
Truven_mdcr dbo enrollment_detail plankey 5 smallint 5 2 0 10 1 NULL NULL 5 NULL NULL 21 YES 38
Truven_mdcr dbo enrollment_detail plantyp 5 smallint 5 2 0 10 1 NULL NULL 5 NULL NULL 22 YES 38
Truven_mdcr dbo enrollment_detail region -8 nchar 1 2 NULL NULL 1 NULL NULL -8 NULL 2 23 YES 39
Truven_mdcr dbo enrollment_detail rx -8 nchar 1 2 NULL NULL 1 NULL NULL -8 NULL 2 24 YES 39
Truven_mdcr dbo enrollment_detail seqnum -5 bigint 19 8 0 10 1 NULL NULL -5 NULL NULL 25 YES 108
Truven_mdcr dbo enrollment_detail sex -8 nchar 1 2 NULL NULL 1 NULL NULL -8 NULL 2 26 YES 39
Truven_mdcr dbo enrollment_detail version -8 nchar 2 4 NULL NULL 1 NULL NULL -8 NULL 4 27 YES 39
Truven_mdcr dbo enrollment_detail wgtkey 5 smallint 5 2 0 10 1 NULL NULL 5 NULL NULL 28 YES 38
Truven_mdcr dbo enrollment_detail year 5 smallint 5 2 0 10 1 NULL NULL 5 NULL NULL 29 YES 38

and my Vocab database does not show any Enrollment_Detail table

Thanks
Jose

ERRROR MESSAGES:
at System.Threading.Tasks.TaskReplicator.Replica.Execute()
at System.Threading.Tasks.TaskReplicator.Replica1.ExecuteAction(Boolean& yieldedBeforeCompletion) at System.Threading.Tasks.Parallel.<>c__DisplayClass19_01.b__1(RangeWorker& currentWorker, Int32 timeout, Boolean& replicationDelegateYieldedBeforeCompletion)
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw(Exception source)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Parallel.<>c__DisplayClass19_01.<ForWorker>b__1(RangeWorker& currentWorker, Int32 timeout, Boolean& replicationDelegateYieldedBeforeCompletion) at org.ohdsi.cdm.presentation.builder.Controllers.BuilderController.<>c__DisplayClass18_0.<Build>b__2(Int32 chunkId, ParallelLoopState state) in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Controllers\BuilderController.cs:line 303 Stacktrace: at org.ohdsi.cdm.presentation.builder.Base.DatabaseChunkBuilder.Process(IDatabaseEngine sourceEngine, String sourceSchemaName, List1 sourceQueryDefinitions, OdbcConnection sourceConnection, String vendor) in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Base\DatabaseChunkBuilder.cs:line 48
Source: org.ohdsi.cdm.presentation.builder
Message: Input string was not in a correct format.
Type: System.FormatException
Inner Exception Found:
at org.ohdsi.cdm.presentation.builder.Controllers.BuilderController.PerformAction(Action act) in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Controllers\BuilderController.cs:line 55
at org.ohdsi.cdm.presentation.builder.Controllers.BuilderController.<>c__DisplayClass18_0.b__0() in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Controllers\BuilderController.cs:line 289
at System.Threading.Tasks.Parallel.For(Int32 fromInclusive, Int32 toExclusive, ParallelOptions parallelOptions, Action2 body) at System.Threading.Tasks.Parallel.ForWorker[TLocal](Int32 fromInclusive, Int32 toExclusive, ParallelOptions parallelOptions, Action1 body, Action2 bodyWithState, Func4 bodyWithLocal, Func1 localInit, Action1 localFinally)
at System.Threading.Tasks.Parallel.ThrowSingleCancellationExceptionOrOtherException(ICollection exceptions, CancellationToken cancelToken, Exception otherException)
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw(Exception source)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Parallel.ForWorker[TLocal](Int32 fromInclusive, Int32 toExclusive, ParallelOptions parallelOptions, Action1 body, Action2 bodyWithState, Func4 bodyWithLocal, Func1 localInit, Action1 localFinally) Stacktrace: at System.Threading.Tasks.TaskReplicator.Run[TState](ReplicatableUserAction1 action, ParallelOptions options, Boolean stopOnFirstFailure)
Source: System.Threading.Tasks.Parallel
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_concept_id'.)
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'vendor'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_concept_id'.) (ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'payersource'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'vendor'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_concept_id'.) (ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'payersource'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'vendor'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_concept_id'.) (ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'payersource'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'vendor'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_concept_id'.) (ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'payersource'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'vendor'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_concept_id'.) (ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'payersource'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'vendor'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_concept_id'.) (ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'payersource'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'vendor'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_concept_id'.
Message: One or more errors occurred. (Input string was not in a correct format.) (ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'payersource'.
Type: System.AggregateException
12/14/2020 10:29:39 AM| Exception Found:
Stacktrace: at org.ohdsi.cdm.presentation.builder.Base.DatabaseChunkBuilder.Process(IDatabaseEngine sourceEngine, String sourceSchemaName, List`1 sourceQueryDefinitions, OdbcConnection sourceConnection, String vendor) in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Base\DatabaseChunkBuilder.cs:line 48
Source: sqlncli11.dll
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'vendor'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_concept_id'.
Message: ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'payersource'.
Type: System.Data.Odbc.OdbcException
12/14/2020 10:29:39 AM| Exception Found:

Query:
File name=ENROLLMENT_DETAIL
SourceConnectionString=
12/14/2020 10:29:39 AM| SourceEngine=org.ohdsi.cdm.framework.desktop.Databases.MssqlDatabaseEngine
Stacktrace: at org.ohdsi.cdm.presentation.builder.Base.DatabaseChunkBuilder.Process(IDatabaseEngine sourceEngine, String sourceSchemaName, List`1 sourceQueryDefinitions, OdbcConnection sourceConnection, String vendor) in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Base\DatabaseChunkBuilder.cs:line 48
Source: sqlncli11.dll
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'vendor'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_concept_id'.
Message: ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'payersource'.
Type: System.Data.Odbc.OdbcException
12/14/2020 10:29:35 AM| Exception Found:

Query:
File name=ENROLLMENT_DETAIL
SourceConnectionString=
12/14/2020 10:29:35 AM| SourceEngine=org.ohdsi.cdm.framework.desktop.Databases.MssqlDatabaseEngine
Stacktrace: at org.ohdsi.cdm.presentation.builder.Base.DatabaseChunkBuilder.Process(IDatabaseEngine sourceEngine, String sourceSchemaName, List`1 sourceQueryDefinitions, OdbcConnection sourceConnection, String vendor) in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Base\DatabaseChunkBuilder.cs:line 48
Source: sqlncli11.dll
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'vendor'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_concept_id'.
Message: ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'payersource'.
Type: System.Data.Odbc.OdbcException
12/14/2020 10:29:34 AM| Exception Found:

Query:
File name=ENROLLMENT_DETAIL
SourceConnectionString=
12/14/2020 10:29:34 AM| SourceEngine=org.ohdsi.cdm.framework.desktop.Databases.MssqlDatabaseEngine
Stacktrace: at org.ohdsi.cdm.presentation.builder.Base.DatabaseChunkBuilder.Process(IDatabaseEngine sourceEngine, String sourceSchemaName, List`1 sourceQueryDefinitions, OdbcConnection sourceConnection, String vendor) in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Base\DatabaseChunkBuilder.cs:line 48
Source: sqlncli11.dll
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'vendor'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_concept_id'.
Message: ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'payersource'.
Type: System.Data.Odbc.OdbcException
12/14/2020 10:29:31 AM| Exception Found:

Query:
File name=ENROLLMENT_DETAIL
SourceConnectionString=
12/14/2020 10:29:30 AM| SourceEngine=org.ohdsi.cdm.framework.desktop.Databases.MssqlDatabaseEngine
Stacktrace: at org.ohdsi.cdm.presentation.builder.Base.DatabaseChunkBuilder.Process(IDatabaseEngine sourceEngine, String sourceSchemaName, List`1 sourceQueryDefinitions, OdbcConnection sourceConnection, String vendor) in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Base\DatabaseChunkBuilder.cs:line 48
Source: sqlncli11.dll
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'vendor'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_concept_id'.
Message: ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'payersource'.
Type: System.Data.Odbc.OdbcException
12/14/2020 10:29:27 AM| Exception Found:

Query:
File name=ENROLLMENT_DETAIL
SourceConnectionString=
12/14/2020 10:29:27 AM| SourceEngine=org.ohdsi.cdm.framework.desktop.Databases.MssqlDatabaseEngine
Stacktrace: at org.ohdsi.cdm.presentation.builder.Base.DatabaseChunkBuilder.Process(IDatabaseEngine sourceEngine, String sourceSchemaName, List`1 sourceQueryDefinitions, OdbcConnection sourceConnection, String vendor) in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Base\DatabaseChunkBuilder.cs:line 48
Source: sqlncli11.dll
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'vendor'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_concept_id'.
Message: ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'payersource'.
Type: System.Data.Odbc.OdbcException
12/14/2020 10:29:26 AM| Exception Found:

Query:
File name=ENROLLMENT_DETAIL
SourceConnectionString=
12/14/2020 10:29:26 AM| SourceEngine=org.ohdsi.cdm.framework.desktop.Databases.MssqlDatabaseEngine
Stacktrace: at org.ohdsi.cdm.presentation.builder.Base.DatabaseChunkBuilder.Process(IDatabaseEngine sourceEngine, String sourceSchemaName, List`1 sourceQueryDefinitions, OdbcConnection sourceConnection, String vendor) in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Base\DatabaseChunkBuilder.cs:line 48
Source: sqlncli11.dll
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ethnicity_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_concept_id'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'race_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'vendor'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_source_value'.
ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'observation_concept_id'.
Message: ERROR [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'payersource'.
Type: System.Data.Odbc.OdbcException
12/14/2020 10:29:24 AM| Exception Found:

Query:
File name=ENROLLMENT_DETAIL
SourceConnectionString=
12/14/2020 10:29:24 AM| SourceEngine=org.ohdsi.cdm.framework.desktop.Databases.MssqlDatabaseEngine

"Incorrect syntax near GO" when using with SQL Server

Hi,
We are trying to use ETL-CDMBuilder to import a Truven database to CDMv5, using SQL Server 2016 as the backend

When running the Building Manager we get an error message Incorrect syntax near 'GO' in org.ohdsi.cdm.framework.data.DbLayer.DbSource.CreateIndexesChunkTable()

Looking at http://stackoverflow.com/a/25681013/6621470 it would appear that the GO statement should be removed from the various SQL files this code is calling, starting with CreateIndexesChunkTable.sql. Is that correct? Did you run into this problem before?

Best, Aurélien

Duplicate visit_cost_id in visit_cost table violating primary key constraint

The ETL appears to produce multiple duplicate records in the visit_cost table, violating the constraint that visit_cost_id is a unique primary key.

We first detected this after running the ETL under Windows, exporting the tables to text, importing them into a Linux PostgreSQL CDMv5 schema , then running the following constraint:

ALTER TABLE mdcr.visit_cost ADD CONSTRAINT xpk_visit_cost PRIMARY KEY ( visit_cost_id ) ;

We get the error:

psql:create_constraints.sql:158: ERROR:  could not create unique index "xpk_visit_cost"
DETAIL:  Key (visit_cost_id)=(147820) is duplicated.

Here is the duplicate data in visit_cost:

truven=> select * from visit_cost where visit_cost_id=147820;
 visit_cost_id | visit_occurrence_id | currency_concept_id | paid_copay | paid_coinsurance | paid_toward_deductible | paid_by_payer | paid_by_coordination_benefits | total_out_of_pocket |     total_paid     | payer_plan_period_id
---------------+---------------------+---------------------+------------+------------------+------------------------+---------------+-------------------------------+---------------------+--------------------+----------------------
        147820 |            11640952 |            44818668 |        0.0 |              0.0 |                    0.0 |      49914.18 |                           0.0 |                 0.0 | 50080.660000000003 |               367458
        147820 |            11640952 |            44818668 |        0.0 |              0.0 |                    0.0 |      49914.18 |                           0.0 |                 0.0 | 50080.660000000003 |               367458
(2 rows)

Note, some visit_cost records are duplicated as many as 25 times.

The ETL was interrupted under Windows after transforming the data but before creating indices and so forth that we don't need. Is there a step in the ETL that drops duplicate entries in visit_cost? All of the other tables had no problems with creating constraints.

Should it be safe to just drop all but one copy of the duplicate entries?

What we would give for a direct Linux PostgreSQL implementation of this ETL!

Thanks!
Christophe

System.OutOfMemoryException while creating Lookup creation

Builder: oedmt-vm
Time: Jan 8 2019 5:51PM
Error: Exception Found:
Type: System.OutOfMemoryException
Message: Exception of type 'System.OutOfMemoryException' was thrown.
Source: org.ohdsi.cdm.framework.core
Stacktrace: at org.ohdsi.cdm.framework.core.Lookups.MultiLookup.Add(IDataRecord reader, IDictionary2 mLookup) in C:\Users\Public\Documents\ETL-CDMBuilder-master\source\Framework\org.ohdsi.cdm.framework.core\Lookups\MultiLookup.cs:line 72 at org.ohdsi.cdm.framework.core.Lookups.MultiLookup.AddRecord(IDataReader reader) in C:\Users\Public\Documents\ETL-CDMBuilder-master\source\Framework\org.ohdsi.cdm.framework.core\Lookups\MultiLookup.cs:line 32 at org.ohdsi.cdm.framework.core.Lookups.BaseLookup.Load() in C:\Users\Public\Documents\ETL-CDMBuilder-master\source\Framework\org.ohdsi.cdm.framework.core\Lookups\BaseLookup.cs:line 61 at org.ohdsi.cdm.framework.core.Vocabulary.Load(String folder, IEnumerable1 definitions) in C:\Users\Public\Documents\ETL-CDMBuilder-master\source\Framework\org.ohdsi.cdm.framework.core\Vocabulary.cs:line 254
at org.ohdsi.cdm.framework.core.Vocabulary.Initialize() in C:\Users\Public\Documents\ETL-CDMBuilder-master\source\Framework\org.ohdsi.cdm.framework.core\Vocabulary.cs:line 156
at org.ohdsi.cdm.framework.core.Controllers.BuilderController.<>c__DisplayClass33_0.b__0() in C:\Users\Public\Documents\ETL-CDMBuilder-master\source\Framework\org.ohdsi.cdm.framework.core\Controllers\BuilderController.cs:line 248
at org.ohdsi.cdm.framework.core.Controllers.BuilderController.PerformAction(Action act) in C:\Users\Public\Documents\ETL-CDMBuilder-master\source\Framework\org.ohdsi.cdm.framework.core\Controllers\BuilderController.cs:line 79

Builder: oedmt-vm
Time: Jan 8 2019 2:31PM
Error: Exception Found:
Type: System.OutOfMemoryException
Message: Exception of type 'System.OutOfMemoryException' was thrown.
Source: mscorlib
Stacktrace: at System.Number.FormatInt32(Int32 value, String format, NumberFormatInfo info)
at System.Int32.ToString()
at org.ohdsi.cdm.framework.core.Lookups.MultiLookup.Add(IDataRecord reader, IDictionary2 mLookup) in C:\Users\vmadmin\Documents\ETL-CDMBuilder-master\source\Framework\org.ohdsi.cdm.framework.core\Lookups\MultiLookup.cs:line 38 at org.ohdsi.cdm.framework.core.Lookups.MultiLookup.AddRecord(IDataReader reader) in C:\Users\vmadmin\Documents\ETL-CDMBuilder-master\source\Framework\org.ohdsi.cdm.framework.core\Lookups\MultiLookup.cs:line 32 at org.ohdsi.cdm.framework.core.Lookups.BaseLookup.Load() in C:\Users\vmadmin\Documents\ETL-CDMBuilder-master\source\Framework\org.ohdsi.cdm.framework.core\Lookups\BaseLookup.cs:line 61 at org.ohdsi.cdm.framework.core.Vocabulary.Load(String folder, IEnumerable1 definitions) in C:\Users\vmadmin\Documents\ETL-CDMBuilder-master\source\Framework\org.ohdsi.cdm.framework.core\Vocabulary.cs:line 254
at org.ohdsi.cdm.framework.core.Vocabulary.Initialize() in C:\Users\vmadmin\Documents\ETL-CDMBuilder-master\source\Framework\org.ohdsi.cdm.framework.core\Vocabulary.cs:line 164
at org.ohdsi.cdm.framework.core.Controllers.BuilderController.<>c__DisplayClass33_0.b__0() in C:\Users\vmadmin\Documents\ETL-CDMBuilder-master\source\Framework\org.ohdsi.cdm.framework.core\Controllers\BuilderController.cs:line 248
at org.ohdsi.cdm.framework.core.Controllers.BuilderController.PerformAction(Action act) in C:\Users\vmadmin\Documents\ETL-CDMBuilder-master\source\Framework\org.ohdsi.cdm.framework.core\Controllers\BuilderController.cs:line 79

ETL-CDMBuilder INPATIENT_SERVICES table issue

The builder is expecting a column (dx5) that doesn’t seem to exists in Truven CCAE (2015) data.
https://github.com/OHDSI/ETL-CDMBuilder/blob/master/source/Builders/org.ohdsi.cdm.builders.truven_v5/TruvenV5/Definitions/INPATIENT_SERVICES.xml

Error from Source: Amazon Redshift ODBC Driver: Message: ERROR [42703] [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 42703] ERROR: column "dx5" does not exist in inpatient_services, ch, unnamed_join

The following columns are part of these data (taken from the Truven sample data):
SEQNUM VERSION DX1 DX2 DXVER PROC1 PROCTYP CASEID DISDATE DOBYR YEAR ADMDATE AGE CAP_SVC COB COINS COPAY DEDUCT DRG DX3 DX4 FACHDID FACPROF MHSACOVG MSCLMID NETPAY NPI NTWKPROV PAIDNTWK PAY PDDATE PDX PPROC PROCMOD PROVID QTY REVCODE SVCDATE SVCSCAT TSVCDAT UNITS ADMTYP MDC DSTATUS STDPLAC STDPROV EFAMID ENROLID PLANTYP REGION MSA DATATYP AGEGRP EECLASS EESTATU EGEOLOC EIDFLAG EMPREL ENRFLAG PHYFLAG RX SEX HLTHPLAN INDSTRY

Exception running Builder

I am running builder with the following settings:
Vendor: Cprd
Batches: 10
Batch size: 100
Parallelism: 2

I get this exception:
Exception Found:
Type: System.ArgumentNullException
Message: Value cannot be null.
Parameter name: format
Source: mscorlib
Stacktrace: at System.String.Format(String format, Object arg0)
at org.ohdsi.cdm.framework.data.DbLayer.DbSource.d__5.MoveNext() in c:\Users\john-c\Source\Repos\ETL-CDMBuilder\source\Framework\org.ohdsi.cdm.framework.data\DbLayer\DbSource.cs:line 58
at org.ohdsi.cdm.framework.core.Controllers.ChunkController.d__5.MoveNext() in c:\Users\john-c\Source\Repos\ETL-CDMBuilder\source\Framework\org.ohdsi.cdm.framework.core\Controllers\ChunkController.cs:line 76
at org.ohdsi.cdm.framework.core.Controllers.ChunkController.CreateChunks() in c:\Users\john-c\Source\Repos\ETL-CDMBuilder\source\Framework\org.ohdsi.cdm.framework.core\Controllers\ChunkController.cs:line 30
at org.ohdsi.cdm.framework.core.Controllers.BuilderController.PerformAction(Action act) in c:\Users\john-c\Source\Repos\ETL-CDMBuilder\source\Framework\org.ohdsi.cdm.framework.core\Controllers\BuilderController.cs:line 72

wrong operator_concept_id

In the document "man/OPTUM_EXTENDED/v5.3.0/ETL/measurement.md",

when '<' then operator_concept_id = 4172704
when '>' then operator_concept_id = 4171756

However, 4172704 is '>' and 4171756 is '<'.

Mapping to invalid concepts when valid concepts available in MarketScan ETLs

For example: currently the ICD-10 code T42.6X2D (Poisoning by other antiepileptic and sedative-hypnotic drugs, intentional self-harm, subsequent encounter) is currently mapped to concept ID 35216220, which is invalid. Instead, it should map to 45594639.

Perhaps relevant: the invalid concept has concept code 'T42.6x2D', whereas the valid concept has 'T42.6X2D', so a capital 'D'.

Update tag line

"ETL-CDMBuilder is a repo containing a .NET application to perform ETL to OMOP CDMv4 for multiple databases"

Update to:

"ETL-CDMBuilder is a repo containing a .NET application to perform ETL to OMOP CDMv5 for multiple databases"

Incompatible Data types error on CDM mapping

Hello,
Thanks for such a wonderfull solution.
I am encountering below error during lookup creation stage, precisly when providers are being created;

 at org.ohdsi.cdm.presentation.builder.Controllers.BuilderController.PerformAction(Action act) in D:\VSProjects\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Controllers\BuilderController.cs:line 55
  at org.ohdsi.cdm.presentation.builder.Controllers.BuilderController.<>c__DisplayClass16_0.<CreateLookup>b__0() in D:\VSProjects\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Controllers\BuilderController.cs:line 176
  at org.ohdsi.cdm.framework.desktop.Savers.Saver.SaveEntityLookup(CdmVersions cdmVersions, List`1 location, List`1 careSite, List`1 provider, List`1 cohortDefinition) in D:\VSProjects\ETL-CDMBuilder\source\org.ohdsi.cdm.framework.desktop\Savers\Saver.cs:line 553
Stacktrace:    at org.ohdsi.cdm.framework.desktop.Savers.PostgreSaver.Write(Nullable`1 chunkId, Nullable`1 subChunkId, IDataReader reader, String tableName) in D:\VSProjects\ETL-CDMBuilder\source\org.ohdsi.cdm.framework.desktop\Savers\PostgreSaver.cs:line 95
Source: org.ohdsi.cdm.framework.desktop
22P03: incorrect binary data format
Message: COPY public.PROVIDER (provider_id,provider_name,npi,dea,specialty_concept_id,care_site_id,year_of_birth,gender_concept_id,provider_source_value,specialty_source_value,specialty_source_concept_id,gender_source_value,gender_source_concept_id) from STDIN (FORMAT BINARY)
Type: System.Exception

The DB (postgresql) log points to the issue on the column care_site_id

2021-12-10 15:41:44.814 GMT [20592] LOG:  statement: COPY public.PROVIDER (provider_id,provider_name,npi,dea,specialty_concept_id,care_site_id,year_of_birth,gender_concept_id,provider_source_value,specialty_source_value,specialty_source_concept_id,gender_source_value,gender_source_concept_id) from STDIN (FORMAT BINARY)
2021-12-10 15:41:44.823 GMT [20592] ERROR:  incorrect binary data format
2021-12-10 15:41:44.823 GMT [20592] CONTEXT:  COPY provider, line 1, column care_site_id
2021-12-10 15:41:44.823 GMT [20592] STATEMENT:  COPY public.PROVIDER (provider_id,provider_name,npi,dea,specialty_concept_id,care_site_id,year_of_birth,gender_concept_id,provider_source_value,specialty_source_value,specialty_source_concept_id,gender_source_value,gender_source_concept_id) from STDIN (FORMAT BINARY)

Have tried to match the column types for the provider table with the populating query at org.ohdsi.cdm.framework.etl/org.ohdsi.cdm.framework.etl.cprd/ETL/CPRD/Definitions/L_PROVIDER.xml. Have changed the cast data type, for the column, to integer to no resolution.

Any assistance will be appreciated.

Conversion from RAW to CDM - Batch number not changing

We are running ETL CDM Builder to convert Truven CCAE data to CDM format. The CDM Builder has been running for 8 days, but today the fourth step 'Conversion from RAW to CDM' got stuck at batch number 256447. It has been running for more than 2.5 hours and batch number 256447 is still unfinished. Our batch size is 512 and it usually takes a few seconds to process one batch.
Can anyone please let me know how to fix this issue?

Mappings for device_cost and observation in Truven_CCAE_and_MDCR_ETL_CDM_V5.doc

Is there a newer version of the Truven mapping specs that has the details for the device_cost table, and an update to the observation table section? Device_cost is empty, and observation looks like a previous CDM version.

Thanks!

Invalid column name 'DXVER' and issue related to mscorlib

I downloaded the latest version of ETL-CDMBuilder and when I executed 'org.ohdsi.cdm.presentation.buildingmanager.exe', the first 3 steps of the building process 'CDM database creation', 'Chunks creation', and 'Lookup creation' ran successfully, but the fourth step 'Conversion from RAW to CDM' gave the following errors - 'One or more errors occurred. mscorlib' and 'Invalid column name 'DXVER'.

I had used the following builder settings:
Vendor: Truven MDCR v5
Number of batches: 1
Batch Size: 100
MaxDegreeOfParallelism: 1

Also, I checked all tables in Truven database and no table has the DXVER column. Could you please tell me what needs to be changed to fix these issues?

The following information is from Building Manager:

Builder: MUSE1
Time: Oct 12 2016 11:34AM
Error: Exception Found:
Type: System.AggregateException
Message: One or more errors occurred.
Source: mscorlib
Stacktrace:    at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
   at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
   at System.Threading.Tasks.Parallel.ForWorker[TLocal](Int32 fromInclusive, Int32 toExclusive, ParallelOptions parallelOptions, Action`1 body, Action`2 bodyWithState, Func`4 bodyWithLocal, Func`1 localInit, Action`1 localFinally)
   at System.Threading.Tasks.Parallel.ForEachWorker[TSource,TLocal](IEnumerable`1 source, ParallelOptions parallelOptions, Action`1 body, Action`2 bodyWithState, Action`3 bodyWithStateAndIndex, Func`4 bodyWithStateAndLocal, Func`5 bodyWithEverything, Func`1 localInit, Action`1 localFinally)
   at System.Threading.Tasks.Parallel.ForEach[TSource](IEnumerable`1 source, Action`1 body)
   at org.ohdsi.cdm.framework.core.Base.ChunkBuilder.Load()
   at org.ohdsi.cdm.presentation.builderprocess.Program.Build(BuilderController builderController)
  Inner Exception Found:
  Type: System.Data.SqlClient.SqlException
  Message: Invalid column name 'DXVER'.
  Source: .Net SqlClient Data Provider
  Stacktrace:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at org.ohdsi.cdm.framework.core.Base.ChunkBuilder.<>c__DisplayClass9.<Load>b__4(QueryDefinition queryDefinition)
   at System.Threading.Tasks.Parallel.<>c__DisplayClass17_0`1.<ForWorker>b__1()
   at System.Threading.Tasks.Task.InnerInvokeWithArg(Task childTask)
   at System.Threading.Tasks.Task.<>c__DisplayClass176_0.<ExecuteSelfReplicating>b__0(Object )

Thanks,
Praveen

Connecting to mssql server DB does not work as before

With the previous version (before December, Friday 03/2020) my mssql server db settings were working well in the sense that the builder always saw the databases. Now, I got the next error:

   at org.ohdsi.cdm.presentation.builder.Controllers.BuilderController.PerformAction(Action act) in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Controllers\BuilderController.cs:line 55
   at org.ohdsi.cdm.presentation.builder.Controllers.BuilderController.<>c.<CreateDestination>b__9_0() in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Controllers\BuilderController.cs:line 75
   at org.ohdsi.cdm.framework.desktop.DbLayer.DbDestination.CreateDatabase(String query) in E:\repo\ohdsi\ETL-CDMBuilder\source\org.ohdsi.cdm.framework.desktop\DbLayer\DbDestination.cs:line 42
   at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
Stacktrace:    at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
Source: sqlncli11.dll
Message: ERROR [42000] [Microsoft][SQL Server Native Client 11.0]Syntax error, permission violation, or other nonspecific error
Type: System.Data.Odbc.OdbcException
12/4/2020 11:44:33 AM| Exception Found:

The latest version ask for a schema but it was not needed in previous versions. I also entered my DB settings as:

<add name="SourceConnectionStringTemplate" connectionString="Driver={SQL Server Native Client 11.0};Server={localhost};Database={Truven_mdcr};Uid={sa};Pwd={somepwd};" />
<add name="DestinationConnectionStringTemplate" connectionString="Driver={SQL Server Native Client 11.0};Server={localhost};Database={JoseTruvenmdcrDest};Uid={sa};Pwd={somepwd};" />
<add name="VocabularyConnectionStringTemplate" connectionString="Driver={SQL Server Native Client 11.0};Server={localhost};Database={Vocab};Uid={sa};Pwd={somepwd};" />

My vendor setting is:

<add key="Vendor" value="MDCR" />

<add key="PersonBuilder" value="org.ohdsi.cdm.framework.etl.ibm.IbmPersonBuilder, org.ohdsi.cdm.framework.etl.ibm, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
`

and the error still continued. Also, the interface does not offer a drop down list for vendors anymore and the error log refers to a E: disk drive unit and I only have a C: drive,

Thanks

CDM vocabulary - loinc answer code is not available

Currently, I am working on load MDS3.0 items into DB schema in CDM Version 4. As i know, all questions in MDS3 are representing by loinc code in table concept. (ex. for question A0310A "Type of assessment: OBRA", we have loinc code "54583-0" and omopid "40757714" )

However, there is no concept available for those answer code in standard vocabulary. (ex. for answer "Admission assessment (required byday 14)", we have code " LA10-4" in MDS3.0 item mappings but it's not in CDM vocabulary).

Should we load loinc answer code into standard vocabulary? I will be great appreciate if anyone can help me on refers to loinc answer code based on CDM vocabularies.

ICD10PCS Fix

Per this thread:
http://forums.ohdsi.org/t/icd10pcs-procedure-codes-from-inpatient-admissions-and-facility-headers-are-being-dropped/2342/10

Also HIX-1255 Janssen Side

any plans to support v6?

also, the Cerner HealthFacts ETL targets only 5.1

Working version

The latest commit doesn't seem to work for Truven source data due to conflicts in the code and the database that is created by the various scripts, e.g., the SubChunks table isn't there but the code expects it. The code related to Achilles also causes exceptions. I'm using SqlServer as the database for all schemas, e.g., Builder, Vocabulary, CDM, etc. Is the current commit or any earlier one something that can be built and run from source code? I've tried the Oct 17 version and it doesn't work in similar ways.

CDM Builder DDL needs review for Payer Plan Period / Achilles analysis 1424

###Expected behavior
DDL is compliant with current Achilles version and analysis id 1425
###Actual behavior
DDL from .net ETL Builder seems to vary from schema that Achilles expects when processing analysis 1425 / table payer_play_period aggregation
###Steps to reproduce behavior
Run Achilles analysis 1425 using Achilles/Master

###Notes
Please see ticket open on Achilles Repo
@alondhe @t-abdul-basser

Builder stuck at Chunks creation

I am running ETL-CDMBuilder on Windows Server 2016 to convert Truven MDCR data to CDM format.
I managed to create the builder database and the first step 'CDM database creation' but I am stuck at the Chunks creation. I don't have any timelines indication and Errors is also empty so I can't figure out what's going on.

When I look in the table 'Building' from the builder database I only have this:

Does anyone have an idea to solve this issue ?