Header and Detail Data
-
Here are the specs directly from the instructions I received from the vendor and some sample data. I appreciate everyone's feedback on this!
The Input File must be a fixed format file. You must fill every position with either an appropriate character or space. Numeric fields must be right-justified and character fields must be left justified. You do not have to fill Notes fields with spaces, but every record must end with a carriage return and line feed.
--Create Tables
---Header
CREATE TABLE xHeader
(
RecordType varchar(1) NULL,
VCHR_NO varchar(9) NOT NULL,
VEND_ID varchar(12) NULL,
TERMS varchar(15) NULL,
INVC_ID varchar(15) NULL,
INVC_DT varchar(10) NULL,
INVC_AMT decimal(12, 2) NULL,
NOTES varchar(254) NULL
CONSTRAINT xHeader_PK PRIMARY KEY (VCHR_NO)
);
---Detail
CREATE TABLE xDetail
(
RecordType varchar(1) NULL,
VCHR_NO varchar(9) NOT NULL,
VCHR_LN_NO varchar(6) NOT NULL,
ACCT_ID varchar(15) NULL,
ORG_ID varchar(20) NULL,
PROJ_ID varchar(30) NULL,
CST_AMT decimal(12, 2) NULL,
TAXABLE_CD varchar(1) NULL,
SALES_TAX_CD varchar(6) NULL,
SALES_TAX_AMT decimal(12, 2) NULL,
DISC_AMT decimal(12, 2) NULL,
USE_TAX_AMT decimal(12, 2) NULL,
NOTES varchar(254) NULL
CONSTRAINT xDetail_PK PRIMARY KEY CLUSTERED (VCHR_NO ASC, VCHR_LN_NO ASC)
);
---Sample Data
INSERT INTO xHeader (RecordType,VCHR_NO,VEND_ID,TERMS,INVC_ID,INVC_DT,INVC_AMT,NOTES)
VALUES ('H','021453','C01478','NET30','47896512','2015-12-21','730.21','')
INSERT INTO xHeader (RecordType,VCHR_NO,VEND_ID,TERMS,INVC_ID,INVC_DT,INVC_AMT,NOTES)
VALUES ('H','023875','C00008','NET30','621457845','2015-12-25','1152.63','Payback')
INSERT INTO xHeader (RecordType,VCHR_NO,VEND_ID,TERMS,INVC_ID,INVC_DT,INVC_AMT,NOTES)
VALUES ('H','038159','C02587','NET30','1134287885','2015-11-15','200.54','')
INSERT INTO xDetail (RecordType,VCHR_NO,VCHR_LN_NO,ACCT_ID,ORG_ID,PROJ_ID,CST_AMT,TAXABLE_CD,SALES_TAX_CD,SALES_TAX_AMT,DISC_AMT,USE_TAX_AMT,NOTES)
VALUES ('D','021453','1','052461','6.2.3.00','789456123','730.21','A','CA1','0.00','0.00','0.00','')
INSERT INTO xDetail (RecordType,VCHR_NO,VCHR_LN_NO,ACCT_ID,ORG_ID,PROJ_ID,CST_AMT,TAXABLE_CD,SALES_TAX_CD,SALES_TAX_AMT,DISC_AMT,USE_TAX_AMT,NOTES)
VALUES ('D','023875','1','052564','6.2.3.00','789456123','384.21','A','CA1','0.00','0.00','0.00','')
INSERT INTO xDetail (RecordType,VCHR_NO,VCHR_LN_NO,ACCT_ID,ORG_ID,PROJ_ID,CST_AMT,TAXABLE_CD,SALES_TAX_CD,SALES_TAX_AMT,DISC_AMT,USE_TAX_AMT,NOTES)
VALUES ('D','023875','2','052564','6.2.3.00','789456123','384.21','A','CA1','0.00','0.00','0.00','')
INSERT INTO xDetail (RecordType,VCHR_NO,VCHR_LN_NO,ACCT_ID,ORG_ID,PROJ_ID,CST_AMT,TAXABLE_CD,SALES_TAX_CD,SALES_TAX_AMT,DISC_AMT,USE_TAX_AMT,NOTES)
VALUES ('D','023875','3','052564','6.2.3.00','789456123','384.21','A','CA1','0.00','0.00','0.00','')
INSERT INTO xDetail (RecordType,VCHR_NO,VCHR_LN_NO,ACCT_ID,ORG_ID,PROJ_ID,CST_AMT,TAXABLE_CD,SALES_TAX_CD,SALES_TAX_AMT,DISC_AMT,USE_TAX_AMT,NOTES)
VALUES ('D','038159','1','057894','6.2.4.00','11112365897','100.27','B','TX1','0.00','0.00','0.00','')
INSERT INTO xDetail (RecordType,VCHR_NO,VCHR_LN_NO,ACCT_ID,ORG_ID,PROJ_ID,CST_AMT,TAXABLE_CD,SALES_TAX_CD,SALES_TAX_AMT,DISC_AMT,USE_TAX_AMT,NOTES)
VALUES ('D','038159','2','057894','6.2.4.00','11112365897','100.27','B','TX1','0.00','0.00','0.00','')
-
Did they provide any guidance on the file-format itself (column order and width) or can we safely derive that from the table defs?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Anyway, here is a resultset with the data in a fixed-width format and you can tweak it from here. If the header and detail lines need to have different total widths you'll have some additional work to do. I'll post an iterative SSIS solution too just so you can see how that would look.
SELECT h.RecordType,h.VCHR_NO,
CAST(h.RecordType AS CHAR(1)) + --
CAST(h.VCHR_NO AS CHAR(9)) + --
CAST(h.VEND_ID AS CHAR(12)) + --
CAST(h.TERMS AS CHAR(15)) + --
CAST(h.INVC_ID AS CHAR(15)) + --
CAST(h.INVC_DT AS CHAR(10)) + --
CAST(h.INVC_AMT AS CHAR(14)) + --
CAST(h.NOTES AS CHAR(254))
FROM dbo.xHeader h
UNION ALL
SELECT d.RecordType,
d.VCHR_NO,
CAST(d.RecordType AS CHAR(1)) + --
CAST(d.VCHR_NO AS CHAR(9)) + --
CAST(d.VCHR_LN_NO AS CHAR(6)) + --
CAST(d.ACCT_ID AS CHAR(15)) + --
CAST(d.ORG_ID AS CHAR(20)) + --
CAST(d.PROJ_ID AS CHAR(30)) + --
CAST(d.CST_AMT AS CHAR(14)) + --
CAST(d.TAXABLE_CD AS CHAR(1)) + --
CAST(d.SALES_TAX_CD AS CHAR(6)) + --
CAST(d.SALES_TAX_AMT AS CHAR(14)) + --
CAST(d.DISC_AMT AS CHAR(14)) + --
CAST(d.USE_TAX_AMT AS CHAR(14)) + --
CAST(d.NOTES AS CHAR(254))
FROM dbo.xDetail d
ORDER BY h.VCHR_NO,
h.RecordType DESC;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Orlando Colamatteo (1/12/2016)
Anyway, here is a resultset with the data in a fixed-width format and you can tweak it from here. If the header and detail lines need to have different total widths you'll have some additional work to do. I'll post an iterative SSIS solution too just so you can see how that would look.SELECT h.RecordType,h.VCHR_NO,
CAST(h.RecordType AS CHAR(1)) + --
CAST(h.VCHR_NO AS CHAR(9)) + --
CAST(h.VEND_ID AS CHAR(12)) + --
CAST(h.TERMS AS CHAR(15)) + --
CAST(h.INVC_ID AS CHAR(15)) + --
CAST(h.INVC_DT AS CHAR(10)) + --
CAST(h.INVC_AMT AS CHAR(14)) + --
CAST(h.NOTES AS CHAR(254))
FROM dbo.xHeader h
UNION ALL
SELECT d.RecordType,
d.VCHR_NO,
CAST(d.RecordType AS CHAR(1)) + --
CAST(d.VCHR_NO AS CHAR(9)) + --
CAST(d.VCHR_LN_NO AS CHAR(6)) + --
CAST(d.ACCT_ID AS CHAR(15)) + --
CAST(d.ORG_ID AS CHAR(20)) + --
CAST(d.PROJ_ID AS CHAR(30)) + --
CAST(d.CST_AMT AS CHAR(14)) + --
CAST(d.TAXABLE_CD AS CHAR(1)) + --
CAST(d.SALES_TAX_CD AS CHAR(6)) + --
CAST(d.SALES_TAX_AMT AS CHAR(14)) + --
CAST(d.DISC_AMT AS CHAR(14)) + --
CAST(d.USE_TAX_AMT AS CHAR(14)) + --
CAST(d.NOTES AS CHAR(254))
FROM dbo.xDetail d
ORDER BY h.VCHR_NO,
h.RecordType DESC;
I'm pretty sure that won't actually work. UNIONs have to have at least the same number of columns.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (1/12/2016)
Orlando Colamatteo (1/12/2016)
Anyway, here is a resultset with the data in a fixed-width format and you can tweak it from here. If the header and detail lines need to have different total widths you'll have some additional work to do. I'll post an iterative SSIS solution too just so you can see how that would look.SELECT h.RecordType,h.VCHR_NO,
CAST(h.RecordType AS CHAR(1)) + --
CAST(h.VCHR_NO AS CHAR(9)) + --
CAST(h.VEND_ID AS CHAR(12)) + --
CAST(h.TERMS AS CHAR(15)) + --
CAST(h.INVC_ID AS CHAR(15)) + --
CAST(h.INVC_DT AS CHAR(10)) + --
CAST(h.INVC_AMT AS CHAR(14)) + --
CAST(h.NOTES AS CHAR(254))
FROM dbo.xHeader h
UNION ALL
SELECT d.RecordType,
d.VCHR_NO,
CAST(d.RecordType AS CHAR(1)) + --
CAST(d.VCHR_NO AS CHAR(9)) + --
CAST(d.VCHR_LN_NO AS CHAR(6)) + --
CAST(d.ACCT_ID AS CHAR(15)) + --
CAST(d.ORG_ID AS CHAR(20)) + --
CAST(d.PROJ_ID AS CHAR(30)) + --
CAST(d.CST_AMT AS CHAR(14)) + --
CAST(d.TAXABLE_CD AS CHAR(1)) + --
CAST(d.SALES_TAX_CD AS CHAR(6)) + --
CAST(d.SALES_TAX_AMT AS CHAR(14)) + --
CAST(d.DISC_AMT AS CHAR(14)) + --
CAST(d.USE_TAX_AMT AS CHAR(14)) + --
CAST(d.NOTES AS CHAR(254))
FROM dbo.xDetail d
ORDER BY h.VCHR_NO,
h.RecordType DESC;
I'm pretty sure that won't actually work. UNIONs have to have at least the same number of columns.
Should work.
3 columns in each query - unless I'm not seeing it right.
You must have missed those plus signs at the ends of the rows.
_____________
Code for TallyGenerator -
Sergiy (1/12/2016)
Jeff Moden (1/12/2016)
Orlando Colamatteo (1/12/2016)
Anyway, here is a resultset with the data in a fixed-width format and you can tweak it from here. If the header and detail lines need to have different total widths you'll have some additional work to do. I'll post an iterative SSIS solution too just so you can see how that would look.SELECT h.RecordType,h.VCHR_NO,
CAST(h.RecordType AS CHAR(1)) + --
CAST(h.VCHR_NO AS CHAR(9)) + --
CAST(h.VEND_ID AS CHAR(12)) + --
CAST(h.TERMS AS CHAR(15)) + --
CAST(h.INVC_ID AS CHAR(15)) + --
CAST(h.INVC_DT AS CHAR(10)) + --
CAST(h.INVC_AMT AS CHAR(14)) + --
CAST(h.NOTES AS CHAR(254))
FROM dbo.xHeader h
UNION ALL
SELECT d.RecordType,
d.VCHR_NO,
CAST(d.RecordType AS CHAR(1)) + --
CAST(d.VCHR_NO AS CHAR(9)) + --
CAST(d.VCHR_LN_NO AS CHAR(6)) + --
CAST(d.ACCT_ID AS CHAR(15)) + --
CAST(d.ORG_ID AS CHAR(20)) + --
CAST(d.PROJ_ID AS CHAR(30)) + --
CAST(d.CST_AMT AS CHAR(14)) + --
CAST(d.TAXABLE_CD AS CHAR(1)) + --
CAST(d.SALES_TAX_CD AS CHAR(6)) + --
CAST(d.SALES_TAX_AMT AS CHAR(14)) + --
CAST(d.DISC_AMT AS CHAR(14)) + --
CAST(d.USE_TAX_AMT AS CHAR(14)) + --
CAST(d.NOTES AS CHAR(254))
FROM dbo.xDetail d
ORDER BY h.VCHR_NO,
h.RecordType DESC;
I'm pretty sure that won't actually work. UNIONs have to have at least the same number of columns.
Should work.
3 columns in each query - unless I'm not seeing it right.
You must have missed those plus signs at the ends of the rows.
I'm must be very slowly but surely going blind. The trailing "+" signs didn't even register with me tonight. Thanks for the correction.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Where it will have trouble is if the header and detail lines need to have different lengths which is why I asked the OP for the file-descriptor info.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Attached is a zip file containing three files:
1. TestData.sql is a script that will build two tables and some test data in tempdb
2. SSIS Package (InterleaveHeaderAndFooter.dtsx file) that will interleave the header and footer lines from the tables in tempdb into a single flat-file.
3. Data.txt, a ample of the flat-file produced by the SSIS Package using the test data. Note that the header lines are of different length than the detail lines.
I tried to build this Package in SSIS 2005 so anyone with any version of SSIS installed could play however my attempts were dashed when I lost network connectivity to a certain VPN. As such, the Package is built using SSIS 2016 (still in CTP) which is what is on my Surface Pro at the moment. You will need to install SQL Server Data Tools for SQL Server 2016 Preview to open the Package and give it a spin.
Here is a screenshot of the Control Flow:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Orlando Colamatteo (1/12/2016)
Where it will have trouble is if the header and detail lines need to have different lengths which is why I asked the OP for the file-descriptor info.Agreed. A record layout by column position and length would be incredibly helpful here, unless that's the intent of the OP's previous post with the "tables".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (1/13/2016)
Orlando Colamatteo (1/12/2016)
Where it will have trouble is if the header and detail lines need to have different lengths which is why I asked the OP for the file-descriptor info.Agreed. A record layout by column position and length would be incredibly helpful here, unless that's the intent of the OP's previous post with the "tables".
Matching the actual requirements might be relevant for the OP to reach a production solution but we can still talk techniques. My SSIS Package demonstrates a specific technique to build a multi-record file format and can easily be modified to fit specific requirements.
I have a few ideas on how this could be done using T-SQL and cmd-shell stuff but am curious to know how you might set out to meet a requirement where the lines in the file must have different total lengths using your chosen tools and techniques?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Sorry, I am just now getting back to this project. I have been on loan to another department for a hot project.
I do not see anywhere in the vendor documentation for total column width. Just the definition for each column format, which is included in the sample tables. I am going to work through the options provided and see which works best. I will give feedback as soon as I complete.
Thanks again for all the help and I apologize for the time gap in responding!
-
I ran into several errors, which are attached. My data actually has some decimal columns, which throws off the User::Header variable. If I was smart I would have provided other data types in my sample. Anyway if I change the variable to Object it will run, but it does not interval the header and details and it repeats itself several times. I also notice it is not keeping a fixed width on my columns. For example the VCHR_NO should be a fixed width of numeric(9), but on the advanced tab of the FFCM it shows an InputColumnWidth of 45. I tried changing it, but it does not keep the change. I also tried changing on the Advanced editor of the source OLEDB, but no luck there as well. I will keep plugging away to see if I can get this to work. Thanks for all the help!
-
If you post sample tables in the form of CREATE TABLE statements, sample data in the form of INSERT INTO statements and the SSIS Package you are working with I'll have a look.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Attached is the new sample data and SSIS project I mimicked using your example. I appreciate all the help on this!
-
bpowers (5/2/2016)
Attached is the new sample data and SSIS project I mimicked using your example. I appreciate all the help on this!Luckily it was a minor issue. When you select the set of VCHR_NO values in "Get List of Headers" they are returned as NUMERIC(9,0) per the column type. You are then mapping those values to a variable of type String in the For Each Loop Container therefore SSIS expects the values in the set to be of type String. All I did was change the SQL statement in "Get List of Headers" to cast the numeric column to a VARCHAR(9) and the Package works great now:
SELECT CAST(VCHR_NO AS VARCHAR(9)) AS VCHR_NOEDIT: reduce picture size
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply