I need to export data to a positional flat file without a delimiter that has several records with different formats. I, also, need to loop through the data being exported to arrange the data in the file by a key. (Account) I have created 5 tables in 2005 with the different formats needed as all varchar columns at the proper size required by the spec and are ready for export. These tables are sorted by the account key; however the key is only in one record. The first record identifies a facility, which is a single record. The next record identifies an account. The next 2 records identify all the different types of tasks that are assigned to an account that have different formats. Then the final record for each account is the actual charges. Therefore, my task is to write the flat file in the steps. 1. Insert the one facility record. 2. Insert an account record with the key information to the account. 3. Insert the first task record that is assigned to the account without the key. 4. Insert the second task record (with a different format of different columns). Both 3 and 4 can have several records of each with the same format that belongs to the original account. 5. Insert the 4th record type with the actual charges. Again, can have several records. 6. Loop back to a second account record (step 2) that is the next account. Then repeat steps 3-5 for that account until all the accounts have been entered. Each record is delimited with a LF and CR only without any column delimiters. There must be someone that has done this and can tell me what the best way to do it is and how they did it. SSIS, CLR, BCP?
you could definitely do this in SSIS, but it is a bit tricky and i would need table structures and sample data plus sample output to provide any meaningful help though. All you need to do is create a flat file connection with row delimiter LF CR, and concatenate the mixed format records in a single row to your requirements. Oleg is right though, even though you have the format in the database, you will either have to convert them to Char in you source query, or use an SSIS transform component to pad the values to the right. So, if you would like some more help, feel free to post table structures and sample data and sample output. Good luck!
Looking at it, this could be done as a T-SQL script, assuming you can get the keys sorted out. What I would first try to do is create intermediate tables holding the results for each intermediate step (1-5) as run across all records - keeping the key and any sequencing / ordering information in. [OVER & ROW_NUMBER] clause may help here. Then either create a view or a CTE that builds up the output - something like SELECT 1 AS StepID, NULL AS KeyID, data from #Step1 UNION SELECT 2 AS StepID, Key AS KeyID, data FROM #Step2 UNION SELECT 3 AS StepID, Key.... and then your final output is SELECT data from CTEorView ORDER BY StepID, KeyID Wrap up the script in a .sql file, and use SQLCmd (or oSQL) to connect to the server, execute the script, and capture the output in a new file. :
Daniel and Thomas are headed in the right direction on this one. The key will be to number the rows internally (perhaps using more than 1 column) so that they come out in the file in the order you need. BCP could certainly provide the output but, because all the rows have a different format, you need to contatenate the data for each row into a single column for all the rows. An output staging table would be good for all that work and then you'd just BCP out the final data column in the correct order.