Should Be Simple - FLAT FILE - HEADER / DETAIL LINES

Feb 21, 2007

I can't believe it's been a few days and I can't figure this out. We have a flat file (purchaseOrder.txt) that has header and detail lines. It gets dropped in a folder. I need to pick it up and insert it into normalized tables and/or transform it into another file structure or .NET class.

10001,2005/01/01,some more data
SOME PRODUCT 1, 10
SOME PRODUCT 2, 5

Can somebody place give me some guidance on how to do this in SSIS?

View 2 Replies


ADVERTISEMENT

Transform Header And Detail Flat File Into One Table

Sep 13, 2007

I have a flat file with header and detail information, it is actually employee punch card data. I need to parse the header line which contains the Employee ID and don't save it to a table just save the value. Then with the detail line, parse the different data elements and save them along with the employee ID to one table. Then continue until the next header line is read.


The file looks something like this:

FINNEY,RONNIE 0001005420
Mon 09/03 700a HOL 8.00
Tue 09/04 630a*E 326p 8.50 8.50
Wed 09/05 645a 330p 8.00 16.50
Thu 09/06 639a 2.40 18.90
HALL,MARK 0001005601
Mon 09/03 700a HOL 8.00
Tue 09/04 608a*E 257p 8.40 8.40
Wed 09/05 601a*E 259p 8.50 16.90
Thu 09/06 606a*E 3.30 20.20
JONES,WILLA JEAN 0001005702
Mon 09/03 700a HOL 8.00
Tue 09/04 556a*E 326p 9.10 9.10
Wed 09/05 600a*E 328p 9.00 18.10
Thu 09/06 554a*E 3.50 21.60

So I think I need a data flow transformation object that let's me save the Employee ID into a variable available when the next record is read. What type of transformation would be best?

View 1 Replies View Related

SSIS Header And Detail To A File

Feb 19, 2008

Hi Anyone

How to export a header and details data from two different table and export it in the below format ?

RecordCount = 129 ------------> Header
001|Manager|2399.00|12 ------------> Detail Lines
002|Technican|1800.00|15
003|Mechanic|1500.00|18
.......
Total Amount = 180000.00 ------------> Footer Line

I want to use the SSIS to do this job can anyone explain step by step.

Thanks,
Madhu

View 3 Replies View Related

Selecting Detail Based On A Sum Of The Detail Lines

Sep 14, 2007

I am listing detail transaction lines in a table sorted by account and order number.
the problem is that I only want to see the detail if the sum of a value field is zero for all the transactions in an order otherwise ignore the detail for that order.

I was trying Group by and Having but this doesn't seem to do what I need.

Being relatively new to Reporting services, any nudge in the right direction would be useful.

View 4 Replies View Related

Removing Lines From A Flat File

Oct 5, 2006

Hi There,

I am parsing a directory of flat files and looping through it with a foreach loop. Some of the files have lines that contain characters that I would like to remove. In fact, it would be good if I could remove the entire line. Is there a way to do this with a Script Task or some other way.

Thanks for your help.

View 3 Replies View Related

Reading First Few Lines From Flat File

Jun 5, 2007



Hi,

I have a very simple question, how do i read the first couple of lines only from a flat file source. Let me illustrate with an example:



**Source file***

Date of refresh 04/05/06 **

abc, 123

bac, 156



I need a way to read the first line 'Date of refresh 04/05/06 **' and get the date '04/05/06' ; and store the date in a variable.



Right now, I have a flat file source and a script component, which receives a connection from the flat file source, The script component is reading the lines, but thrice instead of once. It would be great if I can program the script component to terminate after reading the first line only. Any help would be appreciated very much.



Thanks

Bidyut



View 5 Replies View Related

How Can I Take This Example Flat File And Parse Out Each Section To A New Flat File? Each Section Starts With HD (header Row)

Mar 13, 2006

How can I take this example Flat file and parse out each section to a new flat file?  Each section starts with HD (header row)

http://www.webfound.net/flat_file_example.txt

e.g. an example output file based on above (cutting out the first section) would be:

http://www.webfound.net/flatfile_output.txt

Also, I'll need to grab a certain value in each header row (certain position in the 100 byte header row) to use that as part of the filename that's outputed.  I assume it would be better to insert these rows into a temp table then somehow do a search on a specific position in the row...but that's impossible?  The other route is to insert each row into a temp table separated out by fields but that is going to be too combursome because we have several formats to determine separation of fields based on the row type so I'd have to create many temp tables and many components in SSIS when all we want to do is again:

1) output each group (broken by each header row) into it's own txt file

2) use a field in  the  header row as part of the name of the output txt file (e.g. look at the first row, whcih is a header row in flat_file_example. txt.  I want to grab the text 'AR10' and use that as part of the filename that I create

Any suggestions on how to approach this whole process in SSIS...the simplest approach that will work ?

View 1 Replies View Related

Get Header Data Of Flat File

Jul 4, 2007

hi everyone!

i am currently creating a package which involves getting data from CSV files. i can successfully get the data from the files, my problem is, i need to get data from the header of the CSV files. i am currently skipping the header rows. the format of the CSV files is as follows:

-----------------------------------------------------------------------------------
Date, 20070704
Store Code, storeCode1

data row.....
data row.....
data row.....
-----------------------------------------------------------------------------------

technically, i also need the date from the header row, but since it is also indicated in the data rows, i have no problem with that. what i need is the Store Code, which is not indicated on the data rows. i need to store the data in a database in the following format:

-----------------------------------------------------------------------------------
StoreCode Date column1 column2 column3 ......
storeCode1 20070704 ...
storeCode2 20070704 ...
storeCode3 20070704 ...

-----------------------------------------------------------------------------------


any idea how SSIS can handle this? thanks a lot!

View 4 Replies View Related

Header And Transaction In A Flat File

Mar 12, 2008

Hi All,
Any one could help me like how to port the data into the SQL Server Database if i have the below sample data in flat file.

2 300 1 3 5058045 09999XX999999999 01/10/200801/18/200840477
3 300 5058045 66 20080118232724
4 300 5058045 03000000235 20080118232724
4 300 5058045 03000000322 20080118232724
4 300 5058045 03000000335 20080118232724

Basically, Marked Red record stands for Transaction Header, Marked Green records are Transaction Detail 1 and Marked Blue records stands for another Transaction Detail 2.


Now I need to move the data, based on the Record Type ( First Column 2,3,4) If its RecTyp 2 then move into Tran Header table, when RecType 3 then move into Tran Det1 table and finally, when RecType 4 then move into Tran Det2 table.

Anyone could guide how to start this migration.

Note: The given sample is the one set of data in that flat file. Similarly in the same flatfile we have multiple set of data.

View 10 Replies View Related

Different Format In Flat File For Header And Data

Apr 12, 2007

Hello all,



Is it possible to have two different formats for the header and the data in a flat file connection?



An example text file would look like this:

Col1,Col2,Col3

abcdefghi12345testtesttesttest

abcdeeeee12333setsetsetsetsets



where the header is delimited and the data is ragged right.



It looks like you should be able to accomplish this from the Flat File Connection Manager Editor interface, but perhaps having different delimiter dropdown boxes for the header and columns can only be used if you are using the Delimited format?



Thanks for any info you can provide!

Jessica

View 4 Replies View Related

Writing A Header Row To A Flat File Destination

Sep 18, 2006

I'm unable to figure out how to write a column header to my flat file destination. My source is a OLE DB SQL query and I need the column names as a header row in my text file destination. This seems easy but the closet I can find is hardcoding the column header row in the header property. Is this the only option?



Thanks

View 1 Replies View Related

TSQL Help; Need A Header File In The Top Of A Flat File

Aug 16, 2007

I have a tsql script that gets the data I need, into the format I need, and  saves it in a format (.output) I needI also have a script that creates a header for the report, basically its just a name and rowcount() that also works fine.PROBLEM: If I combine them using UNION, I have to pad out the header report with NULL columns, and it messes up the layout of the report.Anyone have  a simple way to do this?here's my code:SELECT 'A71310000'+ltrim(Str(count(UserName))) + 'HRBATCH' AS header, NULL as col2, NULL as col3, NULL as col4, NULL as col5, NULL as col6, NULL as col7, NULL as col8FROM db_owner.PS_HR_HrsWHERE Reported is NULLUNION ALLSELECT EmplID, Convert(VarChar,DateWorked,111),'STSSH', CAST(REPLACE(STR(HoursWorked,9, 5), SPACE(1), '0') AS nchar(9)), HRAccountCode, CAST(REPLACE(STR(EmployeePayRate,18, 6), SPACE(1), '0') AS nchar(18)), 'A_STUDSUM', HRAccountCodeOverrideFROM db_owner.PS_HR_HrsWHERE reported is NULL  What I need it to look like is: A713100007HRBATCH                                                                                       


10068800                 
2007/06/04STSSH012.00000 A108145                  00000000007.500000 A_STUDSUM

10068800                 
2007/06/05STSSH002.00000 A108145                  00000000007.500000 A_STUDSUM  ...(this is a ragid right with spaces padding out fixed width columns) THANKS for ANY light ANYONE can shed on this.   

View 14 Replies View Related

Flat File With Fixed Length Header And No Delimeter

Aug 21, 2006

Hi,

I'm trying to extract data from a Flat File which is as fixed length as they come. The file has a header, which simply contains the number of records in the file, followed by the records, with no header delimeter (No CR/LF, nothing).

For example a file would look like the following:

00000003Name1Address1Name2Address2Name3Address3

So this has 3 records (indicated by the first 8 characters), each consisting of a Name and Address.

I can't see a way to extract the data using a flat file connection, unless we add a delimeter for the header (not possible at this stage). Am I wrong?

Any suggestions on possible solution would be much appreciated - I'm thinking Ill have to write a script to parse the file manually.

Thanks in advance,

Scott

View 6 Replies View Related

Carriage Return In Header Of Flat File Destination

Sep 8, 2006

I'm trying to create a flat file that has a header like:

/INST=-1
/DELIMITER=","
/FIELDS=FIELD1,FIELD2,FIELD3,FIELD4
/LOCATION=100
data,data,data,data
data,data,data,data

where 'data' represents the data written out by the data flow process to the flat file destination. This actually turns out quite nice except that when I place the lines that start with '/' in the header box for the flat file destination the carriage return doesn't get written correctly after each line and I end up with an unrecognized character when I open the file in a simple app like notepad. I've tried using different encodings for the flat file connection, but to no avail. It is also interesting to note that when I close the package and reopen it the flat file destination editor UI also doesn't recognize the carriage returns and places a box in there place.

Below is a copy of the the property as it is written in the package xml:

<property id="92" name="Header" dataType="System.String" state="default" isArray="false" description="Specifies the text to write to the destination file before any data is written." typeConverter="" UITypeEditor="" containsID="false" expressionType="Notify">/INST=-1
/DELIMITER=","
/FIELDS=FIELD1,FIELD2,FIELD3,FIELD4
/LOCATION=100</property>

Any help is appreciated.

-dotnetwiz

View 6 Replies View Related

SSIS: Set Header Printed When Writing To A Flat File From A Variable

Jun 13, 2007

I have a variable defined as "Country". Based on the value, the header row printed needs to be different.



I've already created a 'HeaderRow' variable that I'm able to set using a script task. But how can you set the Header text value at run time from the variable? There is no expression defined for the Header with the Flat File Destination object, and when I attempt to reference the HeaderRow variable as the Header text, the variable name is printed as the header.



Another approach I tried was to write the Header Row separately through another data flow task, but the issue here is: what is the input source when all you have is a Country variable?

View 1 Replies View Related

Integration Services :: How To Add Row-count In Flat File Header In SSIS

Jul 30, 2015

I am facing a problem i just want total no row count and it should be show in header .So how can i do in SSIS

 Suppose 

eg. HeaderName , 3
data1
data2
data3
FooterName,3

View 5 Replies View Related

Header Rows To Skip On Flat File Import/Export

Aug 29, 2006

Just attempting to import a simple tab delimited text file into my SQL Server 2005 database using the SQL Server Import and Export wizard. Column names are specified within the first line of the file. The Header Rows to Skip field value is listed as 0, but the wizard indicates that "The field, Header rows to skip, does not contain a valid numeric value".

Why isn't zero (0) a valid numeric value? I don't want to skip any rows. PLUS, I get the same error when trying to export to a text file although the header rows to skip field does not exist. I can increase the number to 1 or more, but the wizard will skip part of my data .. unacceptable.

What am I missing here? I installed SP1 of SQL server 2005, but that did not help.

Thanks in advance.



View 1 Replies View Related

Report With Constant Number Of Detail Lines

Nov 1, 2007

I am developing a report analog of a machine readable form that has to display a static number of detail rows regardless of the number returned from the database - i.e. if a record set has only three detail records, I need to display three blank rows, while if the record set has ten detail records, I need to display six detail records, print the footer, start a new page, repeat the header information, print the remaining 4 detail records and 2 blank rows, print the footer again and move on to the remaining recordset. I am new to report development and I'm having to pick it up on the fly. I can't seem to locate any documentation about how to handle this scenario.

I don't have the time or inclination to re-invent the wheel here, is there anyone who has solved this problem who can point me in the direction of some help?

View 2 Replies View Related

Adding A Header Record To A Fixed Width Flat File Data Export.

Jun 21, 2007

Hi-I have a sql database (2005) that I need to extract a report from that looks somehintg like  SELECT * From Empl_Hours WHERE some_flag <> 'true' .The thing works fine, but the problem is this: I need to insert a record in the 1st row that looks like "Static_text"+row_count() +"more_static_text"where row_count is the actual # of rows that were retrieved. Thanks in advance for any help.DAn 

View 3 Replies View Related

Changing Header Rows To Skip Property In Flat File Connection During Runtime

Dec 21, 2006

Hi all

I have a flat file.I am trying to set the value for the property "HeaderRowsToSkip" during runtime.I have set an expression for this in my "flat file connection manager". But this is not working.The connection manager is not able to take the value during runtime.

My expression is as follows:

DataRowsToSkip : @[user:: Var]

where "Var" is my variable which gets the value from the rowcount component and trying to set it back to the "HeaderRowsToskip" property.

I ve even tried setting the value to the "HeaderRowsToSkip" property in the expression builder.

Its not working....

Can anyone help me out in solving this????

Thanks in advance

Regards

Suganya

View 22 Replies View Related

Simple Flat File Import

Jul 26, 2006

Boy, do I need HELP! Have a simple csv file that I need to import. Worked fine in sql2000; I put it into dts to execute on a monthly basis. Makes connection, db connection, table creation fine, but stops at validation of flat file?

Basically, I want to go out and get a flat file, drop the existing table, and create the table, and import the information from the flat file. Not a complicated table of about 30,000 records.

Create table [db].[dbo].[tblPatient] (
[patientID] into not null, [chartID] varChar(15) null, [doctorID] int null, [birthdate] datetime null, [sex] varchar(1) null, [raceID] int null, [city] varchar(100) null, [state] varchar(2) null, [zip9] varchar(9) null, [patientTypeID] int null, [patName] varchar(100) null)

Below is the error report that tells me NOTHING!

Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Success)
- Setting Destination Connection (Success)
- Validating (Error)
Messages
* Error 0xc00470fe: Data Flow Task:
The product level is insufficient for component "Source - pmPatientInfo_csv" (1).
(SQL Server Import and Export Wizard)
* Error 0xc00470fe: Data Flow Task:
The product level is insufficient for component "Data Conversion 1" (71).
(SQL Server Import and Export Wizard)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Success)
- Copying to [fhc].[dbo].[tblpatient3] (Stopped)
- Post-execute (Stopped)
- Cleanup (Stopped)

