1

Earlier, I was running many sql query and was coping the eaxtract in csv file manually. Now, I am trying to make a ssis package which does the same.

I have used OLE DB Source in for-each loop to do this. But thats not working. So am i doing right? or is there any thing else which can help me to do(Other than the script task)?

Kinchit Dalwani
  • 398
  • 4
  • 19
  • 1
    pure SSIS won't be able to do this. With BIML on the other hand, you can do it easely. http://blog.sqlauthority.com/2014/07/11/sql-server-what-is-biml-and-how-can-it-help-me-with-sql-server-bi-notes-from-the-field-036/ – DenStudent Nov 25 '16 at 07:49
  • can you please explain what you are trying to achieve – user1254579 Nov 25 '16 at 11:13
  • @user1254579 Well, I have to create around 100 package. So it would be great if this can be automated – Kinchit Dalwani Nov 28 '16 at 07:15

1 Answers1

3

SSIS is not exactly your tool, and I will try to explain SSIS approach to data transformation.
SSIS as a service runs SSIS packages. SSIS package extracts data from a source and writes it to a destination defined in the package. At the package design time, you set metadata on the data extracted and stored - column names and data type. On the run, SSIS validates source and destination against metadata defined in the package at design time, and fires error if metadata does not match.
In your case, set of columns from the source and destination can vary significantly. Either you have to create a SSIS package for each 'source-destination' pair (and BIML can simplify this job), or switch to other tool. SSIS cannot handle arbitrary data source and destination without first defining its metadata at package design time.

Ferdipux
  • 5,116
  • 1
  • 19
  • 33