Excel file in SQl table gives sometimes null values
-
Hi
We are using automation software to trigger a file. When that file is saved on that directory it triggers a stored procedure that starts a local package. Now we have two questions :
1. We have about 15 databases. As I want to be lazy I want the db connection to be dynamic. Is this possible ? I can give a value through a variable in the automation software.
2. We have an excel file with 4 columns. Reference - Debtorid - Articlecode - quantity
The dts works just fine. But sometimes there are null values stored in the debtorid.
So like :
Ref1A52 - 100 - PRMPPG001 - 001
Ref1B52 - <null> - PRMPPG001 - 100
Ref1C53 - 512 - PRMPPG001 -133
And very often the table is filled up with nulls up to 65554 rows (just like the length of an excel sheet).
Thx
Any reply (with a answer 😉 ) would be a big help !!
Jeff
JV -
thx pacarter
So using Excel templates should help ?
And does this happen even in the middle of the data ?
Kind regards
J
JV -
>>And very often the table is filled up with nulls up to 65554 rows (just like the length of an excel sheet).
This is a known bug in DTS. When processing an Excel sheet, it previews the first 10 (I think?) rows to determine the data and data types. If the first rows do not contain data for a column, it ends up as Null for *all* rows in the destination SQL table.
There is a registry setting relating to the Excel data provider, but I can't recall where in the registry & I can't locate the MSKB article. Will try to dig it up.
[Edit] Can't locate the MSKB article. Here's a sqldts.com reference: http://www.sqldts.com/default.aspx?254
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply