Recurring Data Generation using Command Line - Change seed values?

• 
  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)

  gerald_leach September 15, 2022 17:48

  - 0 +
• 
  Great solution, works perfectly, thanks!

  JustinStewart September 15, 2022 18:00

  - 0 +
• 
  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 September 16, 2022 01:27

  - 0 +

Add comment

Please sign in to leave a comment.