Control deployment of static data

Control deployment of static data

• Angelo_f

  Hi Jamie

  Unfortunitely in V1 we are fully supporting stock data and migrating those as part of your deployment process. We certainly don;t block you from doing in the way the Nicole described about in regards to post deployment scripts.

  We do have it on our suggestion list for future releases

  mairead

  
  

• Tdotsoulja

  I can't believe this isn't part of V1. I mean, you guys absolutely crippled "regular" DB projects in 2005. They are completely useless compared to what you could do in VS.NET/2003. Now you're not even going to support this scenario in V1 of a brand new product that you're going to charge a ton of money for as a separate SKU Prettttttttyyyyy crazy.

  I'll go vote, but the fact that you're already saying "future releases" means that you guys have already ruled it out and are leaving people who have counted on this functionality stranded. Not cool, not cool at all.

  Unsatisfied customer,
  Drew

  
  

• Fiksman Evgeny

  If you did want to deploy stock data from another DB as part of your deployment, you could look at using the Schema-bound generator in data generator. You could configure this generator to retrieve information (via query) from your other DB. When we run the generation plan we will attempt to populate the tables you have selected in the correct order according to the referential integrity constraints. The feasibility of this approach does depend on how many tables you want to populate in this manor.

  Data generation is also available as a MSBuild task, so, you could build a call to this MSBuild task into your deployment script.

  
  
  

• Dottj

  Mairead wrote:

  Hi Jamie Unfortunitely in V1 we are fully supporting stock data and migrating those as part of your deployment process. We certainly don;t block you from doing in the way the Nicole described about in regards to post deployment scripts. We do have it on our suggestion list for future releases mairead

  Mairead,

  I understand that completely. My suggestion that has been submitted to Microsoft Connect should, I hope, be considered for V2.

  Thanks for the reply!

  -Jamie

  
  
  

• Spigot

  Cool thanks for that. We really do appreciate your feedback
  

• Nuno_Salvado

  Hi Jamie,

  I'm not sure I can give a full answer, but in light of our other thread, I'll try. :)

  In VSTS4DB you build your project and then deploy it. I think if you just click deploy, and the project is not built, then the project is built first for you automatically. In any event, when you build your project, a deployment script is generated that is just a .sql file. When you deploy your project, the .sql file is run againt the target database. Before you deploy, you can open up the .sql deployment script and look at it. This way you can see what order things are going to be deployed in, and how the keys are going to be deployed. You can also edit the script, but if you edit the script you have to deploy it manually instead of clicking deploy. The deployment script is located at:

  YourPath \ Your Project \ Sql \ file.sql

  For the script insertion question - there are two places that you can do this. I'm not sure if either of these will be exactly what you want but they are:

  • You can specify scripts to run before and after deployment. Please see the help topic "How to: Specify a Pre-build or Post-build Command Line"
  • You can specify data generation plans as pre- and post- parts of unit tests, and I think you might be able to use this to do what you want. Please see the help topic "Walkthrough: Creating and Running a Database Unit Test"

  Thank you,

  Nicole Johanson

  
  
  

• Tomasz24311

  tjanuario wrote:

  I agree with Jamie's request. One of the things that was available in VS2003 Enterprise was the ability to create dat files. I have a home grown app which creates the tables from scripts, and then inserts the data from the DAT files into the tables before running the scripts for the fkeys. This gets around the ordering problem. This is a real problem because most database have lookup tables that random data can't replace for true functionality testing (not just unit testing - but actual testers going in and looking at data, changing data and then seeing the expected results).

  YES!

  Thank you tjanuario. I'm glad its not just me

  Could you do us both a favour and click through to my Microsoft Connect posting (link above) and add your two cents worth. We're more likely to get it if more people ask for it. And voting doesn't really help much either - customer requests do. So add some words.

  Thanks

  Jamie

  
  
  

• dlcollison

  Nicole Johanson MSFT wrote:

  Hi Jamie, One more question for clarification: The data insertion scripts, do you already have those prepared as your own custom scripts, or do you want to use VSTS data generation scripts Thank you, Nicole Johanson

  Hi Nicole,

  Great question. I want to use my own scripts. Data generation scripts are in no good in the context of what I am thinking about because they only generate random data. I want to deploy my own static reference-type data.

  And by sheer coincidence I posted this suggestion: https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx FeedbackID=207872 at Microsoft Connect less than 5 minutes ago which talks about exactly this sort of thing.

  Happy to answer more questions about this if you like. i think this is a bit of a gap in the product to be honest.

  -Jamie

  
  
  

• Kris Nye

  Hi Jamie,

  One more question for clarification: The data insertion scripts, do you already have those prepared as your own custom scripts, or do you want to use VSTS data generation scripts

  Thank you,

  Nicole Johanson

  
  
  

• leahcimp

  Folks

  We hear you loud and clear and yes adding more votes in Connect increases the changes of us acting upon this request in future releases:-)

  mairead

  
  

• Jonathan Allen

  J. D. Laflen wrote:

  If you did want to deploy stock data from another DB as part of your deployment, you could look at using the Schema-bound generator in data generator. You could configure this generator to retrieve information (via query) from your other DB. When we run the generation plan we will attempt to populate the tables you have selected in the correct order according to the referential integrity constraints. The feasibility of this approach does depend on how many tables you want to populate in this manor. Data generation is also available as a MSBuild task, so, you could build a call to this MSBuild task into your deployment script.

  Interesting. I'll check that out later today and see if it fits my requirement.

  Note, what I don't want to do is have my deployment process look at another DB in order to get its data at deployment time. I just want static scripts. I guess this thread has evolved into me asking "Can VSTS4DBP generate the insertion scripts for me " and *I think* the answer is no. Hence my Microsoft Connect submission.

  Thanks for the reply JD.

  -Jamie

  
  
  

• Milzit

  I agree with Jamie's request. One of the things that was available in VS2003 Enterprise was the ability to create dat files. I have a home grown app which creates the tables from scripts, and then inserts the data from the DAT files into the tables before running the scripts for the fkeys. This gets around the ordering problem. This is a real problem because most database have lookup tables that random data can't replace for true functionality testing (not just unit testing - but actual testers going in and looking at data, changing data and then seeing the expected results).

  
  

• Ogulcank

  Thanks Nicole. That's useful but it doesn't quite answer my question.

  I want VSTS4DB to deploy my database schema AND some data in it.Schema gets deployed first, then data. If the data isn't deployed in the correct order then something could fail due to FK violations.

  Is there any way that VSTS4DBP can automatically deploy data insertion scripts in the correct order or is it a manual process to ensure that data is deployed in the correct order

  I expect the latter to be the answer. if that is the case I have a bona fide request for V2.

  -Jamie

  
  
  
• Control deployment of static data