November 24, 2014 at 11:11 am
This is urgent please.
Use SSIS
flat file sample content (use attachement, sampleFF),
ABC 8585
TRANID
A4145
CCA
96
FVA
78
ABO 8500
IBD 8585
FBD 8585
TRANID
A4146
CCA
90
FVA
71
ABO 8110
requirement .
if a rowline contains "CA" or "VA"
extract else no , unless it is tranID which ties the above two fields as follow:
IDfieldName FieldvalueTranID(see attachment Sampleresult.jpg)
1CCA96A4145
2FVA78A4145
3CCA90A4146
4FVA71A4146
Note: fieldvalues in the flat file are always on one row below the fieldnames and position starts at 7(for those fields that contains , CA,VA or TranID).
for the sake of Performance , Cannot import data to database and manupilate DB side , file is too big, may be 12million rows and fields that are required are those with CA , VA and tranIDs.
November 24, 2014 at 11:54 am
use a Conditional Split Transformation on the Data Flow. Look for your data there.
Gerald Britton, Pluralsight courses
November 24, 2014 at 11:54 am
You aren't going to be able to use a standard SSIS Flat File Source for this, you are going to have to do scripting to read the file row-by-row. Somthing like this psuedocode:
Open File
Read First Row
IF TRANID Then read the next row and store value in variable
Read next row if contains "CA" or "VA" store value in variable
Read next row and store in value in variable
Write stored values to Output
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 24, 2014 at 11:54 am
OK, I think I understand what you want. But what is your question?
--Edit: Jack's on to something. Use an asynchronous Script Component for this.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 24, 2014 at 1:34 pm
Jack , Would you direct me to a resource I can refer to or yourself may write a bit of code sample to that I can expand?
November 24, 2014 at 2:45 pm
Quick suggestion as this isn't as complex as it seems at first, import the file's content into a staging table, one line per row and group/parse it from there.
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID('dbo.TBL_LINE_IMPORT') IS NOT NULL DROP TABLE dbo.TBL_LINE_IMPORT;
CREATE TABLE dbo.TBL_LINE_IMPORT
(
LIMP_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_LINE_IMPORT_LIMP_ID PRIMARY KEY CLUSTERED
,LIMP_LINE VARCHAR(250) NOT NULL
);
/* The file content imported line by line */
INSERT INTO dbo.TBL_LINE_IMPORT (LIMP_LINE)
VALUES
('ABC 8585')
,('TRANID ')
,(' A4145')
,('CCA ')
,(' 96')
,('FVA ')
,(' 78')
,('ABO 8500')
,('ABD 8585')
,('TRANID ')
,(' A4146')
,('CCA ')
,(' 90')
,('FVA ')
,(' 71')
,('ABO 8110');
;WITH BASE_GROUP AS
(
SELECT
LI.LIMP_ID
FROM dbo.TBL_LINE_IMPORT LI
WHERE LTRIM(RTRIM(LI.LIMP_LINE)) = 'TRANID'
UNION ALL
SELECT MAX(LI.LIMP_ID) + 1 AS LIMP_ID
FROM dbo.TBL_LINE_IMPORT LI
)
,NUMBERED_GROUP AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY (SELECT NULL)
ORDER BY (SELECT NULL)
) AS BG_RID
,BG.LIMP_ID
FROM BASE_GROUP BG
)
,PARSE_GROUP AS
(
SELECT
NG.BG_RID
,NG.LIMP_ID AS START_ID
,LG.LIMP_ID - 1 AS END_ID
FROM NUMBERED_GROUP NG
INNER JOIN NUMBERED_GROUP LG
ON NG.BG_RID = LG.BG_RID - 1
)
,COMBINED_SET AS
(
SELECT
LI.LIMP_ID
,PG.BG_RID
,LTRIM(RTRIM(LI.LIMP_LINE)) AS LI_LINE
,LTRIM(RTRIM(L2.LIMP_LINE)) AS L2_LINE
FROM dbo.TBL_LINE_IMPORT LI
LEFT OUTER JOIN dbo.TBL_LINE_IMPORT L2
ON LI.LIMP_ID = (L2.LIMP_ID - 1)
CROSS APPLY PARSE_GROUP PG
WHERE LI.LIMP_ID BETWEEN PG.START_ID AND PG.END_ID
)
,FINAL_SET AS
(
SELECT
CASE
WHEN CS.LI_LINE = 'CCA' THEN CS.LI_LINE
WHEN CS.LI_LINE = 'FVA' THEN CS.LI_LINE
END AS fieldName
,CASE
WHEN CS.LI_LINE = 'CCA' THEN CS.L2_LINE
WHEN CS.LI_LINE = 'FVA' THEN CS.L2_LINE
END AS Fieldvalue
,MAX(CASE WHEN CS.LI_LINE = 'TRANID' THEN CS.L2_LINE END) OVER (PARTITION BY CS.BG_RID) AS TranID
FROM COMBINED_SET CS
)
SELECT
FS.fieldName
,FS.Fieldvalue
,FS.TranID
FROM FINAL_SET FS
WHERE FS.fieldName IS NOT NULL;
Results
fieldName Fieldvalue TranID
---------- ----------- -------
CCA 96 A4145
FVA 78 A4145
CCA 90 A4146
FVA 71 A4146
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply