Inserting a Control Record into a Flat Text File through SSIS

I am working on an SSIS project where I create two flat files for submission to a data contractor. This contractor requires a control record be the first line in the file. I create the control record based on the table information being exported.

What I would like to know is, is it possible to utilize the Header Section of the Flat File Destination Editor to insert the control record? And, as it is dynamic, what kind of coding must I do in order to utlise this functionality?

Thanks.

Yes, I would guess that you can do exactly this using the header section.

You can set it dynamically by putting an expression on the [<Flat File Destination Name>].[Header] property of the parent data-flow task.

-Jamie

|||

Ok, I see that this can work, but looking at the available variables, functions for expressions, I do not see how I would get the data inserted from another text file (table) already created into this second one.

Truly not trying to be dense here, just "can't seem to see the forest for the trees."

Thanks.

|||

That's a bit of a different requirement. You may be hampered by the fact that the maximum length of the result of an expression can only be 4000 chars

The way to do it would probably be to build the text up programatically in a script task.

-Jamie

sql

Insert rows into a Flat File

Hi!

I have a problem that I can′t understand.

I have rows in a table in a SQL Server 2005 that I want to send to a Flat file. When I am executing the task for the first time it is OK and works perfectly. But the problem is when I am trying to do it again. I seems like the path to the file and the filename dissappears after the first time and the execution failes with an error message that tells me that the file is invalid and don′t exists.

I don′t now why this is happening and it′s such an easy thing to do and I can′t make it work.

So please help me!

Regards,

Johanna

I have no idea from what you descripted

I think you should double check your setting or programming

|||

Johanna Maria wrote:

Hi!

I have a problem that I can′t understand.

I have rows in a table in a SQL Server 2005 that I want to send to a Flat file. When I am executing the task for the first time it is OK and works perfectly. But the problem is when I am trying to do it again. I seems like the path to the file and the filename dissappears after the first time and the execution failes with an error message that tells me that the file is invalid and don′t exists.

I don′t now why this is happening and it′s such an easy thing to do and I can′t make it work.

So please help me!

Regards,

Johanna

Can you post the full error message?

Does this happen at design-time or execution-time?

-Jamie

insert or update data records

I'd like to do the following thing with a data flow task

Get all the records from a source (for example customers from a textfile, flat file source) Then check for each record if the customer already exists in a table, for example with a customerID. If not, insert the record in the table (ole db destination), else copy the customer thats already in the table to another table (history table) and update the record with the customer from the textfile.

Is this possible?, and what kind of data flow transformation do I need?

Use a lookup to check if the customer exists. Use error output from the lookup to insert new customers, OLE-DB Destination. Use the normal output from the lookup to feed an OLE-DB Command to do the move and update. That may be easier if wrapped in a stored procedure.