Solved

SQL 2005 SSIS Include hearer and footer rows in Data Extract

Posted on 2009-07-14
5
419 Views
Last Modified: 2013-11-10
I've been tasked with creating a merchandiser product extract for submission to Linkshare. I've extracted all the product information into a new table in SQL and using SSIS i've exported the table to a CSV file. However that's where I come unstuck.

The requirements also state that I need to do the following:
1. Add a header row to the CSV file in the format: HDR | Merchant id | Company Name | Time Stamp
2. Add a footer row below the product information in the format: TRL | product row count
3.Finally the resultant file name needs to include the date in the format filename_20091407 ie. yyyyddmm

Anyone?

0
Comment
Question by:Christie_M
5 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24851900
1.  If the header delimiter is the same as detail rows, then all you need to do is include the column names in the first row.
2. This can sometimes be accomplished by using a UNION statement with the same number/type of columns as the rest of the data.  Alternatively you will have to open the file and write the results at the end.
3. You can change this programatically within the SSIS package.
0
 

Author Comment

by:Christie_M
ID: 24852061
Both the header and the footer are different formats from the product data itself. So there would be no way to join tables or populate the first row. Any other ideas.

3. How do I porgram this in the SSIS package?
0
 
LVL 22

Accepted Solution

by:
PedroCGD earned 500 total points
ID: 24867904
I created a example package with the solution at:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24571933.html

Helped?
Regards,
Pedro
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question