View 12 Replies View Related

Drilldown: Collapsed Detail Lines Still Take Up White Space Instead Of Resizing On Page

May 15, 2007

I have a report that (for purposes of this example) displays States and the Cities in a state.



Now the State is the first group which I want to drill down into by expanding and then show the Cities as the detail line.



What I would expect to happen (and what does happen if I use the wizard instead of creating the report manually) is that I would click on a State and then the page would expand to make room for all the cities listed below.



(That is to say if I am showing only states, not expanded, then all 50 states fit on a single page. Then if I expand California all the states below that shift onto other pages to make room for the 400+ cities in California)



However, what happens is that when I preview the report instead of being 1 page long it is many pages in length with empty white space between the States (white space that would be occupied by the cities if I were to expand the state).



Can someone tell me what setting I need to adjust to get the report to dynamically resize when I expand/collapse groups?



Thank you.

View 1 Replies View Related

Header And Detail Records From DTS

Apr 16, 2004

I am creating a DTS package to export a text file. My question is: does anyone have any ideas on how for one read of the tables I can produce 2 lines of output. Here is how the file layout needs to be...

HEADER
DETAIL
HEADER
DETAIL
HEADER
DETAIL

I am a little confused about how I can stagger header and detail using the same data.

I appreciate any help you can give. Hopefully my explanation of the problem is understandable.

Thanks, Val

View 2 Replies View Related

Detail Record Header

Mar 29, 2007

Hi,

I'm new to SSRS. I was just wondering how do I make the header for a detail record appear once per grouping rather than once per detail record?



Thanks.

View 1 Replies View Related

How To Copy Detail Records To Another Header

Jun 4, 2004

Im trying to copy details from a specific header as details of a different header (eg. all sales items from invoice #10 copied as sales items of invoice #11).

So far I have two stored procedures:
1) sp_copyDetailsOne
/*Create a recordset of the desired items to be copied*/
CREATE PROCEDURE sp_copyDetailsOne @invoiceIdFrom INT, @outCrsr CURSOR VARYING OUTPUT AS
SELECT itemId, itemPrice, itemDescription, itemQuantity
FROM tblSalesItems
WHERE (invoiceId = @invoiceIdFrom)
OPEN @outCrsr


2) sp_copyDetailsTwo
CREATE PROCEDURE sp_copyDetailsTwo @invoiceIdFrom INT, @invoiceIdTo INT
/*Allocate a cursor variable*/
DECLARE @crsrVar CURSOR

/*Execute sp_copyDetailsOne to instantiate @crsrVar*/
EXEC sp_copyDetailsOne @invoiceIdFrom, @outCrsr = @crsrVar OUTPUT

