0

I have a Test database which is overwritten each week by a fresh new production copy. But we have changes in our Test environment which I script in manually each Monday morning after the copy is created. Is there a way to schedule script code to run which can generate my objects and data changes eg new stored procedures etc.

The Job scheduler in SQL Server can import a SQL script, but it's not dynamic I need something that I can use in future where it will read in the script each time before it's run and pick up any changes.

ErickTreetops
  • 3,189
  • 4
  • 27
  • 37

1 Answers1

1

I suggest you create a SSIS package and use SMO inside a script component to generate DDL.

This link may help you a little bit.

Using SMO is very easy and straight forward

SMO tutorial

FLICKER
  • 6,439
  • 4
  • 45
  • 75
  • I think you're onto something there. I will use system,IO to read in any script i have sitting on a dedicated network folder. Just schedule the job to run weekly – ErickTreetops Mar 18 '16 at 00:05
  • I don't get what you mean? – FLICKER Mar 18 '16 at 00:06
  • i want to create stored procedures automatically from a script. the script will change from week to week. As long as i place the script in the network folder the ssis package should read it and use the SMO to create the stored procs. – ErickTreetops Mar 18 '16 at 00:32
  • I'm not saying you create any script. SSIS generates scripts of your stored procedure every Monday before cloning your database. To generate the scripts in SSIS, you use a script component in your SSIS. In script component you need to use SMO to easily generate script for any object in database. If this is not clear, please comment and ask specific question – FLICKER Mar 18 '16 at 02:28
  • Yep ! got it working using script component and sql file. – ErickTreetops Mar 21 '16 at 06:45