Solved

SQL 2005 SSIS Include hearer and footer rows in Data Extract

Posted on 2009-07-14
5
414 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
Comment Utility
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
Comment Utility
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
Comment Utility
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now