Import damages Flat File Connection Manager

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Import damages Flat File Connection Manager

Import damages Flat File Connection Manager

Rate Topic

Display Mode

Topic Options

Author

Message

Alan Spillert

Posted Friday, January 23, 2009 8:03 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:17 PM
Points: 163, Visits: 384

I am converting a number of SS00 DTS packages in my ETL system to SS05 SSIS. They copy a flat file as text fields into a load table, which a stored procedure then processes. The SSIS Flat File Connection Manager needs to copy the fields into char fields in the load table, but many of the fields are predominantly numbers. When I move the package it seems to be reverting the data to a different datatype, usually DT_I4 (4-byte signed integer) based on scanning the first 200 records. But a few of the thousands are garbage, and the DT_I4 will not load to a char(6) field.

Can I "lock" the connection manager so that SSIS will not change? How can I keep the package stable when moving between systems? Or is there another approach?

Thank you for your help.

Post #642477

Martin Schoombee

Posted Friday, January 23, 2009 8:29 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, April 18, 2013 6:20 AM
Points: 317, Visits: 3,605

Hi,

I don't fully understand what you mean by "moving between systems"...maybe you can elaborate.

Aside from that, the connection manager has properties for every column and you can specify explicitly what the datatype for every column is.

Post #642499

Alan Spillert

Posted Friday, January 23, 2009 1:37 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:17 PM
Points: 163, Visits: 384

I created the packages on my desktop using BIDS and saved them to a server, using "save as". Evey column was defined, with name, length, and a type of DT_STR. When importing the package to another server, using BIDS on the server, the datatype and length gets clobbered.

I'm struggling with managing packages. I've seen little of use written about all the XML files produced, how to manage them, move them, use one VSS folder from a two different platforms. Very frustrating when a few hours work is destroyed trying to save a package.

Thanks for your time and any help -

Post #642745

« Prev Topic | Next Topic »

Permissions