Generate multiple text files using SSIS

Question

Generate multiple text files using SSIS

bhavnabakshi

SSC Eights!

Points: 848
More actions
November 26, 2008 at 9:46 am

#124543

I'm new to SSIS. I have to generate seperate text file for each meeting. Is it possible in SSIS?
Thanks in Advance,
Bhavna

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Tim Mitchell SSCoach Points: 15685 More actions · Answer 1

Yes, it is possible. You can use a Conditional Split transform to branch your output to the respective flat file destinations. There are other options, including the Foreach Loop and the Script Task/Component depending on exactly what you are trying to accomplish. Feel free to post more detail about your need for a more specific answer.

hth,

Tim

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

bhavnabakshi SSC Eights! Points: 848 More actions · Answer 2

Thanks Tim!

Here is the query. I would like to generate seperate text file for each meeting

SELECT tblWrkshops.MeetingCode, SessionNumber= ISNULL(tblWrkshopSubjects.SessionNumb, '01'), tblSessionCons.ConsultantCode,

tblWrkshopSubjects.SubjectTitle, GenSubject = tbl_Subject_Code.parent_session_code--, tbl_lkp_ParentSessionCodes.ParentSessionTitle

FROM tblWrkshops LEFT JOIN tblWrkshopSubjects ON tblWrkshops.SetupNumb = tblWrkshopSubjects.SetupNumb

LEFT JOIN tblSessionCons ON tblWrkshopSubjects.WkshopSubjctID = tblSessionCons.WkshopSubjctID AND tblSessionCons.ConsultantType='Primary'

LEFT JOIN tbl_Subject_Code ON tblWrkshopSubjects.GenSubject = tbl_Subject_Code.Subject_Code_pk

LEFT JOIN tbl_lkp_ParentSessionCodes ON tbl_Subject_Code.parent_session_code = tbl_lkp_ParentSessionCodes.ParentSessionCode

WHERE tblWrkshops.meetingcode in ('0800806231', '3010803141', '3010806232', '3010806235', '3010806239')

Thanks,

Bhavna

Tim Mitchell SSCoach Points: 15685 More actions · Answer 3

Bhavna,

Will you be splitting this data based on the meetingcode value? If so, since you have a small number of distinct values you are best using the Conditional Split and send to 6 different flat file outputs - the five values listed above, plus an optional but recommended default output to capture and audit any information should the input change in the future.

If you're not familiar with the conditional split, your condition syntax will be something like:

meetingcode == "0800806231"

to test for the first value, and so forth.

hth,

Tim

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

bhavnabakshi SSC Eights! Points: 848 More actions · Answer 4

Yes, I'll be splitting data based on the meeting code. I have about 100+ meeting codes. I'm still not sure how to use the conditional split. I have defined condition meetingcode == "0800806231" in the condition column and when I run the package it’s eliminating the above meeting code from the file. What I want to achieve is to generate separate text files per meeting.

Thanks,

Bhavna