data not flowing out of flat file source

my package has a flat file source that should be extracting data from a text file passing the data to the next component in the data flow. the package validates fine, but the data isn't flowing. however, i see the data in the source component. i added a data viewer between the source and the next component to see if any data flowed and saw no data. can someone suggest how i should go about trying to debug this? thanks.Look at the output window. Anything of interest there?|||

Could you give more details about the flat file format and how you configured the flat file connection manager?

Thanks.

|||

DarrenSQLIS wrote:

Look at the output window. Anything of interest there?

below are some output lines that interest me:

Information: 0x40043007 at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Lockbox Validate File and Header Info, Flat File Lockbox [1]: The processing of file "c:\casestudy\lockbox\samplelockbox.txt" has started.
Information: 0x400490F4 at Data Flow Lockbox Validate File and Header Info, Lookup BankBatchID [373]: component "Lookup BankBatchID" (373) has cached 0 rows.

"Flat File Lockbox [1]" is the flat file source component. the flat file connection manager is connected to file "c:\casestudy\lockbox\samplelockbox.txt"|||

Bob Bojanic wrote:

Could you give more details about the flat file format and how you configured the flat file connection manager?

Thanks.

below is the entire contents of the text file:

H080105 B1239-99Z-99 0058730760
I4001010003 181INTERNAT
C4001010004 01844400
I4002020005 151METROSPOOO1
C4002020006 02331800
I4003030009 MAGIC CYCLES
C4003030010 02697000
I4004040013 LINDELL
C4004040014 02131800
I4005040017 151GMASKI0001
C4005040019 01938800

the general tab of the flat file connection manager is configured as follows:

file name: c:\casestudy\lockbox\samplelockbox.txt
locale: english (united states)
unicode: unchecked
code page: 1252 (ANSI - Latin I)
format: ragged right
text qualifier: <none>
header row delimiter: {CR}{LF}
header rows to skip: 0
column names in first data row: unchecked|||

Can you try this?

In a copy of your package, delete everything after the flat file source.

Add a row count component after the Flat File Source (see http://msdn2.microsoft.com/en-us/library/ms141136(SQL.90).aspx for the row count component)

Now you can run the data flow with nothing but the flat file - if data flows to the row count, then your source is ok.

The reason I ask is becuase I see that your lookup component cached 0 rows - I wonder if that is actually the issue. Lookup row caching occurs on pre-execute so if there is a problem there, your flat file source will never even get started.

Donald Farmer

|||donald,

i took your suggestion and copied my package. then, i deleted everything from the data flow task except a flat file source and a rowcount. i then ran this package and the data still wasn't flowing out of the flat file source. next, i deleted my data flow task and created a new data flow task. then, i added a flat file source and a rowcount to this new data flow. low and behold, that resolved the issue. it seems that my original data flow somehow became corrupted and that this prevented the data from flowing out of the flat file source. to me, this seems to be a bug. is there a way to repair my original data flow so that it won't be necessary for me to duplicate all of my previous work?|||

Well that's odd, for sure. You can select, copy and paste components from data flow to data flow, so you could try copying and pasting the rest of your original data flow into your new one and hooking it up. There will be messages about metadata needing fixed up, but it all is effectively identical it should be relatively easy to do so.

(Copy and back up that new data flow first of course.)

I don't really have any suggestions about what could have gone wrong. I do wonder if the new data flow is identical in all ways to the old one, but difficult to tell without examining them in detail.

Donald

|||

Donald Farmer wrote:

Well that's odd, for sure. You can select, copy and paste components from data flow to data flow, so you could try copying and pasting the rest of your original data flow into your new one and hooking it up. There will be messages about metadata needing fixed up, but it all is effectively identical it should be relatively easy to do so.

(Copy and back up that new data flow first of course.)

I don't really have any suggestions about what could have gone wrong. I do wonder if the new data flow is identical in all ways to the old one, but difficult to tell without examining them in detail.

Donald

donald,

you were correct about there being messages about metadata needing fixed up. below are the messages:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task [DTS.Pipeline]: input column "line" (158) has lineage ID 28 that was not previously used in the Data Flow task.

Error at Data Flow Task [DTS.Pipeline]: "component "Derived Column Checks 1" (156)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

you previously stated that fixing this should be relatively easy to do so. so, how should i go about fixing this?|||

There should be a warning triangle in the components that need to be fixed up. Double click on those components to open the UI and the metadata may be fixed automatically, or you will be prompted with a mapping dialog to fix up the changes.

Donald

|||

Donald Farmer wrote:

There should be a warning triangle in the components that need to be fixed up. Double click on those components to open the UI and the metadata may be fixed automatically, or you will be prompted with a mapping dialog to fix up the changes.

Donald

ok, that worked. thanks for your assistance.

Data Load Query

Hi,

I'm extracting data from a mainframe application with a view to loading
it into a MS SQL database. I'm trying to determine the most efficient
way to format the mainframe extract file to make loading into the
database easier.

The problem I have is that the existing record structure includes an
array that can vary between 1 to 50. If I include this array in a
single record the table I use to import the data would need 50 columns
though not all these would be populated. There is a field in the record
to identify how many occurances of the array there are.

Current Record Structure :
Account Number
Account Name
Other Account Details
TotalNumberOfArrayFieldsPopulated
Array :
Value1
Value2
Value3
...
up to Value50 (if required)

i.e.

12344,Mr Agent,$29.95,2,BX123,BX124
12345,Mr Jones,$14.95,3,XX123,XX124,XX125
12345,Mr Jones,$14.00,1,XY123
12345,Mr Jones,$15.95,2,XZ124,XZ125
12346,Mr Smith,$19.95,3,AX123,AX124,AX125
12346,Mr Smith,$19.00,1,BY123
12347,Mr Acant,$99.95,7,CX123,CX124,CX125,CX126,CX127,CX128 ,CX129

There may be up to 3 records created for each Account Number with
different values in the array fields.

Am I better to break this file into two files .. one with the core
customer information and a second file with a row for each array value
which has a link to the customer information file.
Or
Is there a way to efficiently process the original file once it is
loaded into the staging tables in the database ?

i.e.

File 1 - Core Customer Information
====================================
Current Record Structure :
Record Number
Account Number
Account Name
Other Account Details
TotalNumberOfArrayFieldsPopulated

File 2 - Array Information
====================================
Record Number
Array :
Value1
Value2
Value3
...
up to Value50 (if required)

File 1
========================
12344,Mr Agent,$29.95,2
12345,Mr Jones,$14.95,3
12345,Mr Jones,$14.00,1
12345,Mr Jones,$15.95,2
12346,Mr Smith,$19.95,3
12346,Mr Smith,$19.00,1
12347,Mr Acant,$99.95,7

File 2
========================
12344,BX123
12344,BX124
12345,XX123
12345,XX124
12345,XX125
12345,XY123
12345,XZ124
12345,XZ125
12346,AX123
12346,AX124
12346,AX125
12346,BY123
12347,CX123
12347,CX124
12347,CX125
12347,CX126
12347,CX127
12347,CX128
12347,CX129

At times the individual array values will be used for look ups though
essentially the Customer Information record will be the primary lookup
data.

I'm leaning toward changing my COBOL code and creating the 2nd output
unless someone can suggest a simple way to process the information once
loaded into the table.

Any help that could be suggested would be greatly appreciated.For a varying number of fields per record, you might consider XML, as
it's a good format for that kind of data. But I have to say that I have
very limited experience of importing XML data into MSSQL myself - check
out OPENXML in Books Online, the SQLXML tools from Microsoft (which
include an XML bulk load COM component), and you could also post in
microsoft.public.sqlserver.xml to get some feedback on that approach.

Simon