Reporting Services/DTS issues

Hello,
I have a Reporting Services report that gets exported to Excel. A DTS
package (running on SQL-Server 2000) then attemps to import that Excel
file into an existing table. Occasionally, I receive the following
error:
Error Source: Microsoft JET Database Engine
Error Description: External table is not in the expected format.
There doesn't appear to be anything unusual about the spreadsheet when
this error is thrown (it contains good data, and is able to be opened
in Excel with no errors). The strange thing is that if I open the
spreadsheet and re-save it, the DTS package runs fine.
This error occurs completely randomly (at least it seems like it does).
Has anyone encountered anything similar?
Thanks!This seems like a roundabout way of doing things. Why don't you code an SP
that loads the data. then run the report off the loaded data. It should be
significantly more reliable and put a significantly smaller load on the
server if it is a large amount of data.
"bmcelhany@.gmail.com" wrote:
> Hello,
> I have a Reporting Services report that gets exported to Excel. A DTS
> package (running on SQL-Server 2000) then attemps to import that Excel
> file into an existing table. Occasionally, I receive the following
> error:
> Error Source: Microsoft JET Database Engine
> Error Description: External table is not in the expected format.
> There doesn't appear to be anything unusual about the spreadsheet when
> this error is thrown (it contains good data, and is able to be opened
> in Excel with no errors). The strange thing is that if I open the
> spreadsheet and re-save it, the DTS package runs fine.
> This error occurs completely randomly (at least it seems like it does).
> Has anyone encountered anything similar?
> Thanks!
>|||Actually, the report isn't run FROM the spreadsheet. The report pulls data
from a SQL-Server table (via a stored procedure) and then EXPORTS that report
to Excel. That spreadsheet is then e-mailed to one of our clients who uses a
DTS package to import the data into their local database...that's the point
in the process that's throwing the error. Sorry...should have been a little
more clear.
"johnE" wrote:
> This seems like a roundabout way of doing things. Why don't you code an SP
> that loads the data. then run the report off the loaded data. It should be
> significantly more reliable and put a significantly smaller load on the
> server if it is a large amount of data.
> "bmcelhany@.gmail.com" wrote:
> > Hello,
> >
> > I have a Reporting Services report that gets exported to Excel. A DTS
> > package (running on SQL-Server 2000) then attemps to import that Excel
> > file into an existing table. Occasionally, I receive the following
> > error:
> >
> > Error Source: Microsoft JET Database Engine
> > Error Description: External table is not in the expected format.
> >
> > There doesn't appear to be anything unusual about the spreadsheet when
> > this error is thrown (it contains good data, and is able to be opened
> > in Excel with no errors). The strange thing is that if I open the
> > spreadsheet and re-save it, the DTS package runs fine.
> >
> > This error occurs completely randomly (at least it seems like it does).
> > Has anyone encountered anything similar?
> >
> > Thanks!
> >
> >|||Why not use the DTS package directly to output the resultset to Excel
rather than running it through Reporting Services? I know, because
the output from RS is more aesthetically pleasing. So, the solution is
to do it both ways, the RS Excel file for humans to look at and the
DTS Excel file for the client's SQL Server database to import.
"bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
news:6402C5D3-0038-4172-9E28-C99943850EC8@.microsoft.com...
> Actually, the report isn't run FROM the spreadsheet. The report pulls data
> from a SQL-Server table (via a stored procedure) and then EXPORTS that report
> to Excel. That spreadsheet is then e-mailed to one of our clients who uses a
> DTS package to import the data into their local database...that's the point
> in the process that's throwing the error. Sorry...should have been a little
> more clear.
> "johnE" wrote:
>> This seems like a roundabout way of doing things. Why don't you code an SP
>> that loads the data. then run the report off the loaded data. It should be
>> significantly more reliable and put a significantly smaller load on the
>> server if it is a large amount of data.
>> "bmcelhany@.gmail.com" wrote:
>> > Hello,
>> >
>> > I have a Reporting Services report that gets exported to Excel. A DTS
>> > package (running on SQL-Server 2000) then attemps to import that Excel
>> > file into an existing table. Occasionally, I receive the following
>> > error:
>> >
>> > Error Source: Microsoft JET Database Engine
>> > Error Description: External table is not in the expected format.
>> >
>> > There doesn't appear to be anything unusual about the spreadsheet when
>> > this error is thrown (it contains good data, and is able to be opened
>> > in Excel with no errors). The strange thing is that if I open the
>> > spreadsheet and re-save it, the DTS package runs fine.
>> >
>> > This error occurs completely randomly (at least it seems like it does).
>> > Has anyone encountered anything similar?
>> >
>> > Thanks!
>> >
>> >|||As a last resort, that would work, however there should be no reason we can't
do it all from RS. In fact, the client's DTS import works fine about 75% of
the time...it's that last 25% that's got me. I'd rather not have to maintain
two versions (one from RS and one from SQL) of the same report.
"GeoSynch" wrote:
> Why not use the DTS package directly to output the resultset to Excel
> rather than running it through Reporting Services? I know, because
> the output from RS is more aesthetically pleasing. So, the solution is
> to do it both ways, the RS Excel file for humans to look at and the
> DTS Excel file for the client's SQL Server database to import.
> "bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
> news:6402C5D3-0038-4172-9E28-C99943850EC8@.microsoft.com...
> > Actually, the report isn't run FROM the spreadsheet. The report pulls data
> > from a SQL-Server table (via a stored procedure) and then EXPORTS that report
> > to Excel. That spreadsheet is then e-mailed to one of our clients who uses a
> > DTS package to import the data into their local database...that's the point
> > in the process that's throwing the error. Sorry...should have been a little
> > more clear.
> >
> > "johnE" wrote:
> >
> >> This seems like a roundabout way of doing things. Why don't you code an SP
> >> that loads the data. then run the report off the loaded data. It should be
> >> significantly more reliable and put a significantly smaller load on the
> >> server if it is a large amount of data.
> >>
> >> "bmcelhany@.gmail.com" wrote:
> >>
> >> > Hello,
> >> >
> >> > I have a Reporting Services report that gets exported to Excel. A DTS
> >> > package (running on SQL-Server 2000) then attemps to import that Excel
> >> > file into an existing table. Occasionally, I receive the following
> >> > error:
> >> >
> >> > Error Source: Microsoft JET Database Engine
> >> > Error Description: External table is not in the expected format.
> >> >
> >> > There doesn't appear to be anything unusual about the spreadsheet when
> >> > this error is thrown (it contains good data, and is able to be opened
> >> > in Excel with no errors). The strange thing is that if I open the
> >> > spreadsheet and re-save it, the DTS package runs fine.
> >> >
> >> > This error occurs completely randomly (at least it seems like it does).
> >> > Has anyone encountered anything similar?
> >> >
> >> > Thanks!
> >> >
> >> >
>
>|||I think I know what the problem is. Excel is more forgiving of what's in a field
than either Access or SQL Server are. Null values may be showing up in
Excel as blank strings, that is the only thing in a blank cell in Excel will be
an apostrophe, which a database import will not accept into a date or value
field.
So, here's what to do. The client's DTS package should be writing out a log
file - if it isn't, talk them through the process of setting it up. When the DTS
job
fails, have them send you the log file and it should show at which record the
job
failed. Compare that to the row number in the RS-generated Excel file to confirm
what went wrong. The solution will be for the client's DTS package to be
modified
to do transforms on these suspect fields, which will probably fall on your
shoulders.
GeoSynch
"bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
news:D507373F-7B2E-4D37-B6AE-52FEF3F1FB58@.microsoft.com...
> As a last resort, that would work, however there should be no reason we can't
> do it all from RS. In fact, the client's DTS import works fine about 75% of
> the time...it's that last 25% that's got me. I'd rather not have to maintain
> two versions (one from RS and one from SQL) of the same report.
> "GeoSynch" wrote:
>> Why not use the DTS package directly to output the resultset to Excel
>> rather than running it through Reporting Services? I know, because
>> the output from RS is more aesthetically pleasing. So, the solution is
>> to do it both ways, the RS Excel file for humans to look at and the
>> DTS Excel file for the client's SQL Server database to import.
>> "bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
>> news:6402C5D3-0038-4172-9E28-C99943850EC8@.microsoft.com...
>> > Actually, the report isn't run FROM the spreadsheet. The report pulls data
>> > from a SQL-Server table (via a stored procedure) and then EXPORTS that
>> > report
>> > to Excel. That spreadsheet is then e-mailed to one of our clients who uses
>> > a
>> > DTS package to import the data into their local database...that's the point
>> > in the process that's throwing the error. Sorry...should have been a little
>> > more clear.
>> >
>> > "johnE" wrote:
>> >
>> >> This seems like a roundabout way of doing things. Why don't you code an
>> >> SP
>> >> that loads the data. then run the report off the loaded data. It should
>> >> be
>> >> significantly more reliable and put a significantly smaller load on the
>> >> server if it is a large amount of data.
>> >>
>> >> "bmcelhany@.gmail.com" wrote:
>> >>
>> >> > Hello,
>> >> >
>> >> > I have a Reporting Services report that gets exported to Excel. A DTS
>> >> > package (running on SQL-Server 2000) then attemps to import that Excel
>> >> > file into an existing table. Occasionally, I receive the following
>> >> > error:
>> >> >
>> >> > Error Source: Microsoft JET Database Engine
>> >> > Error Description: External table is not in the expected format.
>> >> >
>> >> > There doesn't appear to be anything unusual about the spreadsheet when
>> >> > this error is thrown (it contains good data, and is able to be opened
>> >> > in Excel with no errors). The strange thing is that if I open the
>> >> > spreadsheet and re-save it, the DTS package runs fine.
>> >> >
>> >> > This error occurs completely randomly (at least it seems like it does).
>> >> > Has anyone encountered anything similar?
>> >> >
>> >> > Thanks!
>> >> >
>> >> >
>>|||OK, we're getting closer. I believe I've identified the column that's causing
the problem. In the database, the field is set up as a VARCHAR(4000). When
the value in that field is <= 255 characters, the DTS package runs fine. As
soon as the value goes beyond the 255 character mark the package fails.
So, I guess at this point I've got two questions: 1) What is it that opening
and re-saving the spreadsheet does that "fixes" an import that would
otherwise fail and 2) Short of truncating the data for that field to 255
characters, what other options might I have?
"GeoSynch" wrote:
> I think I know what the problem is. Excel is more forgiving of what's in a field
> than either Access or SQL Server are. Null values may be showing up in
> Excel as blank strings, that is the only thing in a blank cell in Excel will be
> an apostrophe, which a database import will not accept into a date or value
> field.
> So, here's what to do. The client's DTS package should be writing out a log
> file - if it isn't, talk them through the process of setting it up. When the DTS
> job
> fails, have them send you the log file and it should show at which record the
> job
> failed. Compare that to the row number in the RS-generated Excel file to confirm
> what went wrong. The solution will be for the client's DTS package to be
> modified
> to do transforms on these suspect fields, which will probably fall on your
> shoulders.
>
> GeoSynch
>
> "bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
> news:D507373F-7B2E-4D37-B6AE-52FEF3F1FB58@.microsoft.com...
> > As a last resort, that would work, however there should be no reason we can't
> > do it all from RS. In fact, the client's DTS import works fine about 75% of
> > the time...it's that last 25% that's got me. I'd rather not have to maintain
> > two versions (one from RS and one from SQL) of the same report.
> >
> > "GeoSynch" wrote:
> >
> >> Why not use the DTS package directly to output the resultset to Excel
> >> rather than running it through Reporting Services? I know, because
> >> the output from RS is more aesthetically pleasing. So, the solution is
> >> to do it both ways, the RS Excel file for humans to look at and the
> >> DTS Excel file for the client's SQL Server database to import.
> >>
> >> "bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
> >> news:6402C5D3-0038-4172-9E28-C99943850EC8@.microsoft.com...
> >> > Actually, the report isn't run FROM the spreadsheet. The report pulls data
> >> > from a SQL-Server table (via a stored procedure) and then EXPORTS that
> >> > report
> >> > to Excel. That spreadsheet is then e-mailed to one of our clients who uses
> >> > a
> >> > DTS package to import the data into their local database...that's the point
> >> > in the process that's throwing the error. Sorry...should have been a little
> >> > more clear.
> >> >
> >> > "johnE" wrote:
> >> >
> >> >> This seems like a roundabout way of doing things. Why don't you code an
> >> >> SP
> >> >> that loads the data. then run the report off the loaded data. It should
> >> >> be
> >> >> significantly more reliable and put a significantly smaller load on the
> >> >> server if it is a large amount of data.
> >> >>
> >> >> "bmcelhany@.gmail.com" wrote:
> >> >>
> >> >> > Hello,
> >> >> >
> >> >> > I have a Reporting Services report that gets exported to Excel. A DTS
> >> >> > package (running on SQL-Server 2000) then attemps to import that Excel
> >> >> > file into an existing table. Occasionally, I receive the following
> >> >> > error:
> >> >> >
> >> >> > Error Source: Microsoft JET Database Engine
> >> >> > Error Description: External table is not in the expected format.
> >> >> >
> >> >> > There doesn't appear to be anything unusual about the spreadsheet when
> >> >> > this error is thrown (it contains good data, and is able to be opened
> >> >> > in Excel with no errors). The strange thing is that if I open the
> >> >> > spreadsheet and re-save it, the DTS package runs fine.
> >> >> >
> >> >> > This error occurs completely randomly (at least it seems like it does).
> >> >> > Has anyone encountered anything similar?
> >> >> >
> >> >> > Thanks!
> >> >> >
> >> >> >
> >>
> >>
> >>
>
>|||Excel limits the number of characters in a cell to 256, including the
leftmost alignment character of ' or " or ^. Nonetheless, a foreign
app can create an Excel file with the number of character exceeding
the 256 threshold. If you open the file and edited the offensive cell,
Excel will truncate it to the leftmost 256 characters. I suppose
explicitly saving the file from within Excel truncates all offensive cells.
You'll can modify your SRS field to Left(DataSet!FieldName.Value, 255)
to make sure all goes smoothly, or add more fields to the SRS
report for each incremental chunk of 255 characters. The client's
import process could then concatenate these fields into one.
GeoSynch
"bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
news:D19E073D-989B-4517-8695-68EC82C73053@.microsoft.com...
> OK, we're getting closer. I believe I've identified the column that's causing
> the problem. In the database, the field is set up as a VARCHAR(4000). When
> the value in that field is <= 255 characters, the DTS package runs fine. As
> soon as the value goes beyond the 255 character mark the package fails.
> So, I guess at this point I've got two questions: 1) What is it that opening
> and re-saving the spreadsheet does that "fixes" an import that would
> otherwise fail and 2) Short of truncating the data for that field to 255
> characters, what other options might I have?
> "GeoSynch" wrote:
>> I think I know what the problem is. Excel is more forgiving of what's in a
>> field
>> than either Access or SQL Server are. Null values may be showing up in
>> Excel as blank strings, that is the only thing in a blank cell in Excel will
>> be
>> an apostrophe, which a database import will not accept into a date or value
>> field.
>> So, here's what to do. The client's DTS package should be writing out a log
>> file - if it isn't, talk them through the process of setting it up. When the
>> DTS
>> job
>> fails, have them send you the log file and it should show at which record the
>> job
>> failed. Compare that to the row number in the RS-generated Excel file to
>> confirm
>> what went wrong. The solution will be for the client's DTS package to be
>> modified
>> to do transforms on these suspect fields, which will probably fall on your
>> shoulders.
>>
>> GeoSynch
>>
>> "bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
>> news:D507373F-7B2E-4D37-B6AE-52FEF3F1FB58@.microsoft.com...
>> > As a last resort, that would work, however there should be no reason we
>> > can't
>> > do it all from RS. In fact, the client's DTS import works fine about 75% of
>> > the time...it's that last 25% that's got me. I'd rather not have to
>> > maintain
>> > two versions (one from RS and one from SQL) of the same report.
>> >
>> > "GeoSynch" wrote:
>> >
>> >> Why not use the DTS package directly to output the resultset to Excel
>> >> rather than running it through Reporting Services? I know, because
>> >> the output from RS is more aesthetically pleasing. So, the solution is
>> >> to do it both ways, the RS Excel file for humans to look at and the
>> >> DTS Excel file for the client's SQL Server database to import.
>> >>
>> >> "bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
>> >> news:6402C5D3-0038-4172-9E28-C99943850EC8@.microsoft.com...
>> >> > Actually, the report isn't run FROM the spreadsheet. The report pulls
>> >> > data
>> >> > from a SQL-Server table (via a stored procedure) and then EXPORTS that
>> >> > report
>> >> > to Excel. That spreadsheet is then e-mailed to one of our clients who
>> >> > uses
>> >> > a
>> >> > DTS package to import the data into their local database...that's the
>> >> > point
>> >> > in the process that's throwing the error. Sorry...should have been a
>> >> > little
>> >> > more clear.
>> >> >
>> >> > "johnE" wrote:
>> >> >
>> >> >> This seems like a roundabout way of doing things. Why don't you code
>> >> >> an
>> >> >> SP
>> >> >> that loads the data. then run the report off the loaded data. It
>> >> >> should
>> >> >> be
>> >> >> significantly more reliable and put a significantly smaller load on the
>> >> >> server if it is a large amount of data.
>> >> >>
>> >> >> "bmcelhany@.gmail.com" wrote:
>> >> >>
>> >> >> > Hello,
>> >> >> >
>> >> >> > I have a Reporting Services report that gets exported to Excel. A DTS
>> >> >> > package (running on SQL-Server 2000) then attemps to import that
>> >> >> > Excel
>> >> >> > file into an existing table. Occasionally, I receive the following
>> >> >> > error:
>> >> >> >
>> >> >> > Error Source: Microsoft JET Database Engine
>> >> >> > Error Description: External table is not in the expected format.
>> >> >> >
>> >> >> > There doesn't appear to be anything unusual about the spreadsheet
>> >> >> > when
>> >> >> > this error is thrown (it contains good data, and is able to be opened
>> >> >> > in Excel with no errors). The strange thing is that if I open the
>> >> >> > spreadsheet and re-save it, the DTS package runs fine.
>> >> >> >
>> >> >> > This error occurs completely randomly (at least it seems like it
>> >> >> > does).
>> >> >> > Has anyone encountered anything similar?
>> >> >> >
>> >> >> > Thanks!
>> >> >> >
>> >> >> >
>> >>
>> >>
>> >>
>>|||OK, I'll buy that. Thanks a lot for all of your help with this!
"GeoSynch" wrote:
> Excel limits the number of characters in a cell to 256, including the
> leftmost alignment character of ' or " or ^. Nonetheless, a foreign
> app can create an Excel file with the number of character exceeding
> the 256 threshold. If you open the file and edited the offensive cell,
> Excel will truncate it to the leftmost 256 characters. I suppose
> explicitly saving the file from within Excel truncates all offensive cells.
> You'll can modify your SRS field to Left(DataSet!FieldName.Value, 255)
> to make sure all goes smoothly, or add more fields to the SRS
> report for each incremental chunk of 255 characters. The client's
> import process could then concatenate these fields into one.
>
> GeoSynch
>
> "bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
> news:D19E073D-989B-4517-8695-68EC82C73053@.microsoft.com...
> > OK, we're getting closer. I believe I've identified the column that's causing
> > the problem. In the database, the field is set up as a VARCHAR(4000). When
> > the value in that field is <= 255 characters, the DTS package runs fine. As
> > soon as the value goes beyond the 255 character mark the package fails.
> >
> > So, I guess at this point I've got two questions: 1) What is it that opening
> > and re-saving the spreadsheet does that "fixes" an import that would
> > otherwise fail and 2) Short of truncating the data for that field to 255
> > characters, what other options might I have?
> >
> > "GeoSynch" wrote:
> >
> >> I think I know what the problem is. Excel is more forgiving of what's in a
> >> field
> >> than either Access or SQL Server are. Null values may be showing up in
> >> Excel as blank strings, that is the only thing in a blank cell in Excel will
> >> be
> >> an apostrophe, which a database import will not accept into a date or value
> >> field.
> >> So, here's what to do. The client's DTS package should be writing out a log
> >> file - if it isn't, talk them through the process of setting it up. When the
> >> DTS
> >> job
> >> fails, have them send you the log file and it should show at which record the
> >> job
> >> failed. Compare that to the row number in the RS-generated Excel file to
> >> confirm
> >> what went wrong. The solution will be for the client's DTS package to be
> >> modified
> >> to do transforms on these suspect fields, which will probably fall on your
> >> shoulders.
> >>
> >>
> >> GeoSynch
> >>
> >>
> >> "bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
> >> news:D507373F-7B2E-4D37-B6AE-52FEF3F1FB58@.microsoft.com...
> >> > As a last resort, that would work, however there should be no reason we
> >> > can't
> >> > do it all from RS. In fact, the client's DTS import works fine about 75% of
> >> > the time...it's that last 25% that's got me. I'd rather not have to
> >> > maintain
> >> > two versions (one from RS and one from SQL) of the same report.
> >> >
> >> > "GeoSynch" wrote:
> >> >
> >> >> Why not use the DTS package directly to output the resultset to Excel
> >> >> rather than running it through Reporting Services? I know, because
> >> >> the output from RS is more aesthetically pleasing. So, the solution is
> >> >> to do it both ways, the RS Excel file for humans to look at and the
> >> >> DTS Excel file for the client's SQL Server database to import.
> >> >>
> >> >> "bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
> >> >> news:6402C5D3-0038-4172-9E28-C99943850EC8@.microsoft.com...
> >> >> > Actually, the report isn't run FROM the spreadsheet. The report pulls
> >> >> > data
> >> >> > from a SQL-Server table (via a stored procedure) and then EXPORTS that
> >> >> > report
> >> >> > to Excel. That spreadsheet is then e-mailed to one of our clients who
> >> >> > uses
> >> >> > a
> >> >> > DTS package to import the data into their local database...that's the
> >> >> > point
> >> >> > in the process that's throwing the error. Sorry...should have been a
> >> >> > little
> >> >> > more clear.
> >> >> >
> >> >> > "johnE" wrote:
> >> >> >
> >> >> >> This seems like a roundabout way of doing things. Why don't you code
> >> >> >> an
> >> >> >> SP
> >> >> >> that loads the data. then run the report off the loaded data. It
> >> >> >> should
> >> >> >> be
> >> >> >> significantly more reliable and put a significantly smaller load on the
> >> >> >> server if it is a large amount of data.
> >> >> >>
> >> >> >> "bmcelhany@.gmail.com" wrote:
> >> >> >>
> >> >> >> > Hello,
> >> >> >> >
> >> >> >> > I have a Reporting Services report that gets exported to Excel. A DTS
> >> >> >> > package (running on SQL-Server 2000) then attemps to import that
> >> >> >> > Excel
> >> >> >> > file into an existing table. Occasionally, I receive the following
> >> >> >> > error:
> >> >> >> >
> >> >> >> > Error Source: Microsoft JET Database Engine
> >> >> >> > Error Description: External table is not in the expected format.
> >> >> >> >
> >> >> >> > There doesn't appear to be anything unusual about the spreadsheet
> >> >> >> > when
> >> >> >> > this error is thrown (it contains good data, and is able to be opened
> >> >> >> > in Excel with no errors). The strange thing is that if I open the
> >> >> >> > spreadsheet and re-save it, the DTS package runs fine.
> >> >> >> >
> >> >> >> > This error occurs completely randomly (at least it seems like it
> >> >> >> > does).
> >> >> >> > Has anyone encountered anything similar?
> >> >> >> >
> >> >> >> > Thanks!
> >> >> >> >
> >> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>