January 16, 2008 at 5:29 am
Hi All,
I have created a DTS package that exports a table data into a text file.
What i need to do is to insert a description of the file before the actual data is exported.
Is there any way to accomplish this task.
Thanks in advance.
Kamran
January 16, 2008 at 2:39 pm
I had to do it before and it might not be the best way to do it.
I had a ActiveX script to write the header line in the text file before insert the data into the text file.
January 16, 2008 at 4:46 pm
By description, I assume you mean a list of the column names. There are number of different ways you can do this.
One way would be to use a SQL query in your Data Transformation source that is a UNION of 2 select statements. 1 statement will be the column list or your description, the other will be the query for actual data. The only trick to this is that to ensure the header is actually the 1st row returned by the query is to introduce 1 additional column that will be of a numeric data type and sort by this column. Set the value to 1 for the column list and 2 for the data query. To prevent this column from appearing in the destination text file, simply delete the tranformation for that column. Here's an example (I'm calling the numeric column "SortOrder":
SELECT
'FirstName',
'LastName',
'AddressLine1',
'AddressLine2',
'City',
'State',
'Zip',
1 AS SortOrder
UNION
SELECT
FirstName,
LastName,
AddressLine1,
AddressLine2,
City,
State,
Zip,
2 AS SortOrder
FROM
dbo.Customers (NOLOCK)
ORDER BY
SortOrder
Another way would be to keep your tranformation as is and add an Active X script task that uses the FileSystem Object to create a new text file, write your description as the 1st line, then copy all the data from the text file created by the Data Transformation and paste it into this new text file. There are plenty of examples on this site and others on how to do this.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply