0

I want to generate a csv report with the following format in SSIS -

Some Static Text - 1

Data from SQL

Some Static Text - 2

Data From SQL

I was thinking to create a report with these static texts in my report and generate the SQL data at runtime. But i can not fix the position of the Some Static text - 2 as the records from SQL can be 'n' in numbers.

I am newbie to SSIS, any help is much appreciated

Abhijith Nayak
  • 448
  • 7
  • 21
  • SSIS isn't a report generator, it's an ETL tool. The reporting service is SSRS. While you *could* use 2 or more dataflow tasks to append to the same file, the result will be cumbersome and ugly – Panagiotis Kanavos Feb 12 '16 at 17:05
  • Do each step in a separate SSIS task, in sequence. You can keep appending to the same csv file. – Tab Alleman Feb 12 '16 at 19:22

1 Answers1

1

You need SSRS. You can build reports for it in Visual Studio. SSIS is an ETL tool (Extract, transform, load) which is primarily used to aggregate data, migrate data, consolidate data, etc. SSRS is a tool that allows end-users to consume dynamic reports that you provide.

EDIT:

Install SSRS and configure it so that it's working:

https://msdn.microsoft.com/en-us/library/ms143711.aspx

I assume you already have SSDT since you mentioned SSIS, but just in case you don't:

https://msdn.microsoft.com/en-us/hh500335(v=vs.103).aspx

Then you'll open Visual Studio, create a new SSRS project, make a new report, define your datasource(s), and use the toolbox to add tables or whatever other display you need. It can also use parameters if you need user-input. Static content can be placed wherever you need it - and different datasets can be displayed wherever you need them, as well.

I would like to help more, but it sounds like you are starting from scratch and I can't possibly tell you everything there is to know about SSRS in this forum. So, here is an SSRS Tutorial:

https://msdn.microsoft.com/en-us/library/ms167305.aspx

Good luck - SSRS/SSIS/SSDT are awesome tools so I'm sure you'll find what you need.

halfer
  • 19,824
  • 17
  • 99
  • 186
Stan Shaw
  • 3,014
  • 1
  • 12
  • 27
  • Appreciate your comments. Can you please share some idea how i can go ahead with SSRS on my requirement. – Abhijith Nayak Feb 12 '16 at 17:18
  • @AbhijithNayak I edited my answer with some documentation links and the steps you'll need to take. – Stan Shaw Feb 12 '16 at 17:23
  • Useful info. Now i have a question - I have created a report in SSRS and trying to use that report (.rdl) in SSIS as i need to export to CSV and send that in mail - Do i need deploy the report in that case ? or i can i use .rdl file directly ? – Abhijith Nayak Feb 13 '16 at 06:25
  • Hi All, I am able to generate a csv report with the data. In csv report i am getting the hexa decimal value in the column instead of plain numbers. Eg: "1E+15" instead of "1000000000000080". How can i fix this ? – Abhijith Nayak Feb 15 '16 at 16:49
  • @AbhijithNayak I'm confused. Did I answer your original question? – Stan Shaw Feb 16 '16 at 14:18