Error Exporting FoxPro 2.5 DOS table to SQL Server

Using the Import/Export Data Wizard, I'm trying to export a FoxPro 2.5 DOS (as dBase III) table of 15,000 records to SQL Server 2000. I keep getting this error message:
Insert Error, Column 32 ('PROG_START',DBTYPE_DBTIMESTAMP), Status 6: Data Overflow.
Invalid character value for cast specification.

I have SQL Server create the table each time I run the wizard. The new table allows NULLS in this column and I made sure to overwrite the empty date fields in the FoxPro table with blanks to make sure it would result in NULL. Originally SQL Server tried to put this as SMALL DATETIME, but when I got the message earlier, I changed it to DATETIME.

Any suggestions?hi

1) can you run a select in the fox tables, for example:
select min(col), max(col) from myTable

2) do you use any function to transform the fox date to sql date?

TIA
Abel.|||My first guess would be that you've got some data for 02/29/00, which Fox mis-interprets at 1900-02-29 (which it shouldn't accept, but it did).

If that isn't correct, the only thing I could offer would be that if you'll post the DBF (and any associated NDX, NTX, or CDX) files, I'd be willing to take a shot at converting them tonight.

-patP|||The problem is that it isn't always the same record that I'm getting the error. I'm getting this error with different tables I'm trying to convert and they all have numerous records with blank Date fields that are causing the problem.
Pat, thank you for your offer. I can try to come up with a sample data set to send to you or give you one of the smaller tables that give this error. Which would you prefer.

Error converting data type DBTYPE_DBTIMESTAMP to datetime

I'm getting the error Error converting data type DBTYPE_DBTIMESTAMP to datetime when I try to import a date field from Oracle to SQL Server.

SELECT CAST(cancel_dt as datetime) FROM OPENQUERY(orcldb, 'SELECT cancel_dt FROM tablename WHERE id= 12345')

The date in Oracle is 19-JUN-99, but it's coming over as 06/19/0999 and it throws the error because the year 999 is invalid in SQL Server. Any ideas on why Oracle or SQL server would convert the year to 0999 instead of 1999?
This is also happening on a value 24-SEP-07 coming over as 09/24/0207

Most other date values work, so I'd really like to get in depth into the process that occurs when converting to a SQL Server datetime.Why not use the conversion function in Oracle to produce a 4 digit year?|||I found the problem to be that SQL Server only accepts dates from January 1, 1753 through December 31, 9999 whereas Oracle allows dates January 1, 4712 BC to December 31, 9999 AD.

So these dates that are coming over with years of 999 or 207 produce an error because they are invaild in SQL Server.|||That's correct...

M$ figured that because the calendar was adjusted in that century..(they lost several days) that the date function would produce invalid results...think DATEDIFF, DATEADD, ect

lunacy

anyway...your dates are not 0999

Oracle has the ability to produce the dates you need. The date formatting function in Oracle are far superior to SQL Server...

Just return that date as a varchar in your select, then convert it...|||So what is the actual 4-digit year representation for 19-JUN-99? Is it 06/19/0999 or 06/19/1999?
And what about 24-SEP-07? 09/24/0207 or 09/24/2007?

If the latter is the answer to both then it's not SQL Server, it's how you bring the data in and how much guessing you want SQL Server to do. You also need to check "two digit year cutoff option".|||MY problem with the dates is that 19-JUN-99 is supposed to be 1999, but somehow this date was loaded as June 19, 999 instead. So it is coming over correctly. There's just some data cleaning to be done.

This is what happens when you use someone elses data :mad:|||These 2 calls produce the right result:

select cast('24-SEP-07' as datetime) --09/24/2007
select cast('19-JUN-99' as datetime) --06/19/1999|||Check how you set up the linked server to Horacle. See if there is any datetime/currency setting that can affect this.

Error converting data type

Hello,
I got error when i trying to import data from foxpro table to sql:
SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft Visual
FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select record_dt from
[MY.DBF]')
Server: Msg 8114, Level 16, State 10, Line 1
Error converting data type DBTYPE_DBDATE to datetime.
how to fix? Thanks.I don't know Foxpro; is there some way you can convert the record_dt column
into, perhaps, a string in the OPENROWSET, then convert the column to a
DATETIME back on the SQL Server side? It's just a matter of incompatible
formats...
Something like:
SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft Visual
FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select
CONVERT_TO_STRING(record_dt) from
[MY.DBF]')
Replace CONVERT_TO_STRING with however you convert to a string in Foxpro.
By the way, the safest format to use for dates in SQL Server is the ISO
format: YYYYMMDD HH:MM:SS.MS
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"js" <js@.someone@.hotmail.com> wrote in message
news:O2WDB6L5FHA.3908@.tk2msftngp13.phx.gbl...
> Hello,
> I got error when i trying to import data from foxpro table to sql:
> SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft Visual
> FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select record_dt from
> [MY.DBF]')
> Server: Msg 8114, Level 16, State 10, Line 1
> Error converting data type DBTYPE_DBDATE to datetime.
> how to fix? Thanks.
>
>|||Apparently some of the records are not in datetime format -
try to add where isdate(trade_dt)= 1
SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft
Visual
FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select record_dt from
[MY.DBF]') where isdate(trade_dt)= 1
to see the offending records run the following :
SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft
Visual
FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select record_dt from
[MY.DBF]') where isdate(trade_dt)= 0|||Thanks for the help.
Got this error:
Could not execute query against OLE DB provider 'MSDASQL'.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandText::Execute returned
0x80040e37].
I found some values are '1899-12-30 00:00:00.000', how to fix this in a
query FoxPro can run?
<hussein.safa@.gmail.com> wrote in message
news:1131498021.441399.109230@.g44g2000cwa.googlegroups.com...
> Apparently some of the records are not in datetime format -
> try to add where isdate(trade_dt)= 1
> SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft
> Visual
> FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select record_dt from
> [MY.DBF]') where isdate(trade_dt)= 1
> to see the offending records run the following :
>
> SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft
> Visual
> FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select record_dt from
> [MY.DBF]') where isdate(trade_dt)= 0
>

Error by converting string to decimal

Hi,

I'm trying to import a text-file into SQL2005 by using SSIS. Unfortunately it doesn't seem to work in the way I thought. Because I'm using the german version of SQL2005 I need to translate the items of my toolbox, so dont wonder if their names are not exactly the same as in the english versions.

Try number1:

a) My datasource is a flatfile, in the connection manager I changed the DataType for the input column to DT_DECIMAL. In my destination table the data type of ths column is decimal(20,4). Name of the column is "NXT_PUT_PX".

b) Running this package results in this error:

"Fehler bei der Datenkonvertierung. Die Datenkonvertierung für die NXT_PUT_PX-Spalte gab den Statuswert '2' und den Statustext 'Der Wert konnte aufgrund eines m?glichen Datenverlustes nicht konvertiert werden.' zurück. Fehlercode: 0xC0209084. "

Translation of this is something like:

Error by converting data. Converting data for column NXT_PUT_PX returned state '2' and text 'Value couldn't be converted because of a potential loss of data'.

Try number2:

a) same as try number1

b) Adding a datatransformation-task (Datenkonvertierung), and set NXT_PUT_PX to be converted to DT_DECIMAL wirh SCALE 20.

c) Assigning this converted column to the destination column.

d) Running the package results in the same error as described above.

Try number3:

Same as try number 2 but the input column for my flatfile is now DT_WSTR with width 50.

Running this results in the same error message as above, but different errorcode. Errorcode is 0xC020907F.

Any hints how to do this conversion correct?

Best regards,

Jan Wagner

Without looking at the data its difficult to say.

You should try and find the offending data. Following what you did in your "try number 1", put an error output on the flat file source adapter. This error output will contain all values that failed the conversion. You can then look at the failed data either in a data viewer or a flat file destination and we can then work out why the conversion is failing.

-Jamie

|||

Hi Jamie,

thanks again for your fast answer.

Did add a error output as flatfile.

The "errorflatfile" contains all rows of my test inputfile which didn't had a value in column NXT_PUT_PX. I'm wondering about that, because I allowed this column to be NULL and cannot see why there could be potential data loss.

Any ideas?

Regards,

Jan

|||

It sounds as tho you are on the right track.

What do you mean you allowed the column to be NULL? There is no concept of nullability in the SSIS pipeline (because NULLs are always allowed) so I assume you mean you specified columns to be NULLable on the destination but that is irrelevant because the erros occur before the data even gets to the destination.

It sounds as though the problem is happening during the parsing in the flat file source adapter. For that reason, I would import the data as a string and then parse it out yourself using a derived column expression, making sure that you check for NULLs (or empty strings) and handling them accordingly.

-Jamie

|||

You're right, I meant NULLs allowed in my destination table.

I tried handling the column with a derived column, so I now got a new error...

This is my statement for the derived column:

ISNULL(NXT_PUT_PX) ? NULL(DT_DECIMAL,20) : (DT_DECIMAL,20)NXT_PUT_PX

The new error is: 0xC0049063. "Fehler beim Bedingungsvorgang". Something like error in condition.

What I try to do with this statement is:

IF NXT_PUT_PX == NULL

THEN CREATE A NEW DT_DECIMAL_NULL

ELSE

CONVERT NXT_PUT_PX TO DT_DECIMAL

In german we would say "Hier ist der Wurm drin". ;-)

Regards,

Jan Wagner

|||

Your expression seems correct for what you want to do.

My suspicion would be that the failure is occuring on the conversion to DT_DECIMAL in the non-null cases. It might be as simple as a value that cannot fit in scale 20, or maybe there are spaces you need to trim or replace? My suggestion would be to configure the error output and send the failing rows to it, and inspect the data to see if that gives you any clues.

Mark

|||

Hmmm, not sure. Looks OK to me!

Keep trying :)

Sorry I can't be of more help!!

-Jamie

|||

So,

here is how it now works in my package:

1. I declared all input source columns which were including decimals as strings as DT_WSTR(30).

2. Replaced all "." with "," in "decimal" columns (thats specific for me, because here in germany we use "," as separator, but my input data come with ".").

3. Converting these columns to DT_R8.

4. Inserting into table with float as datatype for these columns.

5. Seems to work!

Thanks for all your help and ideas.

Best regards,

Jan

|||

You might be able to optimize that a litte and get rid of the explicit string replacement of "." with "," by setting the locale ID on the conversion component to a locale that uses "." for decimal place. Or better yet, set the locale ID on the flat file source component, and set the column type to DT_R8 right there.

Of course, if you have other columns you are reading that require the locale ID to be set, this might not work for you...

|||

Hi Mark,

when I set the locale ID, I get a error message that says that this locale id is not installed on my computer. I will try this again in one of the next packages, due to time pressure on my actual work.

Thanks,

Jan

|||

you committed a very common programming error. the following line:

IF NXT_PUT_PX == NULL

is not the correct way to test for a null value. use the ISNULL function instead.

|||Hi Duane,
I used the following line:
ISNULL(NXT_PUT_PX) ? NULL(DT_DECIMAL,20) : (DT_DECIMAL,20)NXT_PUT_PX

The line you are complaining about was just used as "pseudo code" for explaining the algorithmus I wanted to use.

Regards,

Jan

Error by converting string to decimal

Hi,

I'm trying to import a text-file into SQL2005 by using SSIS. Unfortunately it doesn't seem to work in the way I thought. Because I'm using the german version of SQL2005 I need to translate the items of my toolbox, so dont wonder if their names are not exactly the same as in the english versions.

Try number1:

a) My datasource is a flatfile, in the connection manager I changed the DataType for the input column to DT_DECIMAL. In my destination table the data type of ths column is decimal(20,4). Name of the column is "NXT_PUT_PX".

b) Running this package results in this error:

"Fehler bei der Datenkonvertierung. Die Datenkonvertierung für die NXT_PUT_PX-Spalte gab den Statuswert '2' und den Statustext 'Der Wert konnte aufgrund eines m?glichen Datenverlustes nicht konvertiert werden.' zurück. Fehlercode: 0xC0209084. "

Translation of this is something like:

Error by converting data. Converting data for column NXT_PUT_PX returned state '2' and text 'Value couldn't be converted because of a potential loss of data'.

Try number2:

a) same as try number1

b) Adding a datatransformation-task (Datenkonvertierung), and set NXT_PUT_PX to be converted to DT_DECIMAL wirh SCALE 20.

c) Assigning this converted column to the destination column.

d) Running the package results in the same error as described above.

Try number3:

Same as try number 2 but the input column for my flatfile is now DT_WSTR with width 50.

Running this results in the same error message as above, but different errorcode. Errorcode is 0xC020907F.

Any hints how to do this conversion correct?

Best regards,

Jan Wagner

Without looking at the data its difficult to say.

You should try and find the offending data. Following what you did in your "try number 1", put an error output on the flat file source adapter. This error output will contain all values that failed the conversion. You can then look at the failed data either in a data viewer or a flat file destination and we can then work out why the conversion is failing.

-Jamie

|||

Hi Jamie,

thanks again for your fast answer.

Did add a error output as flatfile.

The "errorflatfile" contains all rows of my test inputfile which didn't had a value in column NXT_PUT_PX. I'm wondering about that, because I allowed this column to be NULL and cannot see why there could be potential data loss.

Any ideas?

Regards,

Jan

|||

It sounds as tho you are on the right track.

What do you mean you allowed the column to be NULL? There is no concept of nullability in the SSIS pipeline (because NULLs are always allowed) so I assume you mean you specified columns to be NULLable on the destination but that is irrelevant because the erros occur before the data even gets to the destination.

It sounds as though the problem is happening during the parsing in the flat file source adapter. For that reason, I would import the data as a string and then parse it out yourself using a derived column expression, making sure that you check for NULLs (or empty strings) and handling them accordingly.

-Jamie

|||

You're right, I meant NULLs allowed in my destination table.

I tried handling the column with a derived column, so I now got a new error...

This is my statement for the derived column:

ISNULL(NXT_PUT_PX) ? NULL(DT_DECIMAL,20) : (DT_DECIMAL,20)NXT_PUT_PX

The new error is: 0xC0049063. "Fehler beim Bedingungsvorgang". Something like error in condition.

What I try to do with this statement is:

IF NXT_PUT_PX == NULL

THEN CREATE A NEW DT_DECIMAL_NULL

ELSE

CONVERT NXT_PUT_PX TO DT_DECIMAL

In german we would say "Hier ist der Wurm drin". ;-)

Regards,

Jan Wagner

|||

Your expression seems correct for what you want to do.

My suspicion would be that the failure is occuring on the conversion to DT_DECIMAL in the non-null cases. It might be as simple as a value that cannot fit in scale 20, or maybe there are spaces you need to trim or replace? My suggestion would be to configure the error output and send the failing rows to it, and inspect the data to see if that gives you any clues.

Mark

|||

Hmmm, not sure. Looks OK to me!

Keep trying :)

Sorry I can't be of more help!!

-Jamie

|||

So,

here is how it now works in my package:

1. I declared all input source columns which were including decimals as strings as DT_WSTR(30).

2. Replaced all "." with "," in "decimal" columns (thats specific for me, because here in germany we use "," as separator, but my input data come with ".").

3. Converting these columns to DT_R8.

4. Inserting into table with float as datatype for these columns.

5. Seems to work!

Thanks for all your help and ideas.

Best regards,

Jan

|||

You might be able to optimize that a litte and get rid of the explicit string replacement of "." with "," by setting the locale ID on the conversion component to a locale that uses "." for decimal place. Or better yet, set the locale ID on the flat file source component, and set the column type to DT_R8 right there.

