Recurring Data Generation using Command Line - Change seed values?

JustinStewart

I've created a project and am running that project throught the command line.  I need to generate different data on a weekly basis.  Is there a way to change all the seed values through the command line?  It would be helpful to be able to change the number of rows to output for each table through the command line as well.
  

gerald_leach

I was unable to find a way to programmatically increment the seed at runtime. My solution to the problem was a bit awkward but it works... I wrote a quick powershell script to increment every Seed in the .sqlgen file ahead of having Sql Data Generator process it via command line...

param( 
    [Parameter(Mandatory=$true)][string]$filePath
)

$xml = [xml](Get-Content $filePath)

foreach ( $SDGTable in $xml.Project.Tables.ChildNodes ) {
    foreach ( $SDGField in $SDGTable.Fields.ChildNodes ) {
        foreach ( $element in $SDGField.Generator.GeneratorProperties.ChildNodes ) {
            if ( $element.ChildNodes[0].ChildNodes[0].Value -eq "Seed" ) {
                $element.ChildNodes[1].ChildNodes[0].Value = [int]$element.ChildNodes[1].ChildNodes[0].Value + 1
            }
        }
    }
}
$xml.Save($filePath)

JustinStewart

Great solution, works perfectly, thanks!

Jon_Kirkwood

Great idea @gerald_leach, @JustinStewart

I can confer that there isn't a way within SQL Data Generator to do this and it would need to be done externally.

Working on a similar concept I created a python script that manipulated the SQLGEN XML file. A rough script below that randomizes the amount of rows generated & the seed value. 

import xml.etree.ElementTree as ET

from random import randint

filename = 'c:/temp/seedgen.sqlgen' #Replace with your SQLGEN filename

# Open original file and parse the XML tree

project = ET.parse(filename)

proj_root = project.getroot()

# Change Seed Value to random value between 0 & 9999

update = False

rand_seed = randint(0,9999)

for element in proj_root.iter("element"):

    for key in element.iter('key'):

        if key.text == 'Seed':

            update = True

    if update == True:

        for seed in element.iter('value'):

            seed.text = str(rand_seed)

    # Reset the update flag

    update = False        

                

# Change # of rows to generate between 1000 & 2000 records

rand_row = randint(1000,2000)

for row in proj_root.iter('RowCount'):

        row.text = str(rand_row)

project.write(filename)

I then combined this script with the SQLDataGen command line in a PowerShell script, this runs my seed randomiser and then runs the Data Gen project.

# Python randomizer

cd "c:\temp\"

python ".\seedrandom.py"

# Project file for SQL Data Gen

$project = "c:\temp\seedgen.sqlgen"

cd "C:\Program Files (x86)\Red Gate\SQL Data Generator 4"

Start-Process `"SQLDataGenerator.exe`" `"/project:$project`"

This is a quick proof of concept but may assist with your requirements. Code can be certainly refactored and tailored for your needs but does show it's possible to have Data Gen create randomized data.