Tipps, Tricks And Fun

In the topic Tipps, Tricks And Fun all questions about other stuff, not related to the ExMeX Framework but interesting in conjunction with, are covered. Hopefully some of them are useful in your daily work.

How to Copy & Paste Load Process Step from SSMS?

Issue The character length of 65k characters for the Data Vault load scripts to be generated is too small. Test…

Issue

The character length of 65k characters for the Data Vault load scripts to be generated is too small.

Test

A test with a satellite with more than 400 columns, where the columns are 42 characters long on average, showed that the previous length of 65k characters is sufficient to generate the scripts.

Cause

The problem is not the generation itself, but that the SSMS "only" represents 65k characters per cell in the result set and cuts off the rest (see picture).

Solution

After loading the metadata, all generated Load Process Steps are stored in a database object: MetadataZone.BatchExecuteLoadProcessStep. These Load Process Steps can be extracted from the table using a command line. Execute the following command in the CMD or PowerShell:

bcp "SELECT * FROM [TEDAMOH-ExMeX-Develop].MetadataZone.BatchExecuteLoadProcessStep" QUERYOUT "C:\output.txt" -T -c -S .\SQLEXPRESS

More information about the bcp Utility in Microsoft Docs.

This writes the entire contents of the table to the output.txt file. Et voila, there is the complete statement.

Pitfall getting generated SQL for LPS out of table with SSMSPitfall getting generated SQL for LPS out of table with SSMS.

Posted 2 years agoby Dirk.Lerner