/*Loop through the recordset and insert a new record using the new invoiceId*/
FETCH NEXT FROM @crsrVar
WHILE @@FETCH_STATUS = 0
BEGIN

/*Run the insert here*/
INSERT INTO tblSalesItems
(invoiceId, itemId, itemPrice, itemDescription, itemQuantity)
VALUES (@invoiceIdTo , 5, $25.00, N'Black T-Shirt', 30)

/*Fetch next record from cursor*/
FETCH NEXT FROM @crsrVar
END

CLOSE @crsrVar
DEALLOCATE @crsrVar


My question comes on the Insert of sp_copyDetailsTwo, as you can see the values are hard coded and I want them pulled from the cursor. However I don't know how to do this - do I need varables or can I access the cursor values directly in my VALUES clause? Or is this whole approach needing overhauled. Any advice is welcome.

Thanks

View 2 Replies View Related

Simple SSIS Package, Problems With Flat File Output

Sep 20, 2007

Hello!

I want to make a very simple package: Export all rows in a table to a flat file.
This package I can create pretty much by only using the wizards.
Now to my problems:

1) I need the output to have this format:

H20070920161522
DS3 Plastpall trippelkrage 40 1
E00000000003

H is a header post, in this case with date and time following.
D is a details post, that is all the rows that was exported.
E is and end post, containing only the number of rows in the file, including H and E posts.

2) I need to set the file name dynamically, preferably using date and time to name the file.

I´ve done this very same thing in T-SQL, like so:




Code Snippet
USE AVK
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
SELECT *
FROM tempProducts
GO
CREATE VIEW EXPORT_ORDERS
AS
SELECT 1 AS ROW_ORDER, 'H' + REPLACE(CONVERT(char(8), GETDATE(), 112) + CONVERT(char(8), GETDATE(), 108), ':', '') AS Data_Line
UNION ALL
SELECT 2 AS ROW_ORDER, 'D' + COALESCE (CONVERT(char(10), LBTyp), '') + COALESCE (CONVERT(char(50), Description), '') + COALESCE (CONVERT(char(5),
Volume), '') AS Data_Line
FROM dbo.tempProducts
UNION ALL
SELECT 3 AS ROW_ORDER, 'E' + RIGHT('0000000000' + RTRIM(CONVERT(char(13), COUNT(*) + 2)), 11) AS Data_Line
FROM dbo.tempProducts AS tempProducts_1
GO
IF @@ROWCOUNT > 0
BEGIN
BEGIN TRANSACTION
SELECT *
FROM tempProducts
DECLARE @date char(8)
DECLARE @time char(8)
DECLARE @sql VARCHAR(150)
SELECT @date = CONVERT(char(8), getdate(),112)
SELECT @time = CONVERT(char(8), getdate(),108)
SELECT @time = REPLACE(@time,':','')

