Post reply

Header and Footers in Flat File using SSIS

  • February 28, 2008 at 4:54 pm

    #378967

    Hi All,

    I'm looking for some help on SSIS packages. Following is my problem.

    I need to generate a flat file with the header and footer information and the flat file would be in the following format

    File format:

    Note: Sequence number in the file name must match with the header information(001)

    Row Count(3),

    With a footer 'END'

    ABCD001 3

    2944605|5274.31

    1252986|5115.44

    2568116|2903.14

    END

    File name: XYZ001.txt

    Note: notice digit 001 in both header and filename.

    Could any one please let me know how can I handle the following situations using SSIS packages.

    How to have row count in the header information.

    How to make sure the digit in header information matches with sequence number of the flat file.

    How can I generate the flat file dynamically with a sequence number appended.

    Any help would be greatly appreciated.

    Thanks in advance,

    -Amith Vemuganti

  • February 29, 2008 at 8:47 am

    #783840

    This seems to be a comple text file which will be very difficult to create from SSIS.

    Why you need to generate this file from SSIS?




    My Blog: http://dineshasanka.spaces.live.com/

  • February 29, 2008 at 4:42 pm

    #784084

    Hi Dinesh,

    Thanks for the reply. The sequence number is basically to uniquely identify the file if multiple files have to be generated, because the string to which the digit is appended would be common for all the files. I'm also not sure why the Header information should be in that format, its a requirement given to me.

    I could find a solution by appending date and timestamp to the first part of the header information and file name. I have used a variable and concatinated the timestamp to the basic string.

    For Example, The first part of Header would be ABC20080229173546 and the filename would be XYZ20080229173546.

    The Row Count in the Header section is also achieved using Execute SQL Task component and a variable in the control flow.

    Now my question is, How do we add footer to the file using SSIS.

    Any response would be appreciated.

    Thanks,

    Amith

  • February 29, 2008 at 5:38 pm

    #784088

    You would probably be better off outputting the file using reporting services. Would make the whole process much simpler. You could script out any custom actions that need to be done all with in SSIS.

  • March 1, 2008 at 11:38 am

    #784178

    OK....I would try it and see.

    Thanks,

    Amith Vemuganti

  • March 2, 2008 at 7:29 pm

    #784278

    You have to do some fancy manuvering. This link will walk you through the process 1 step at a time.

    http://agilebi.com/cs/blogs/jwelch/archive/2008/02/08/adding-headers-and-footers-to-flat-files.aspx


    My blog: http://jahaines.blogspot.com

  • March 6, 2008 at 11:15 am

    #786234

    Create / build the results separately, then append them together in the right order at the end.

    With SSIS, you need to avoid trying to do everything "in one step". It's actually ends up way more difficult and takes longer. Always break it down into very small, discrete operations, then string them together. It takes a while to get the hang of it, but then it suddenly make a world of sense.

  • March 6, 2008 at 1:57 pm

    #786346

    I had a similar problem in which one of the developer helped me using .NET to solve this kind of problem. Am not able to find the script. If you have a VB or C# knowledge, it will help you more.

    [font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]

Viewing 8 posts - 1 through 7 (of 7 total)

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