chandrala


Hi,

I have a situation where a tab limited text file is used to populate a sql server table.

The tab limited text file comes from a third party vendor. There are fixed number of columns we need to export to the sql server table. However the third party may add colums in the text file. Whenenver the text file has an added column (which we dont need to import) the build fails since the flat file connection manager does not create the metadata for it again. The problem goes away  where I press the button "Reset Columns" since it builds the metadata then. Since we need to build the tables everyday we cannot automate it using SSIS because the metadata does not change automatically. Is there a way out in SSIS




Re: Flat File Connection manager throws error when a column gets added to the flat file

Duane Douglas


The flat file source won't work because the metadata isn't consistent.

A workaround would be to either use a script component source or create a custom source component.







Re: Flat File Connection manager throws error when a column gets added to the flat file

chandrala

I dont think we can use a script component but I guess we could use a custom source component. Do you have an example for a text source because I dint find any in msdn for text file.






Re: Flat File Connection manager throws error when a column gets added to the flat file

Duane Douglas

 chandrala wrote:

I dont think we can use a script component but I guess we could use a custom source component. Do you have an example for a text source because I dint find any in msdn for text file.

Sorry, but I don't have such an example. However, The Rational Guide to Scripting SQL Server 2005 Integration Services has some sample code for a script source component that you might be able to use in a custom component.




Re: Flat File Connection manager throws error when a column gets added to the flat file

Jamie Thomson

Duane Douglas wrote:

The flat file source won't work because the metadata isn't consistent.

A workaround would be to either use a script component source or create a custom source component.

You may also want to look into using a Ragged Right format for the file.

-Jamie






Re: Flat File Connection manager throws error when a column gets added to the flat file

Duane Douglas

Jamie Thomson wrote:
Duane Douglas wrote:

The flat file source won't work because the metadata isn't consistent.

A workaround would be to either use a script component source or create a custom source component.

You may also want to look into using a Ragged Right format for the file.

-Jamie

Maybe I'm overlooking something obvious, but how would using a ragged right format solve the metadata issue Aren't the number of columns consistent ragged right format