DECLARE @dt char(14)
SELECT @dt = @date + '_' + @time
SELECT @sql = 'bcp "SELECT Data_Line FROM avk..EXPORT_ORDERS ORDER BY ROW_ORDER" queryout "c:AVK_' + @dt + '.txt" -c -t -U sa -P dalla'
EXEC master..xp_cmdshell @sql

--WAITFOR DELAY '0:00:10';
DELETE
FROM tempProducts

COMMIT TRANSACTION
END
DROP VIEW EXPORT_ORDERS
GO






But I´m sure it can be done in SSIS aswell, giving me some nice options for i.e. error handling aswell.
Pointers please

View 5 Replies View Related

Select With Header And Detail For Each Record In View

Sep 16, 2014

I have a flat file I need to generate, wanted to create my file from a SQL view.

Is there a way to have a Header and Detail Record for each Record in my view?

Fields would be:

Line no type period ref amt date Inv_no
0 M 1 3/3/2014
1 M Pay inv: 400.00 12345

where 0 is the header and 1 is the detail. Only certain fields will be in the header and others in the detail.

View 1 Replies View Related

How To Create A Constraint On A Header And Detail Tables?

Feb 5, 2008



Hello,

I have a header and detail table. I want to create a constraint on the detail table, based on a value it's linked to in the header table. If the bit is checked in header then a unique value is required , if it's not checked then a NULL value is acceptable.

Many thanks in advance.

View 3 Replies View Related

(Revised)Database Design Question, Header With Two Detail.. Pls Help

Jul 20, 2005

Hi All,There is some additional info I forget on this same topic I just posted.I have a database design question, pls give me some help..I want to define tables for salesman's sales target commission . Thecommission could be given per EITHER sales amount of : Group of Products ORGroup of Brand. e.g : the data example :For one salesman_A :product_1, product_2, product_3 etc.. => sales = $100 - $200 =>commission = 5%product_1, product_2, product_3 etc.. => sales = $201 - $400 =>commission = 10%Brand_A, Brand_B, Brand_C .. etc => sales = $100 - $200 =>commission = 2.5%Brand_A, Brand_B, Brand_C .. etc => sales = $201 - $400 =>commission = 5%Below is my table design, is this a good design or something is wrong here ?Thank you for your help.CREATE TABLE Sales_Commission_Header (Sales_ID Char(4) ,Sales_Commission_Group Char(4),Note Varchar(30),Constraint Sales_Commission_Header_PK Primary Key(Sales_ID,Sales_Commission_Group))Alter Table Sales_Commission_Header Add ConstraintFK_Sales_Commission_Header Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group)CREATE TABLE Sales_Commission_Detail (Sales_ID Char(4) ,Sales_Commission_Group Char(4),Sales_From Decimal(12,2) ,Sales_To Decimal(12,2) ,Commission Decimal(5,2),Constraint Sales_Commission_Detail_PK Primary Key(Sales_ID,Sales_Commission_Group, Sales_From, Sales_To))Alter Table Sales_Commission_Detail Add Constraint FK_Sales_CommissionForeign Key (Sales_ID, Sales_Commission_Group) ReferencesSales_Commission_Header(Sales_ID, Sales_Commission_Group)--------------------------------------------** ALTERNATIVE _1 :CREATE TABLE Commission_Group_Header (Sales_Commission_Group Char(4) Primary Key,Note Varchar(30))CREATE TABLE Commission_Group_Detail_Product (Sales_Commission_Group Char(4),Product_ID VarChar(10), -- This product_ID will be FKreference to master productConstraint Commission_Group_Detail_Product_PK PrimaryKey(Sales_Commission_Group, Product_ID))Alter Table Commission_Group_Detail_Product Add ConstraintFK_Commission_Group_Detail_Product Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group)CREATE TABLE Commission_Group_Detail_Brand (Sales_Commission_Group Char(4),Brand_ID VarChar(10), -- This brand_ID will be FKreference to master brandConstraint Commission_Group_Detail_Brand_PK PrimaryKey(Sales_Commission_Group, Brand_ID))Alter Table Commission_Group_Detail_Brand Add ConstraintFK_Commission_Group_Detail_Brans Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group)** ALTERNATIVE _2 :CREATE TABLE Commission_Group_Header (Sales_Commission_Group Char(4),Group_Type Char(1), -- 'B': Brand Group 'P': Product GroupNote Varchar(30),Constraint Commission_Group_Header_PK Primary Key(Sales_Commission_Group,Group_Type))CREATE TABLE Commission_Group_Detail (Sales_Commission_Group Char(4),Group_Type Char(1), -- 'B': Brand Group 'P': Product GroupProduct_Brand_ID VarChar(10),Constraint Commission_Group_Detail_PK Primary Key(Sales_Commission_Group,Group_Type, Product_Brand_ID))Alter Table Commission_Group_Detail Add ConstraintFK_Commission_Group_Detail Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group, Group_Type)The PROBLEM here is : with Product_Brand_ID , I CAN NOT make foreign keyinto both Master Product and Master Brand.So which one is better design ?split the Commission_Group_Detail into Two tables, product and brand , andmake the FOREIGN KEYto master product and master brand (previous mail)ORcombile Commission_Group_Detail for Product and Brand into one table likeaboveand NOT make any FK to master Product or Brand ?Thank you for your help,Tristant

View 1 Replies View Related

Reporting Services :: Move Detail Row Under Group Header

Nov 23, 2015

I have a report with two groups and a detail row (subtotals & totals to follow).  When I add the child (detail row) it pushes out to the right of the parent column. Is there any way to start the detail row all the way back to the left hand side of the page? I lose a lot or real estate with the group descriptions.

View 5 Replies View Related

How Place Column Header On 2 Lines In Output

Feb 12, 2002

have
select isntuser from syslogins

output

isntuser
--------
1

want

select isntuser as [Joe]+char(13) +[Blowwwwwwww] from syslogins

Joe
Blowwwwwww
--------
1

any idea ?

View 1 Replies View Related

Combine Data And Split Into Separate Txt Files For Each Header/detail Row Groupings

Mar 16, 2006

I€™ve created with the help of some great people an SSIS 2005 package which does the follow so far:
 
1)       Takes an incoming txt file.  Example txt file: http://www.webfound.net/split.txt    
 
The txt file going from top to bottom is sort of grouped like this
     Header Row (designated by €˜HD€™)
          Corresponding Detail Rows for the Header Row
           €¦..
     Next Header Row
          Corresponding Detail Rows
 
     €¦and so on  
 
       http://www.webfound.net/rows.jpg
 
2)       Header Rows are split into one table, Maintenance Detail Rows into another, and Payment Detail Rows into a third table.  A uniqueID has been created for each header and it€™s related detail rows to form a PK/FK relationship as there was non prior to the import, only the relation was in order of header / related rows below it when we first started.  The reason I split this out is so I can massage it later with stored proc filters, whatever€¦
 
Now I€™m trying to somehow bring back the data in those table together like it was initially using a query so that I can cut out each of the Header / Detail Row sections into their own txt file.  So, if you look at the original txt file, each new header and it€™s related detail rows (example of a cut piece would be http://www.webfound.net/rows.jpg) need to be cut out and put into their own separate txt file. 
 
This is where I€™m stuck.  How to create a query to combine it all back into an OLE DB Souce component, then somehow read that souce and split out the sections into their own individual txt files.
 
The filenames of the txt files will vary and be based on one of the column values already in the header table.
 
Here is a print screen of my package so far:
 
http://www.webfound.net/tasks.jpg
 
http://www.webfound.net/Import_MaintenanceFile_Task_components.jpg
 
http://www.webfound.net/DataFlow_Task_components.jpg
 
Let me know if you need more info.  Examples of the actual data in the tables are here:
 
http://www.webfound.net/mnt_headerRows.txt
http://www.webfound.net/mnt_MaintenanceRows.txt
http://www.webfound.net/mnt_PaymentRows.txt
 
Here's a print screen of the table schema:
http://www.webfound.net/schema.jpg

View 17 Replies View Related

Integration Services :: Insert Data Into Header And Detail Table From XML Through SSIS Package

Jun 2, 2015

I need to insert data into Header & Detail table. As shown in the below xml,

RecordID is identity-column and incremented by 1, after new record is saved into Header table. Need to assign the same recordID for the detail also.

Expecting output should be like as shown below:

How can we accomplish this requirement.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved