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

[ginaburg]

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

 

[jjefferson]

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.

 

[gk53]

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