using xml config to change connection

marchello

 Hi all, 
I'm trying to use xml config to change connection. 

To make it clear, we are using 3 types of connections: 
1) target table
2) sql source for single column (Generic - SQL Statement menu)
3) source of data (existing data source - SQL Table or View). 

So, I tried to change (1) using xml config and it was successful. 
I tried to change also (2) and it was not successful. 
My co-worker tried both (2) and (3) and it was not successful. Just to safe time, let's focus on (2) as it was confirmed by 2 people at least. 

The xml config looks like below: 

<?xml version="1.0"?>
<commandline>
<server>server1</server>
<project>project1.sqlgen</project>
<out>out1.log</out>
</commandline>

What I did to make sure my test results are ok? I created the same database name, source and target tables names and structure, but populated source table in server1 and server2 with different data. 

Please see my sqlgen file below:

<?xml version="1.0" encoding="utf-16" standalone="yes"?>

<!--

SQL Data Generator 4

SQL Data Generator

Version:4.2.3.1975-->

<Project version="2" type="SDGProject">

  <DataSource version="4" type="LiveDatabaseSource">

    <ServerName>server1</ServerName>

    <DatabaseName>database1</DatabaseName>

    <Username />

    <SavePassword>False</SavePassword>

    <Password />

    <ScriptFolderLocation />

    <MigrationsFolderLocation />

    <AuthenticationType>WindowsIntegrated</AuthenticationType>

  </DataSource>

  <Tables type="List&lt;SDGTable&gt;" version="1">

    <value version="3" type="SDGTable">

      <TableType>Generated</TableType>

      <PopulationDetails version="2" type="PopulationDetails">

        <PopulationType>RowCount</PopulationType>

        <RowCount>1000</RowCount>

        <ProportionTableExists>False</ProportionTableExists>

        <Proportion>0</Proportion>

        <TimeToPopulate>0</TimeToPopulate>

      </PopulationDetails>

      <InvalidRowBehaviour>SkipRow</InvalidRowBehaviour>

      <Included>True</Included>

      <Append>False</Append>

      <Name>test1destination</Name>

      <Schema>sandbox</Schema>

      <IsTemporal>False</IsTemporal>

      <IdentityInsert>True</IdentityInsert>

      <Fields type="List&lt;SDGField&gt;" version="1">

        <value version="1" type="SDGField">

          <Name>t1</Name>

          <FieldType>Generated</FieldType>

          <PrimaryKey>False</PrimaryKey>

          <ForeignKey>False</ForeignKey>

          <Unique>False</Unique>

          <AllowsNulls>False</AllowsNulls>

          <SqlType>Integer32</SqlType>

          <Precision>10</Precision>

          <Scale>0</Scale>

          <DefinedSize>4</DefinedSize>

          <Generator version="1" type="GeneratorDetails">

            <GeneratorTypeName>RedGate.SQLDataGenerator.Generators.SQL.SQLColumnGenerator</GeneratorTypeName>

            <DisplayName>SQL Statement</DisplayName>

            <Description>Imports data using a SELECT statement</Description>

            <CategoryName>Generic</CategoryName>

            <GeneratorProperties type="Dictionary&lt;string, object&gt;" version="1">

              <element>

                <key type="string">LiveDatabaseSource</key>

                <value version="4" type="LiveDatabaseSource">

                  <ServerName>server1</ServerName>

                  <DatabaseName>database1</DatabaseName>

                  <Username />

                  <SavePassword>False</SavePassword>

                  <Password />

                  <ScriptFolderLocation />

                  <MigrationsFolderLocation />

                  <AuthenticationType>WindowsIntegrated</AuthenticationType>

                </value>

              </element>

              <element>

                <key type="string">SQLQuery</key>

                <value type="string">select t1 from sandbox.test1</value>

              </element>

              <element>

                <key type="string">ShuffleData</key>

                <value type="string">True</value>

              </element>

              <element>

                <key type="string">ShuffleRows</key>

                <value type="string">1000</value>

              </element>

              <element>

                <key type="string">LoopQuery</key>

                <value type="string">True</value>

              </element>

              <element>

                <key type="string">NullsAllowed</key>

                <value type="string">False</value>

              </element>

              <element>

                <key type="string">NullProportion</key>

                <value type="string">0.01</value>

              </element>

              <element>

                <key type="string">Seed</key>

                <value type="string">58368</value>

              </element>

            </GeneratorProperties>

          </Generator>

        </value>

      </Fields>

      <ForeignKeyProperties type="List&lt;ForeignKeyProperty&gt;" version="1" />

    </value>

  </Tables>

  <ProjectOptions version="2" type="ProjectOptions">

    <BatchSize>1000</BatchSize>

    <FireInsertTriggers>True</FireInsertTriggers>

    <FireDeleteTriggers>False</FireDeleteTriggers>

    <CheckConstraints>True</CheckConstraints>

    <ShuffleBlockSize>1000</ShuffleBlockSize>

    <AutoGeneratePreviews>True</AutoGeneratePreviews>

  </ProjectOptions>

  <PreScripts type="SQLScripts" version="1" />

  <PostScripts type="SQLScripts" version="1" />

  <IsRefresh>False</IsRefresh>

</Project>

So, when I change 'server1' to 'server2' in the xml config, it successfully populates specified table 'test1destination' in 'server2', but it uses source table 'sandbox.test1' from previously set 'server1' according to sqlgen (ignoring xml config). 

Should I specify more parameters in the xml config so that (2) connection is also affected? 
Please also advise about (3) connection, though I did not test it personally yet.

Please advise.

marchello

[deleted]

Jessica R

Hi @marchello!

I just want to first note that editing the config file is not officially supported.

That said, changing the ServerName property in the xml does seem to work alright from a quick test.

        <GeneratorProperties type="Dictionary&lt;string, object&gt;" version="1">

              <element>

                <key type="string">LiveDatabaseSource</key>

                <value version="4" type="LiveDatabaseSource">

                  <ServerName>server1</ServerName>

                  <DatabaseName>database1</DatabaseName>


When I re-open the project in SQL Data Generator, I can see that it is pulling values from the updated server name and it then uses that server for the data generation as well.

Can I first check: when you re-open the edited project and look at the preview for t1, does it show values from server1 still from there as well? Or is it only when you generate the data?

Jessica R

It does work for 3) as well although I had to make sure to change the ServerName property (I originally only changed the description which did not work).

   <Generator version="1" type="GeneratorDetails">

            <GeneratorTypeName>RedGate.SQLDataGenerator.Generators.DataBound.DataPassthroughGenerator</GeneratorTypeName>

            <DisplayName>[Name]</DisplayName>

            <Description>[(local)\SQLexpress].[SmartAssembly].[dbo].[Names].[Name]</Description>

            <CategoryName>DataPassThrough</CategoryName>

            <GeneratorProperties type="Dictionary&lt;string, object&gt;" version="1">

              <element>

                <key type="string">ColumnName</key>

                <value type="string">Name</value>

              </element>

              <element>

                <key type="string">Unique</key>

                <value type="string">True</value>

              </element>

            </GeneratorProperties>

          </Generator>

        </value>

      </Fields>

      <ForeignKeyProperties type="List&lt;ForeignKeyProperty&gt;" version="1" />

      <TableDataSourceExists>True</TableDataSourceExists>

      <TableDataSource version="1" type="SQLTableDataSource">

        <Schema>dbo</Schema>

        <Table>Names</Table>

        <DataSource version="4" type="LiveDatabaseSource">

          <ServerName>(local)\SQL2014</ServerName>

          <DatabaseName>SmartAssembly</DatabaseName>

          <Username />

          <SavePassword>False</SavePassword>

          <Password />

          <ScriptFolderLocation />

          <MigrationsFolderLocation />

          <AuthenticationType>WindowsIntegrated</AuthenticationType>

        </DataSource>

      </TableDataSource>