Recurring Data Generation using Command Line - Change seed values?

JustinStewartJustinStewart Posts: 2 New member
edited September 13, 2022 7:59PM in SQL Data Generator
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.
  
Tagged:

Best Answer

  • gerald_leachgerald_leach Posts: 1 Bronze 1
    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)

Answers

  • Great solution, works perfectly, thanks!
  • Jon_KirkwoodJon_Kirkwood Brisbane, Australia Posts: 132 Silver 1
    edited September 16, 2022 1:30AM
    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.
    Jon Kirkwood | Technical Support Engineer | Redgate Software
Sign In or Register to comment.