Help needed in building SSIS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Help needed in building SSIS

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2007-07-18 : 05:05:09

I am trying to import part of the following file structure into a SQL2005 table
The 1st 10 records are not needed (HEADER DATA), neither are the records that are from ‘TRAILER RECORD’
Can anyone assist me in designing a Simple SSIS to import this file to a table
The LedgerKey will be from a Global variable
Text within <> are for information and NOT contained in the file
In this file there are three records to be imported, but in other files thjere can be upwards of 20,000 records

Table
CREATE TABLE [dbo].[TempTransactions](
[LedgerKey] [varchar](20) NOT NULL,
[Licence] [varchar](6) NOT NULL,
[DestSortCode] [varchar](6) NOT NULL,
[DestAccountNumber] [varchar](8) NOT NULL,
[DestAccountName] [varchar](18) NULL,
[TransValue] [varchar](11) NOT NULL,
[DestBankRef] [varchar](18) NULL,
[TransCode] [varchar](2) NOT NULL,
[tranref] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

File
< HEADER DATA NOT REQUIRED >
"Header Data"
"Header Data"
"Header Data"
"Header Data"
"Header Data"
"Header Data"
"Header Data"
"Header Data"
"Header Data"
"Header Data"
< HEADER DATA NOT REQUIRED >
"11111122222222TEST ACCOUNT 1 BANK REG 0000000020017"
"33333344444444TEST ACCOUNT 2 BANK REG 0000000150017"
"55555566666666TEST ACCOUNT 3 BANK REG 0000000030017"
<FIXED FIELDS LENGTHS 6,8,18,18,11,2 >
<TRAILER RECORD NOT REQUIRED >
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
<TRAILER RECORD NOT REQUIRED >

tmitch
Yak Posting Veteran

60 Posts

Posted - 2007-07-23 : 21:33:24

If there is a discernable pattern to the rows that should be discarded (for example, a distinct substring within each), you can use the Conditional Split to filter out rows that should be ignored.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources