SqlTransformCommandText

<SqlTransformCommandText></SqlTransformCommandText> is used to write SQL querries that are executed during data import. For use, it is more than advisable to have SQL Management Studio or similar software installed in order to debug the querry. Basically, it works by taking the data from the input, loading it into the beforeTashaIOSqlEditTable in the Tasha database, executing the querry and writing it to the afterTashaIOSqlEditTable, where it then proceeds through the scenarioimporter.

SqlTransformCommandText runs after the connection to the distribution database, GlobalCharReplace, GlobalReplace and ColumnsRename, but before ColumnsHeader, ColumnsReplace etc.

Usage

Let's say the import file (for example .xlsx) has the following structure:

Job	CostumerName	Street	PostalCode	City	Country	Telephone	WeightKG	CashOnDelivery
Job123	Customer1	Veselá 52	75651	Zašová	CZ	+420603123456	40,00	42100
Job456	Customer2	Náměstí 5/3	75701	Valašské Meziříčí	CZ	+420603987654	29	10352

If we want to perform a SQL querry during import, it is necessary to start with the following:

<SqlTransformCommandText><![CDATA[

 SELECT *
 INTO [afterTashaIOSqlEditTable] --table with output data
 FROM [beforeTashaIOSqlEditTable] --table with input data]]>

</SqlTransformCommandText>

Two tables are created in the Tasha database, beforeTashaIOSqlEditTable and afterTashaIOSqlEditTable. If we dump the data from the beforeTashaIOSqlEditTable, we can see what goes into Tasha.

columns are renamed in SQL to col0_Command, col1_CommandName... The prefix "colX_" is removed by Tasha and in principle it is not necessary to notice it.

When we know the structure of the input data, it is possible to modify the querries (similarly as when retrieving data from a SQL database.

<SqlTransformCommandText> <![CDATA[

 SELECT
 [col0_Zakazka] AS id
 ,[col1_NazevZakaznika] AS name
 ,CONCAT([col2_Ulice],', ',[col3_PSC],' ',[col4_Mesto]) as address
 ,0 AS x
 ,0 AS y
 ,[col6_Telefon] AS mobile
 ,CASE WHEN [col7_HmotnostKG] IS NULL THEN 0 ELSE [col7_HmotnostKG] END AS demand1
 ,5 as duration
 ,'0600' as start1
 ,'1800' as end1
 ,2 as precedence
 ,'delivery' as stoptype
 ,[col5_Zeme]
 ,[col8_Dobirka]
 , specialcodes
 INTO [afterTashaIOSqlEditTable] --table with output data
 FROM [beforeTashaIOSqlEditTable] --table with input data

]]> </SqlTransformCommandText> You can then work in the scenarioimporter in the standard way:

<ColumnsCalculated>

 <string>dynamic;dobirka;dynamic;dobirka;;dynamicdobirka==?0:dynamicdobirka</string> 
 <string>job;specialcodes;job;specialcodes;dynamic;dobirka;;dynamicdobirka>0?hotovost:jobspecialcodes</string>

</ColumnsCalculated>

SqlTransformCommandText supports the usual SQL commands, but it cannot, for example, read data into temporary tables.