0


I am doing a project to generate data extracts on a daily basis. I have ten different queries with different columns and also the number of columns are also different. the database is MSSQL server 2008 R2 and I tried SSIS packet to accomplish the result.I used the components datasource, then a sort and the result of the sort to merge and then to text file. But I am getting error when combining the result saying the columns are different or something. Can anyone suggest a solution or is there any other way to accomplish this.
thanks,
Sivajith

Sivajith
  • 1,181
  • 5
  • 19
  • 38

3 Answers3

0

Here are the following reference that may help you a bit more: SQL Server : export query as a .txt file

You will have to make sure you have a proper connection to the SQL server and then run this as a powershell or a .bat file. This can be scheduled to run daily as well.

Community
  • 1
  • 1
  • This should not be an answer. It should be a comment to the original question. – SouravA Jul 23 '15 at 09:18
  • I am sorry if the question is confusing . First of all the I cannot set empty columns for each script to make same number of columns because there is specific column numbers for each result set. Why I need a single file - the answer is simple the client requirement is like that and they won't change it. – Sivajith Jul 23 '15 at 14:52
  • Have you taken a look at this: http://stackoverflow.com/questions/6354130/is-there-a-select-into-outfile-equivalent-in-sql-server-management-studio –  Jul 23 '15 at 17:56
0

Can you please provide error message? The merge component can merge data flows with various amount of columns, by selecting for the input columns.

cqi
  • 539
  • 3
  • 13
0
  1. First create a template .csv file which contain all the columns from the queries (i.e. if you have the columns A B C in the first query, B, E, F in the 2nd , B , X, Y in the third and so on, make sure your template file will have A B C E F X Y)
  2. Make 10 tasks (one for each query). As a source use sql from command and write your query. As a destination, use the template file created above. Make sure you uncheck "Overwrite data".
  3. Use the same destination for all the queries. This should do the trick. I am not sure that I completely understood your question since it's a little big vague.
V2k
  • 344
  • 2
  • 4
  • 14