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 Trigger

I am facing problem in creating a insert trigger for the following scenario.

i have transactions, control tables

whenever i insert a record in transactions it should get value from the control table, increment that value in control table and update the same value as transaction_id for new transaction in transaction table.

control table has these fields (control_desc, control_value)

can some one help me to write a trigger (insert trigger) in transactions table.

Tanks for the Help

Coudl you please provide more information like DDL code, showing which column you want to update etc.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for the replay

Fields in Transactions Table : trans_id,trans_type,amount,trans_date,uid

Fields in Control Table: id, id_value

I am inserting all the fields in transction table except trans_id when insert trigger fires i want to take id_value from control table which id is "trans_id" in id coulmn update the same in the trans_id field in transactiontable.

Can u help me reating this trigger.

Regards

|||

Try this

--<Run Once>

drop table Transactions

drop table control

go

create table control

(

control_desc varchar(5) not null primary key,

control_value int not null

)

create table Transactions

(

trans_id int not null identity,

control_desc varchar(5) not null foreign key references control(control_desc),

control_value int not null

)

go

create trigger ti_Transactions on Transactions for insert

as

set nocount on

update c

set c.control_value = c.control_value + 1

from control c

join inserted i

on i.control_desc = c.control_desc

go

insert control select 'ABCDE', 10001

go

--</Run Once>

--<Repeatable>

insert transactions (control_desc, control_value)

select control_desc, control_value

from control

where control_desc = 'ABCDE'

go

select * from control

select * from transactions

--</Repeatable>

|||

The following query may help you...

Code Snippet

create table control (
id int,
id_value int)

Go

create table Transactions (
trans_id int,
trans_type int,
amount float,
trans_date datetime,
uid uniqueidentifier)

Go

Insert Into control values(1,0)--Initiating the value

Go

Create Trigger Trg_Insert_Transactions
On Transactions For Insert
As
Begin
SET NOCOUNT ON;
Declare @.Id as int;

Update control WITH (ROWLOCK)
Set
@.Id = Id_value = (Id_Value +1)
Where
id =1;
Update Transactions
Set
trans_id = @.Id
Where
uid = (Select Uid From Inserted)
End

Go

Insert Into Transactions values(null, 1, 10,getdate(),newid())

GO

select * from Transactions
select * from control

Insert Trigger

I am facing problem in creating a insert trigger for the following scenario.

i have transactions, control tables

whenever i insert a record in transactions it should get value from the control table, increment that value in control table and update the same value as transaction_id for new transaction in transaction table.

control table has these fields (control_desc, control_value)

can some one help me to write a trigger (insert trigger) in transactions table.

Tanks for the Help

Coudl you please provide more information like DDL code, showing which column you want to update etc.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for the replay

Fields in Transactions Table : trans_id,trans_type,amount,trans_date,uid

Fields in Control Table: id, id_value

I am inserting all the fields in transction table except trans_id when insert trigger fires i want to take id_value from control table which id is "trans_id" in id coulmn update the same in the trans_id field in transactiontable.

Can u help me reating this trigger.

Regards

|||

Try this

--<Run Once>

drop table Transactions

drop table control

go

create table control

(

control_desc varchar(5) not null primary key,

control_value int not null

)

create table Transactions

(

trans_id int not null identity,

control_desc varchar(5) not null foreign key references control(control_desc),

control_value int not null

)

go

create trigger ti_Transactions on Transactions for insert

as

set nocount on

update c

set c.control_value = c.control_value + 1

from control c

join inserted i

on i.control_desc = c.control_desc

go

insert control select 'ABCDE', 10001

go

--</Run Once>

--<Repeatable>

insert transactions (control_desc, control_value)

select control_desc, control_value

from control

where control_desc = 'ABCDE'

go

select * from control

select * from transactions

--</Repeatable>

|||

The following query may help you...

Code Snippet

create table control (
id int,
id_value int)

Go

create table Transactions (
trans_id int,
trans_type int,
amount float,
trans_date datetime,
uid uniqueidentifier)

Go

Insert Into control values(1,0)--Initiating the value

Go

Create Trigger Trg_Insert_Transactions
On Transactions For Insert
As
Begin
SET NOCOUNT ON;
Declare @.Id as int;

Update control WITH (ROWLOCK)
Set
@.Id = Id_value = (Id_Value +1)
Where
id =1;
Update Transactions
Set
trans_id = @.Id
Where
uid = (Select Uid From Inserted)
End

Go

Insert Into Transactions values(null, 1, 10,getdate(),newid())

GO

select * from Transactions
select * from control

sql