INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Need a fixed length flat file with Header, Detail and Trailer records

Need a fixed length flat file with Header, Detail and Trailer records

(OP)
I was wondering if someone can help me with some of the flow for this project. I am farily new to SSIS and seems like there are several way to accomplish what I want to do. Just trying to get some input before I go in the wrong direction. Here is what I need to accomplish.
We need a fixed length flat file for an up load to our bank for our Accounts Payable checks.
We will select the checks to be paid in our current SQL Accounting system. Assign check numbers to them and then dump a file to be uploaded to our bank.
I will be working with SQL header and detail tables. Not sure yet how I will pass the parameters through from out current system or select them offline? Date Range or check number range
The file can contain different types of payments, checks, ACH, EFT and will need to be flagged in the output file.
I understand what data I need from the two files, the problem I am having is setting up the flow in SSIS.
I need to bring the two SQL files in with parameters,
manipulate the date - there will need to be a lot of field formatting. filling with leading zeros on some, filling with spaces on others, stripping dates, formatting dollars without decimals places, etc,
Then outputting a header record, detail record and trailer record into one file.

Can anyone help me with the flow. What parts do you put in the Control flow vs Data flow. In playing with this, I see that you can put parameters in both control flow and data flow. When do you use which one.

Any help would be appreciated.

Thanks
Gina

RE: Need a fixed length flat file with Header, Detail and Trailer records

We do something similar to this, I think. We have most of the logic in the stored procedure that just builds a temp table with a varchar(MAX) column and supporting columns to aid in sorting the data for output. Then we just construct the detail rows into the varchar column in either fixed format or delimited fashion, and then just output that as the dataset for the SSIS package.

The package then just outputs that to a flat file connection and does some other administrative stuff, but the heavy lifting is done in the stored procedure.

Just a thought; not sure this is what will work for you.

RE: Need a fixed length flat file with Header, Detail and Trailer records

it is dependence of you system set up you can have different flows...
you can set execute SQL task and create all date and store it in holder tables and after that step done you can set data flow task and put inside all extractions to flat files (and select in data source can have all formatting cases)
To send parameters into I usually use file in shared folder on SQL server which package read as first step (script task) and load into package variables, which you pass to all necessary tasks

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close