0

I would like to use SSIS in orded to perform tranformations on multiple files (CSVs, Excels) which are comming from various datasources and the output should be always CSV files in certain structure.

One of the requirement after performing tranformation steps is to create a output summary file (MANIFEST FILE) about the results of the process in following structure.

BATCH_ID|EXTRACTED_FILE_NAME|MODEL_TYPE|RECORD_COUNT|TOTAL_QTY|GENERATED_ON_TE|CONTENTS_FROM_DATE|CONTENTS_TO_DATE|WORKSET_ID|FILE_STATUS|FILE_STATUS_TS

000005|NSL_B_YFRCARRAB0_PRODUCT_MASTER_20171122.txt|B|829||20171122121525|||||

Important columns:

Batch ID: ID of run

EXTRACTED_FILE_NAME: Name of created CSV file by SSIS (output file)

RECORD_COUNT: Number of rows in output file

TOTAL_QTY: SUM of column QTY

GENERATED_ON_TE: When the file was generated

STATUS_TS: Status - OK / FAIL

Is this output possible to achive in SSIS? Can I create it without using script compontent? If I have to use script compontent, can you navigate me little bit?

Many thanks, Martin!

Martin Lučan
  • 51
  • 1
  • 4
  • 1
    You could do this by inserting rows into a SQL table as your package runs and this at the last step export the rows for the batch into a csv. The easiest way would be to create a procedure to insert the manifest rows and use package variables to set the parameters. – John Nov 27 '17 at 15:51
  • What version of SSIS / SQL Server? – John Nov 27 '17 at 15:52
  • I have SQL 2012 available or 2008 SP2. I have an understading that I can do so. But where I can find these parameters that I want to have in my manifest file as I described in my **important columns section**. Can you navigate me littble bit please? Give me some hint, reference, please. I would like to avoid using scrip componenet but if it is needed I will use it. – Martin Lučan Nov 28 '17 at 12:16
  • I started putting together a test for you and ran out of time. Here is an article that discusses getting the record count. Getting the file name is easy - just use the variable you defined in the file enumerator. Use the paths to determine status. The one item I need to test is getting the total value. – John Nov 28 '17 at 18:00
  • One more question: Generated_on_TE is that the date of the csv file or the date time when the package executes? – John Nov 28 '17 at 18:35

0 Answers0