Hi all,

About a year ago when I upgrade our server from sql 2000 to 2005.
I created a coupld ssis packages via SQL Server 2005 Import and Export Wizard.
I remember I spent quite some time to figure out how to map data from a flat
file to sql server data table.

Now I need to do the similar thing since our table schema is changed and I
need to remap data from a flat file to the changed table. I encoutered the
similar problem but I was not able to solve it quickly this time. I am
dealing with the production issue.
The error message is something like this:
///////////////
• Error 0xc020901c: Data Flow Task: There was an error with input column
"SpouseBirthday" (349) on input "Destination Input" (263). The column status
returned was: "The value could not be converted because of a potential loss
of data.".
(SQL Server Import and Export Wizard)

• Error 0xc0209029: Data Flow Task: The "input "Destination Input" (263)"
failed because error code 0xC0209077 occurred, and the error row disposition
on "input "Destination Input" (263)" specifies failure on error. An error
occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
/////////////
I noticed that the first record of spouseBirthday from souce file is empty(
I have a lot of empty date fields in source file), while my desitnation table
require datetime field. It seems that it complains about the empty field
cannot be converted into datetime datatype. But somehow I did last year.
I just don't remember how I mapped data from source file to destination
table column by column.

Can you shed a light what I should do?
or is it possible to find the mapping from my old ssis packages saved last
year(I save it into ssis when went through the import wizard.)
I don't understand what do those numbers(349)(263) mean.

thank you,

Betty


--
Betty

I forgot to put this:

It also has something like this:
An OLE DB record is available. Source: "XXXXXXXXXXXX" Hresult:
XXXXxXXXXXX Description: "Invalid character value for cast specification".

I just don't remember exactly.

--
Betty


"c676228" wrote:

Hi Betty,

Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.

From your description, I understand that you would like to use SSIS package
to transfer the data from flat file to sql server database.
If I have misunderstood, please let me know.

based on the error message, it is possible that the data type in the flat
file doesn't match for SQL Server tablem. Generally, The columns in flat
file need to be converted to correct data types in SQL Server before
inserting them into the SQL table. Being strongly typed, SSIS does not
accept auto conversion for some data type. For example conversion from text
to datetime.

according to your description, SpouseBirthday seems to be a data. So we
need to manually conversion it from text to datetime before inserting them
into the SQL table. For your convenience, I make en example here. Since the
error meesage is not totally captured, the following command might not be
perfect for you scenario.

ISNULL([SpouseBirthday]) || Len([SpouseBirthday]) < 2 ?
NULL(DT_DBTIMESTAMP):
(DT_DBTIMESTAMP)(substring([SpouseBirthday],9,2) + "-" +
substring([SpouseBirthday],6,2) +
"-" + substring([SpouseBirthday],1,4))

References
===========
Data Conversion Transformation
<http://msdn.microsoft.com/en-us/library/ms141706.aspx>

How to: Convert Data to a Different Data Type Using the Data Conversion
Transformation
<http://msdn.microsoft.com/en-us/library/ms140321.aspx>

Execute DTS 2000 Package Task
<http://msdn.microsoft.com/en-us/library/ms137907.aspx>

Date Conversion - Flat File - YYYYMMDD
<http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1884269&SiteID=1>

How to: Derive Column Values Using the Derived Column Transformation
<http://msdn.microsoft.com/en-us/library/ms137630.aspx>

Integration Services Data Types
<http://msdn.microsoft.com/en-us/library/ms141036.aspx>

Mapping Data Types in the Data Flow
<http://msdn.microsoft.com/en-us/library/ms345165.aspx>

NULL (SSIS)
<http://msdn.microsoft.com/en-us/library/ms141758.aspx>

If there is anything unclear, please do not hesitate to let me know.

Have a nice day.

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subs.../aa948874.aspx

================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ======

Hi Betty,

I am just writing in to see if you have obtained the opportunity to perform
the troubleshooting steps. If anything is unclear with the previous
information I've provided to you, please don't hesitate to let me know.

I appreciate your time and look forward to hearing from you.

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======