Of course, if you have other columns you are reading that require the locale ID to be set, this might not work for you...

|||

Hi Mark,

when I set the locale ID, I get a error message that says that this locale id is not installed on my computer. I will try this again in one of the next packages, due to time pressure on my actual work.

Thanks,

Jan

|||

you committed a very common programming error. the following line:

IF NXT_PUT_PX == NULL

is not the correct way to test for a null value. use the ISNULL function instead.

|||Hi Duane,
I used the following line:
ISNULL(NXT_PUT_PX) ? NULL(DT_DECIMAL,20) : (DT_DECIMAL,20)NXT_PUT_PX

The line you are complaining about was just used as "pseudo code" for explaining the algorithmus I wanted to use.

Regards,

Jan

Error attempting to import from Excel

Here is the error:

TITLE: SQL Server Import and Export Wizard

An error occurred which the SQL Server Integration Services Wizard was not prepared to handle.

ADDITIONAL INFORMATION:

Exception has been thrown by the target of an invocation. (mscorlib)

The connection type "EXCEL" specified for connection manager "{55E5636D-F2A9-48D6-9723-867C9F241F5B}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({F390DBA3-1B52-43F4-A624-9E71D273B4D7})

I really need to get this data into SQL server... why do I have to go through SQL 2000?

Regards,

Try to refresh your connection manager or recreate a manager. Seems like when you move the package the connection properties got lost.|||

This is happening from the Wizard. It happens even after I shut down the SQL server tools and restart them. It happens as I am creating the package. It is not letting me choose Excel at all with this message.

Thanks in advance ...

|||

I get the same error as you. This was working for me, then suddenly it stopped working.

Have you found a resolution for this error?

|||

It looks like the registration of connections got broken during the setup on your mahines.

Could you guys check if dtsconn.dll is in your %ProgramFiles%\Microsoft SQL Server\90\DTS\Binn folder?

If it is there try to register it:

regsvr32 dtsconn.dll

HTH.

|||

I have not got this fixed yet and no, registering dtsconn.dll did not fix the problem. But it looks like some sort of registration is corrupt.

An interesting observation is that as soon as Excel is selected as a source it crashes.

R

|||

The follwoing post helped me fix it.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PageIndex=4&SiteID=1&PostID=363238&PageID=1

The 2nd page of post has a program for you to create and then run.

Read through the post for the directions. It worked for me.

Error attempting to import from Excel

Here is the error:

TITLE: SQL Server Import and Export Wizard

An error occurred which the SQL Server Integration Services Wizard was not prepared to handle.

ADDITIONAL INFORMATION:

Exception has been thrown by the target of an invocation. (mscorlib)

The connection type "EXCEL" specified for connection manager "{55E5636D-F2A9-48D6-9723-867C9F241F5B}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({F390DBA3-1B52-43F4-A624-9E71D273B4D7})

I really need to get this data into SQL server... why do I have to go through SQL 2000?

Regards,

Try to refresh your connection manager or recreate a manager. Seems like when you move the package the connection properties got lost.|||

This is happening from the Wizard. It happens even after I shut down the SQL server tools and restart them. It happens as I am creating the package. It is not letting me choose Excel at all with this message.

Thanks in advance ...

|||

I get the same error as you. This was working for me, then suddenly it stopped working.

Have you found a resolution for this error?

|||

It looks like the registration of connections got broken during the setup on your mahines.

Could you guys check if dtsconn.dll is in your %ProgramFiles%\Microsoft SQL Server\90\DTS\Binn folder?

If it is there try to register it:

regsvr32 dtsconn.dll

HTH.

|||

I have not got this fixed yet and no, registering dtsconn.dll did not fix the problem. But it looks like some sort of registration is corrupt.

An interesting observation is that as soon as Excel is selected as a source it crashes.

R

|||

The follwoing post helped me fix it.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PageIndex=4&SiteID=1&PostID=363238&PageID=1

The 2nd page of post has a program for you to create and then run.

Read through the post for the directions. It worked for me.