0

I have a sql procedure saved into the object variable ("Procdetail") now with the below code I managed to get into the variable(procdetailoutput), Can someone please complete the below to also save it to the local file

public void Main() {

  var table = ((DataSet)Dts.Variables["procdetail"].Value).Tables[0];
  string procdetailoutput = "";

  foreach (DataRow row in table.Rows)
  {
      procdetailoutput += row[0];
  }

  Dts.Variables["procdetailoutput"].Value = procdetailoutput;
  Dts.TaskResult = (int)ScriptResults.Success;
}
Jorge E. Hernández
  • 2,800
  • 1
  • 26
  • 50
bay
  • 1
  • 1
  • You want C# script to save a variable to a file? Does this help? http://stackoverflow.com/questions/15278839/how-do-i-save-variables-to-a-new-text-file-so-that-those-variables-are-loaded-th – Nick.Mc Jul 28 '16 at 01:19
  • Thanks Nick, its working now, managed to write to the file, that's the ultimate required thing, but while writing to file do you know why its creating empty line for each write, its supposed to be 10000 lines but with this empty line the file have 20000 lines, Can you please help me this, below is the code – bay Jul 28 '16 at 17:57
  • public void Main() { var table = ((DataSet)Dts.Variables["procdetail"].Value).Tables[0]; string procdetailoutput = ""; string FileWithPath = "C:\\StoredProcs_" + DateTime.Now.ToFileTime() + ".txt"; StreamWriter sw; sw = File.CreateText(FileWithPath); foreach (DataRow row in table.Rows) { procdetailoutput += row[0]; // write lines of text to the file sw.WriteLine(row[0]); } // close the stream sw.Close(); Dts.Variables["procdetailoutput"].Value = procdetailoutput; Dts.TaskResult = (int)ScriptResults.Success; } – bay Jul 28 '16 at 18:00
  • I guess that `WriteLine` adds a carriage return automatically, but `row[0]` actually already has one. Use a method that doesn't. There probably one just called `Write` or something. What are you actually trying to do? You know you can export database objects from SMSS right? In fact there is probably a method that writes a datatable straight to a file without iterating over the lines – Nick.Mc Jul 29 '16 at 01:06
  • If you are populating your `procdetail` variable from a query..... why don't you just use a data flow then you don't need any script code at all. – Nick.Mc Jul 29 '16 at 01:11
  • I am trying to script some stored procedures to a text file using ssis (so as to automate using dtexec), yes we can generate script from SSMS and cannot automate due to limited access to DB servers, and again how to use that dataflow? I am a db guy. – bay Jul 29 '16 at 21:04
  • This has a multitude of options: http://stackoverflow.com/questions/483568/how-can-i-automate-the-generate-scripts-task-in-sql-server-management-studio-2. Are you populating your `procdetail` variable from a query or not? If you are, I suggest you drop a dataflow in your package, use that query in a datasource and write it to a text file target. – Nick.Mc Jul 30 '16 at 00:17

0 Answers0