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 single Parent and multiple Children

I am working on a project where I have a page that will have a parent record (Product) and then 1 or more children (options available for the product, user enters text to define) displayed in a table/gridview. There is a relationship defined in the database between the product and options table).

My question is how can I allow the user to add the product info and then within the same page also add the options and only then save it all? The options will added to a table.

Thanks for any help

You may use triggers, I mean you can create triggers on the main table, in which you can insert records to other tables. You can start from here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_4nxu.asp

|||I don't see how a trigger will help. I need to have a way to add the parent and all its children at once. How will a trigger help?

insert simultaneously in 2 tables

Dear All,

I am not an expert on T-SQL and I am trying out a small project to learn.

I

have encountered a problem, whereby I have 2 tables, 1 containing the

header (header_id, header_file, admin_menu_id and admin_submenu_id) and

then I have another table called header_details, where I am storing the

language details for this header, with the fields being

(header_details_id, fk_header_id, header_alt, header_caption and

fk_language_id)

Now I want to create a stored proc, first to

insert the header and then the header details. I also want that if the

header or header details already exist, I just do an update on these

tables.

I tried the following code but its not working:-

ALTER Procedure [dbo].[INSERT_Header]
(
@.admin_menu_idint,
@.admin_submenu_idint,
@.header_filevarchar(150),
@.header_altvarchar(150),
@.header_captionvarchar(200),
@.language_idint
@.outIDint OUTPUT
)
AS
BEGIN

-- First do a select on the header table to see if this header already exists

DECLARE @.count integer = 0

SELECT COUNT(*) as @.count
FROM headers
WHERE[fk_admin_menu_id] = @.admin_menu_id
AND[fk_admin_submenu_id] = @.admin_submenu_id

--if it exists, then update this header
IF @.count > 0
BEGIN
UPDATE [headers]
SET [header_file]= @.header_file

WHERE [fk_admin_menu_id] = @.admin_menu_id
[fk_admin_submenu_id] = @.admin_submenu_id
ELSE
-- Insert header in header table --
BEGIN
INSERT INTO [headers]
([header_file]
,[fk_admin_menu_id]
,[fk_admin_submenu_id])
VALUES
(@.header_file
,@.admin_menu_id
,@.admin_submenu_id)
-- Get the Inserted Header ID --
SET @.outID = SCOPE_IDENTITY()
END

-- Now do a select on the header_details table to see if this header with this language already exists
DECLARE @.count_details integer = 0

SELECT COUNT(*) as @.count_details
FROM header_detail
WHERE[fk_admin_menu_id] = @.admin_menu_id
AND[fk_admin_submenu_id] = @.admin_submenu_id
AND[fk_language_id] = @.language_id

--if it exists, then update this header
IF @.count_details > 0
BEGIN
UPDATE [header_detail]
SET[header_alt]= @.header_file
,[header_caption] = @.header_caption

WHERE [fk_header_id] = @.header_id
[fk_language_id] = @.language_id
ELSE
-- Insert header in header table --
BEGIN
INSERT INTO [header_detail]
([fk_header_id]
,[header_alt]
,[header_caption]
,[fk_language_id])
VALUES
(@.@.out_ID
,@.header_alt
,@.header_caption
,@.language_id)
END

END

Can you help me out please?

Thanks a lot for your help and time

Johannhave you done a syntax check... your T-SQL is full of errors

1
DECLARE @.count integer = 0 is not valid, you cannot assign a value on a declaration

2
SELECT COUNT(*) as @.count
FROM headers
WHERE[fk_admin_menu_id] = @.admin_menu_id
AND[fk_admin_submenu_id] = @.admin_submenu_id
does not assign a value to @.count, to do do it you must write as follows:
SELECT @.count=COUNT(*)
FROM headers

.3

WHERE [fk_admin_menu_id] = @.admin_menu_id
[fk_admin_submenu_id] = @.admin_submenu_id
ELSE
is also invalid you must put END berfore else and maybe an AND on the where clause

Are you sure you've tried the statement in query analyzer or management studio ?

|||

Hi,

you should use If Exists construct instead o using Count(*) method as

If Exists (SELECT fk_admin_menu_id FROM headers
WHERE [fk_admin_menu_id] = @.admin_menu_id
AND [fk_admin_submenu_id] = @.admin_submenu_id
)

BEGIN

UPDATE [headers]
SET [header_file] = @.header_file
WHERE [fk_admin_menu_id] = @.admin_menu_id
[fk_admin_submenu_id] = @.admin_submenu_id

END
ELSE

-- Insert header in header table --
BEGIN
INSERT INTO [headers]
([header_file]
,[fk_admin_menu_id]
,[fk_admin_submenu_id])
VALUES
(@.header_file
,@.admin_menu_id
,@.admin_submenu_id)
-- Get the Inserted Header ID --
SET @.outID = SCOPE_IDENTITY()

END

-- Now do a select on the header_details table to see if this header with this language already exists
DECLARE @.count_details integer = 0

IF EXISTS (SELECT fk_admin_menu_id FROM header_detail
WHERE [fk_admin_menu_id] = @.admin_menu_id
AND [fk_admin_submenu_id] = @.admin_submenu_id
AND [fk_language_id] = @.language_id
)

BEGIN

UPDATE [header_detail]
SET [header_alt] = @.header_file
,[header_caption] = @.header_caption
WHERE [fk_header_id] = @.header_id
[fk_language_id] = @.language_id

END

ELSE
-- Insert header in header table --
BEGIN

INSERT INTO [header_detail]
([fk_header_id]
,[header_alt]
,[header_caption]
,[fk_language_id])
VALUES
(@.@.out_ID
,@.header_alt
,@.header_caption
,@.language_id)

END