Export Excel Problem

Well i did a report that show a Week WorkPlan.
I did it using a table which ds get all the missions for the week and has 5
columns for each labor day.In each column there's a inside table which shows
the missions for the current day(using hidden property...).
It worked great but now when i export to excel I get in each cell this
message:"Data Regions within table/matrix cells are ignored."
Whats the problem?This is a limitation of reporting services. I know it sounds crude but
change your table to a series of text areas inside a rectangle. That worked
for me. Are you the same person that post to sqlservercentral? I tried to
reply to you there but our network stops me replying to posts there.
"ש×?×?×?" wrote:
> Well i did a report that show a Week WorkPlan.
> I did it using a table which ds get all the missions for the week and has 5
> columns for each labor day.In each column there's a inside table which shows
> the missions for the current day(using hidden property...).
> It worked great but now when i export to excel I get in each cell this
> message:"Data Regions within table/matrix cells are ignored."
> Whats the problem?
>|||yes I wrote this q also in sqlservercentral.
thanks 4 your answer. I found this approceh also in google but it didn't
worked 4 me.
My report is built as table with ds of missions per Area.
The Grouping in this table is the Area.
For each Area there some mission per day.
When i used the rectangle it showed me onky the last mission.
Whats the problem?
"Nicola Jones" wrote:
> This is a limitation of reporting services. I know it sounds crude but
> change your table to a series of text areas inside a rectangle. That worked
> for me. Are you the same person that post to sqlservercentral? I tried to
> reply to you there but our network stops me replying to posts there.
> "ש×?×?×?" wrote:
> > Well i did a report that show a Week WorkPlan.
> >
> > I did it using a table which ds get all the missions for the week and has 5
> > columns for each labor day.In each column there's a inside table which shows
> > the missions for the current day(using hidden property...).
> >
> > It worked great but now when i export to excel I get in each cell this
> > message:"Data Regions within table/matrix cells are ignored."
> >
> > Whats the problem?
> >

export excel data to sql server

can any body help me how to export excel data to sql server table..using c#.net...please very urgent

It might be better to ask this question on the .NET forums.

But check out this article on MSDN.

Export Excel : put name of table in sheet's name

Hello,
Is it possible to give another name that "Sheet1" when I export my report to
Excel ?
Can I put the title of my table report, for example ?
Thanks.You can not control the name of the sheets. One sheet report will use the
report name for the sheet name. Multiple sheets report will use Sheet1,
Sheet2 ...
--
Nico Cristache [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Drix78" <Drix78@.discussions.microsoft.com> wrote in message
news:C69AE2AB-7FC5-4B61-8E43-318A3DD4115D@.microsoft.com...
> Hello,
> Is it possible to give another name that "Sheet1" when I export my report
to
> Excel ?
> Can I put the title of my table report, for example ?
> Thanks.
>

Export Excel : formatting has number with a formula in data report

Hello,
I have a problem when I export my report to Excel : the cells are not
considered as numbers.
In my report, I have a table witch fills data formatting with 1 decimal if
the data is not null. It's important because 0 and null must be considered as
different values.
An example of one cell :
=IIf(""&Fields!AFF_OBJ_ACT.Value = "", "",
Format(Fields!AFF_OBJ_ACT.Value/1000,"#,0.0"))
So, my report fills data like : empty, "0,0", or "x xxx,0", etc...
But, when I export my report to Excel, data are not considered as numbers
but strings with the green left top corner ! :-(
Any idea to help me ?
I understand that I need to format in my report data as number, but how to
differentiate 0 and null values... ?
Thanks.Sorry for my pathetic english...
You must understand "as" instead of "has" in the title
and "which" instead of "which"...
It should be more clearly...
"Drix78" wrote:
> Hello,
> I have a problem when I export my report to Excel : the cells are not
> considered as numbers.
> In my report, I have a table witch fills data formatting with 1 decimal if
> the data is not null. It's important because 0 and null must be considered as
> different values.
> An example of one cell :
> =IIf(""&Fields!AFF_OBJ_ACT.Value = "", "",
> Format(Fields!AFF_OBJ_ACT.Value/1000,"#,0.0"))
> So, my report fills data like : empty, "0,0", or "x xxx,0", etc...
> But, when I export my report to Excel, data are not considered as numbers
> but strings with the green left top corner ! :-(
>
> Any idea to help me ?
> I understand that I need to format in my report data as number, but how to
> differentiate 0 and null values... ?
> Thanks.|||I find the response of my question. If this information can help someone...
I just work on visibility of the cell.
The Hidden property of Visibility is set with the expression :
=IIf(""&Fields!AFF_OBJ_ACT.Value = "", True, False)
And the value of the cell :
=Fields!AFF_OBJ_ACT.Value/1000
with the customized format #,0.0
"Drix78" wrote:
> Sorry for my pathetic english...
> You must understand "as" instead of "has" in the title
> and "which" instead of "which"...
> It should be more clearly...
>
> "Drix78" wrote:
> > Hello,
> >
> > I have a problem when I export my report to Excel : the cells are not
> > considered as numbers.
> > In my report, I have a table witch fills data formatting with 1 decimal if
> > the data is not null. It's important because 0 and null must be considered as
> > different values.
> >
> > An example of one cell :
> > =IIf(""&Fields!AFF_OBJ_ACT.Value = "", "",
> > Format(Fields!AFF_OBJ_ACT.Value/1000,"#,0.0"))
> >
> > So, my report fills data like : empty, "0,0", or "x xxx,0", etc...
> > But, when I export my report to Excel, data are not considered as numbers
> > but strings with the green left top corner ! :-(
> >
> >
> > Any idea to help me ?
> > I understand that I need to format in my report data as number, but how to
> > differentiate 0 and null values... ?
> >
> > Thanks.sql

export every table into a separate csv

Within sql2000 is there a wizard/function whereby a user can select all
tables and export the data into separate csv files?
Don't think there is a wizard to do this but the following
article has the information to get you started on doing this
in DTS:
How to export all tables in a database
http://www.sqldts.com/default.aspx?299
Make sure the check the link to another article on the site:
How to loop through a global variable Rowset
http://www.sqldts.com/default.aspx?298
-Sue
On Mon, 2 Oct 2006 16:28:12 +0100, "Jack Vamvas"
<DEL_TO_REPLYtechsupport@.ciquery.com> wrote:

>Within sql2000 is there a wizard/function whereby a user can select all
>tables and export the data into separate csv files?
>

export every table into a separate csv

Within sql2000 is there a wizard/function whereby a user can select all
tables and export the data into separate csv files?Don't think there is a wizard to do this but the following
article has the information to get you started on doing this
in DTS:
How to export all tables in a database
http://www.sqldts.com/default.aspx?299
Make sure the check the link to another article on the site:
How to loop through a global variable Rowset
http://www.sqldts.com/default.aspx?298
-Sue
On Mon, 2 Oct 2006 16:28:12 +0100, "Jack Vamvas"
<DEL_TO_REPLYtechsupport@.ciquery.com> wrote:

>Within sql2000 is there a wizard/function whereby a user can select all
>tables and export the data into separate csv files?
>

export every table into a separate csv

Within sql2000 is there a wizard/function whereby a user can select all
tables and export the data into separate csv files?Don't think there is a wizard to do this but the following
article has the information to get you started on doing this
in DTS:
How to export all tables in a database
http://www.sqldts.com/default.aspx?299
Make sure the check the link to another article on the site:
How to loop through a global variable Rowset
http://www.sqldts.com/default.aspx?298
-Sue
On Mon, 2 Oct 2006 16:28:12 +0100, "Jack Vamvas"
<DEL_TO_REPLYtechsupport@.ciquery.com> wrote:
>Within sql2000 is there a wizard/function whereby a user can select all
>tables and export the data into separate csv files?
>

Export Embedded Data regions to Excel problem

I have a report that uses a matrix with an embedded table. There are no problems when you view/print it or export it to pdf. However when you export it to Excel the embedded table does not display. Instead the message 'Data Regions within table/matrix cells are ignored' appears where the table should be.

Is it not possible to view embedded data regions in excel or perhaps some setting I have missed.
I'm using Reporting Services 2000 with Service Pack 2.

thanks
Scott

I found the answer to my problem:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_dc_v1_8yhy.asp

Any data region nested inside of a table or matrix data region is not supported. An error is displayed in Excel if this layout is encountered.

thanks
Scott

|||Then, did you changed the all reports OR you still living with this problem. I am also facing the same problem but am undecisive about the way I should go.

If you did change the reports can you please tell how did you managed it?

Thnaks
Tanveer

Export Embedded Data regions to Excel problem

I have a report that uses a matrix with an embedded table. There are no problems when you view/print it or export it to pdf. However when you export it to Excel the embedded table does not display. Instead the message 'Data Regions within table/matrix cells are ignored' appears where the table should be.

Is it not possible to view embedded data regions in excel or perhaps some setting I have missed.
I'm using Reporting Services 2000 with Service Pack 2.

thanks
Scott

I found the answer to my problem:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_dc_v1_8yhy.asp

Any data region nested inside of a table or matrix data region is not supported. An error is displayed in Excel if this layout is encountered.

thanks
Scott

|||Then, did you changed the all reports OR you still living with this problem. I am also facing the same problem but am undecisive about the way I should go.

If you did change the reports can you please tell how did you managed it?

Thnaks
Tanveer

export DBCC CHECKDB into table

Is there anyway to export DBCC CHECKDB results into a table?
The only example I was able to find on Books Online is:
INSERT INTO #tracestatus
EXEC ('DBCC TRACESTATUS (-1) WITH NO_INFOMSGS')
Please helpBelow work fine on 2005:
CREATE TABLE a
(c1 int, c2 int, c3 int, c4 varchar(4000), c5 int, c6 int, c7 int, c8 int, c9 int, c10 int, c11 int,
c12 int, c23 int, c14 int, c15 int, c16 int, c17 int, c18 int)
insert into a
EXEC('DBCC CHECKDB(pubs) WITH TABLERESULTS')
SELECT * FROM a
Of course, create the table with a bit more care and try to match the data types carefully instead
of just guessing like I did. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:D82B695F-110A-492B-82C6-33C592981F8F@.microsoft.com...
> Is there anyway to export DBCC CHECKDB results into a table?
> The only example I was able to find on Books Online is:
> INSERT INTO #tracestatus
> EXEC ('DBCC TRACESTATUS (-1) WITH NO_INFOMSGS')
> Please help|||It works!
Thanks a million, Tibor!
"Tibor Karaszi" wrote:
> Below work fine on 2005:
> CREATE TABLE a
> (c1 int, c2 int, c3 int, c4 varchar(4000), c5 int, c6 int, c7 int, c8 int, c9 int, c10 int, c11 int,
> c12 int, c23 int, c14 int, c15 int, c16 int, c17 int, c18 int)
> insert into a
> EXEC('DBCC CHECKDB(pubs) WITH TABLERESULTS')
> SELECT * FROM a
>
> Of course, create the table with a bit more care and try to match the data types carefully instead
> of just guessing like I did. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
> news:D82B695F-110A-492B-82C6-33C592981F8F@.microsoft.com...
> > Is there anyway to export DBCC CHECKDB results into a table?
> >
> > The only example I was able to find on Books Online is:
> > INSERT INTO #tracestatus
> > EXEC ('DBCC TRACESTATUS (-1) WITH NO_INFOMSGS')
> >
> > Please help
>

export DBCC CHECKDB into table

Is there anyway to export DBCC CHECKDB results into a table?
The only example I was able to find on Books Online is:
INSERT INTO #tracestatus
EXEC ('DBCC TRACESTATUS (-1) WITH NO_INFOMSGS')
Please helpBelow work fine on 2005:
CREATE TABLE a
(c1 int, c2 int, c3 int, c4 varchar(4000), c5 int, c6 int, c7 int, c8 int, c
9 int, c10 int, c11 int,
c12 int, c23 int, c14 int, c15 int, c16 int, c17 int, c18 int)
insert into a
EXEC('DBCC CHECKDB(pubs) WITH TABLERESULTS')
SELECT * FROM a
Of course, create the table with a bit more care and try to match the data t
ypes carefully instead
of just guessing like I did. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in me
ssage
news:D82B695F-110A-492B-82C6-33C592981F8F@.microsoft.com...
> Is there anyway to export DBCC CHECKDB results into a table?
> The only example I was able to find on Books Online is:
> INSERT INTO #tracestatus
> EXEC ('DBCC TRACESTATUS (-1) WITH NO_INFOMSGS')
> Please help|||It works!
Thanks a million, Tibor!
"Tibor Karaszi" wrote:

> Below work fine on 2005:
> CREATE TABLE a
> (c1 int, c2 int, c3 int, c4 varchar(4000), c5 int, c6 int, c7 int, c8 int,
c9 int, c10 int, c11 int,
> c12 int, c23 int, c14 int, c15 int, c16 int, c17 int, c18 int)
> insert into a
> EXEC('DBCC CHECKDB(pubs) WITH TABLERESULTS')
> SELECT * FROM a
>
> Of course, create the table with a bit more care and try to match the data
types carefully instead
> of just guessing like I did. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message
> news:D82B695F-110A-492B-82C6-33C592981F8F@.microsoft.com...
>

Export dates to CSV

A table has a datetime column, which contains dates only (no time part). In
EM, the dates look like 5/10/2007. After exporting the table to a CSV file
using All Tasks->Export Data... the dates look like 2007-05-10 00:00:00.
How can I export the dates in the same format they appear in the EM, that is
5/10/2007? At least I need that the dates appear in the CSV file without the
time part.
Thank you.
Check BOL for the CONVERT function. I believe 101 is the one you are
looking for.
TheSQLGuru
President
Indicium Resources, Inc.
"Vik" <viktorum@.==yahoo.com==> wrote in message
news:%2307zUA0kHHA.3264@.TK2MSFTNGP04.phx.gbl...
>A table has a datetime column, which contains dates only (no time part). In
>EM, the dates look like 5/10/2007. After exporting the table to a CSV file
>using All Tasks->Export Data... the dates look like 2007-05-10 00:00:00.
> How can I export the dates in the same format they appear in the EM, that
> is 5/10/2007? At least I need that the dates appear in the CSV file
> without the time part.
> Thank you.
>
>

Export database event ?

Hey
I'd like to modify a field in a table when I export my sql server 2000
database to a access db. I would like it to be done between the moment
I launch the process with the Export tool and the the moment the data
is sent to the access db.
Anyone ?
Thanks by advanceCouple of questions . . .
By "the Export tool" do you mean DTS?
By "modify a field in a table" do you mean a particular column in the
SQL Server table gets transformed to a different value and then gets
inserted into a particular column in the MSAccess table during the
export process?
If so, you can transform the data within DTS before populating the
MSAccess table. In the DTS wizard, when you get to the screen titled
"Select Source Tables and Views", select your source table and
destination table and then click in the Transform column. Take a look at
that and see if that will do what you're wanting.
Carl
Robert Kalophtalmos wrote:
> Hey
> I'd like to modify a field in a table when I export my sql server 2000
> database to a access db. I would like it to be done between the moment
> I launch the process with the Export tool and the the moment the data
> is sent to the access db.
> Anyone ?
> Thanks by advance
>|||Thanks for the answer but that's not really it;
By "Export tool" I mean : right click on database name / All Tasks /
Export data
By "modify a field in a table" I mean : insert the actual date (for
example) in a SQL Server database table
I don't know if my english is good enough to explain that :(
Regards
Franck|||Actually,
right click on database name / All Tasks / Export data
*is* running DTS - Data Transformation Services.
When you export the data from SQL Server into MSAccess, do you want to
put the current date into a particular column in the destination table
(in MSAccess)?
try this:
right click on database name / All Tasks / Export data
select a source and click next (probably a SQL Server database)
select a destination and click next (probably an Access database)
select Copy table(s) and view(s) from the source database and click next
select a table in the list and click on the "Transform" button in the
3rd column
Click on the Transformations tab
click the "Transform information as it is copied..." radio button
From there, you're on your own . . . not knowing your table schema or
anything, I could only guess.
Hope this helps -- let me know.
Carl
Robert Kalophtalmos wrote:

> Thanks for the answer but that's not really it;
> By "Export tool" I mean : right click on database name / All Tasks /
> Export data
> By "modify a field in a table" I mean : insert the actual date (for
> example) in a SQL Server database table
> I don't know if my english is good enough to explain that :(
> Regards
> Franck
>|||My mistake for the DTS...
About the current date, I'll try that, thanks !

export data without trailing spaces

Hello,
when I export data from a table to a text file, I get trailing spaces
if the data type in char. (This dosen't happen if the data type is
varchar). I can get rid of the spaces by using the trim() function on
every signle column. here is an example:
DTSDestination("first_name") = DTSSource("last_name")

My question is:
Is there any easier way to get ride of the training spaces for all
columns when exporing a table? It is too time consuming if I have to
type trim() for every single column in the table.
Thank you in advance,
EddyHi

You can create a trim string transformation for the columns you wish to
trim. Depending on what currently have you may have to remove them from the
current transformation and create a new one.

John

<eddiekwang@.hotmail.com> wrote in message
news:1103225990.307403.256040@.c13g2000cwb.googlegr oups.com...
> Hello,
> when I export data from a table to a text file, I get trailing spaces
> if the data type in char. (This dosen't happen if the data type is
> varchar). I can get rid of the spaces by using the trim() function on
> every signle column. here is an example:
> DTSDestination("first_name") = DTSSource("last_name")
> My question is:
> Is there any easier way to get ride of the training spaces for all
> columns when exporing a table? It is too time consuming if I have to
> type trim() for every single column in the table.
> Thank you in advance,
> Eddy

export data to oracle db

Who knows a simple way to export a table from SQL server
to oracle databases? Thanks a lot.Hi Jiaxin,
You can use DTS to do that I believe. From Enterprise Manager, right click
on the table, export data,
Sincerely,
Yih-Yoon Lee [Microsoft]
Microsoft SQL Server Support
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Hi Yih,
Thank you very much for you reply. But I am not a DBA and
the only client software that I can use to access the SQL
Server is MS access97.
Any other suggestions?
Jiaxin
>--Original Message--
>Hi Jiaxin,
>You can use DTS to do that I believe. From Enterprise
Manager, right click
>on the table, export data,
>Sincerely,
>Yih-Yoon Lee [Microsoft]
>Microsoft SQL Server Support
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Subscribe to MSDN & use
http://msdn.microsoft.com/newsgroups.
>.
>

Export Data to MS Access file

Hi guys,
I use Data Flow Task to export data from SQL table to MS Access file.
In Connection Manager for Data Flow Destination task I specify
provider "Native OLEDB/MicrosoftJet 4.0" and path to Access file
in Database file name box. In design time I have no validation error.
Everything looks fine.
However in run time mode I'm getting error:

"Error: 0xC020801C <Datataset name> , MS Access File [1459]: The AcquireConnection method call to the connection manager
<connection manager name> failed with error code 0xC0202009.
Error: 0xC0047017 at <Datataset name>, DTS.Pipeline: component "MS Access File" (1459) failed validation and returned error code 0xC020801C."

Any thoughts?
Thanks.Anyone? :)|||Are you able to use your Access data source outside of SSIS?

Ideally, could you create a small .NET application to open an OLEDBConnection with the same connection string the SSIS connection manager has.

Can you also post the connection string from your Access connection here?

I saw no problems with using Access connections in SSIS designer (and Import/Export wizard) on my machine.|||Ok, what I figured out is if Access application (or Jet engine) isn't installed,
you cannot export data to mdb file.|||A Windows computer without the Jet engine should be a rare occurrence...beginning, I believe, with XP, it is installed with the operating system, independently of the Access application that uses it as its default database format.|||Well, it doesn't work on 2003 Server Enterprise x64 Edition (no MS Access installed), but works on XP with MS Access.|||I'm having the same problem. But I'm trying to import data from an access file. Can you let me know how you resolved your problem? Did you install the Jet engine? If yes, where do you get it from? Thanks in advance for your response.|||

If I am not wrong, the only thing you need is the MDAC which you can download here http://www.microsoft.com/downloads/details.aspx?DisplayLang=en&FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c

Create an OLE DB Connection in SSIS, then create a data flow control. In the data control flow, create an OLE DB source, and then maybe a flat file or OLE DB destination. Link Them together, and test it.

Hope it goes well.

|||

Metal_Fly wrote:

Well, it doesn't work on 2003 Server Enterprise x64 Edition (no MS Access installed), but works on XP with MS Access.

Metal_Fly,

If I understand correctly; you are getting the error only at runtime when the package is executed in a 64-bit machine; right?

If that is the case; you have to run the package in 32-bit mode in the server. I am not sure how you are running the package; but it seems like is running as 64-bit; so SSIS would try to load a Jet 64-bit driver which is not registered in the system.

For running the package in 32-bit; you need to use the DTexec.exe in the Program Files (x86) folder.

The command line should look like:

C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec /FILE "D:\Shared\DigitalCockpit\ETL\ETLControl.dtsx" /MAXCONCURRENT " -1" /CHECKPOINTING OFF /REPORTING EW

I believe you won't be able to use BIDS debugger in the server because it runs the package as 64-bit.

I am not sure if Jet is available for 64-bit; so I will leave that open to discussion.

BTW, Joseph suggestion may not work since that download is available for x86 architecture.

Rafael Salas

Export Data to MS Access file

Hi guys,
I use Data Flow Task to export data from SQL table to MS Access file.
In Connection Manager for Data Flow Destination task I specify
provider "Native OLEDB/MicrosoftJet 4.0" and path to Access file
in Database file name box. In design time I have no validation error.
Everything looks fine.
However in run time mode I'm getting error:

"Error: 0xC020801C <Datataset name> , MS Access File [1459]: The AcquireConnection method call to the connection manager
<connection manager name> failed with error code 0xC0202009.
Error: 0xC0047017 at <Datataset name>, DTS.Pipeline: component "MS Access File" (1459) failed validation and returned error code 0xC020801C."

Any thoughts?
Thanks.

Anyone? :)|||Are you able to use your Access data source outside of SSIS?

Ideally, could you create a small .NET application to open an OLEDBConnection with the same connection string the SSIS connection manager has.

Can you also post the connection string from your Access connection here?

I saw no problems with using Access connections in SSIS designer (and Import/Export wizard) on my machine.|||Ok, what I figured out is if Access application (or Jet engine) isn't installed,
you cannot export data to mdb file.|||A Windows computer without the Jet engine should be a rare occurrence...beginning, I believe, with XP, it is installed with the operating system, independently of the Access application that uses it as its default database format.|||Well, it doesn't work on 2003 Server Enterprise x64 Edition (no MS Access installed), but works on XP with MS Access.|||I'm having the same problem. But I'm trying to import data from an access file. Can you let me know how you resolved your problem? Did you install the Jet engine? If yes, where do you get it from? Thanks in advance for your response.|||

If I am not wrong, the only thing you need is the MDAC which you can download here http://www.microsoft.com/downloads/details.aspx?DisplayLang=en&FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c

Create an OLE DB Connection in SSIS, then create a data flow control. In the data control flow, create an OLE DB source, and then maybe a flat file or OLE DB destination. Link Them together, and test it.

Hope it goes well.

|||

Metal_Fly wrote:

Well, it doesn't work on 2003 Server Enterprise x64 Edition (no MS Access installed), but works on XP with MS Access.

Metal_Fly,

If I understand correctly; you are getting the error only at runtime when the package is executed in a 64-bit machine; right?

If that is the case; you have to run the package in 32-bit mode in the server. I am not sure how you are running the package; but it seems like is running as 64-bit; so SSIS would try to load a Jet 64-bit driver which is not registered in the system.

For running the package in 32-bit; you need to use the DTexec.exe in the Program Files (x86) folder.

The command line should look like:

C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec /FILE "D:\Shared\DigitalCockpit\ETL\ETLControl.dtsx" /MAXCONCURRENT " -1" /CHECKPOINTING OFF /REPORTING EW

I believe you won't be able to use BIDS debugger in the server because it runs the package as 64-bit.

I am not sure if Jet is available for 64-bit; so I will leave that open to discussion.

BTW,Joseph suggestion may not work since that download is available for x86 architecture.

Rafael Salas

Export Data to MS Access file

Hi guys,
I use Data Flow Task to export data from SQL table to MS Access file.
In Connection Manager for Data Flow Destination task I specify
provider "Native OLEDB/MicrosoftJet 4.0" and path to Access file
in Database file name box. In design time I have no validation error.
Everything looks fine.
However in run time mode I'm getting error:

"Error: 0xC020801C <Datataset name> , MS Access File [1459]: The AcquireConnection method call to the connection manager
<connection manager name> failed with error code 0xC0202009.
Error: 0xC0047017 at <Datataset name>, DTS.Pipeline: component "MS Access File" (1459) failed validation and returned error code 0xC020801C."

Any thoughts?
Thanks.Anyone? :)|||Are you able to use your Access data source outside of SSIS?

Ideally, could you create a small .NET application to open an OLEDBConnection with the same connection string the SSIS connection manager has.

Can you also post the connection string from your Access connection here?

I saw no problems with using Access connections in SSIS designer (and Import/Export wizard) on my machine.|||Ok, what I figured out is if Access application (or Jet engine) isn't installed,
you cannot export data to mdb file.|||A Windows computer without the Jet engine should be a rare occurrence...beginning, I believe, with XP, it is installed with the operating system, independently of the Access application that uses it as its default database format.|||Well, it doesn't work on 2003 Server Enterprise x64 Edition (no MS Access installed), but works on XP with MS Access.|||I'm having the same problem. But I'm trying to import data from an access file. Can you let me know how you resolved your problem? Did you install the Jet engine? If yes, where do you get it from? Thanks in advance for your response.|||

If I am not wrong, the only thing you need is the MDAC which you can download here http://www.microsoft.com/downloads/details.aspx?DisplayLang=en&FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c

Create an OLE DB Connection in SSIS, then create a data flow control. In the data control flow, create an OLE DB source, and then maybe a flat file or OLE DB destination. Link Them together, and test it.

Hope it goes well.

|||

Metal_Fly wrote:

Well, it doesn't work on 2003 Server Enterprise x64 Edition (no MS Access installed), but works on XP with MS Access.

Metal_Fly,

If I understand correctly; you are getting the error only at runtime when the package is executed in a 64-bit machine; right?

If that is the case; you have to run the package in 32-bit mode in the server. I am not sure how you are running the package; but it seems like is running as 64-bit; so SSIS would try to load a Jet 64-bit driver which is not registered in the system.

For running the package in 32-bit; you need to use the DTexec.exe in the Program Files (x86) folder.

The command line should look like:

C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec /FILE "D:\Shared\DigitalCockpit\ETL\ETLControl.dtsx" /MAXCONCURRENT " -1" /CHECKPOINTING OFF /REPORTING EW

I believe you won't be able to use BIDS debugger in the server because it runs the package as 64-bit.

I am not sure if Jet is available for 64-bit; so I will leave that open to discussion.

BTW, Joseph suggestion may not work since that download is available for x86 architecture.

Rafael Salas

sql

Export Data in a specific format

I have a transaction table that I need to export for our financial system to import. It has a lot of zero's and a lot of spaces in the requested file.

Is there a way to do that and if there is how?

Can someone please help me in explaining how to extract data to a txt file as well. Here is my SQL statement thus far:

SELECT AccountNumber, CustomerID, Sum(CheckTotal) As CKT, Sum(PSD.Quantity) as QTY, Sum(ItemPrice * PSD.Quantity) As ItPrice, Sum(FCharge) as FCharge, Sum(SCharge) as SCharge, Sum((ItemPrice * PSD.Quantity) + FrankingCharge + ServiceCharge) As Total From PSD INNER JOIN Customer ON PSD.CustomerId = Customer.ID INNER JOIN Item ON PSD.ItemId = Item.ID Where PSD.Datecreated Between '12/01/2006 01:00:00 AM' and '12/31/2006 11:00:00 AM' Group By AccountNumber

ThanksWhat language are you using to do this?|||If you don't need to incorporate the export/import functionality in application - use Data Transformation Services (DTS).|||Guys,

I was away for the holiday, Sorry.

The language I am using is VB.

I am not familiar with DTS. Can you explain how that works please.

Thanks!!|||See here: data transformation services|||create a view (based on your query + include some more where condition to eliminate zeros & spaces) and then directly insert it to the new table.

like,

insert into ...(finance. table)
select ...
from (new view)|||use OSQL command line utility to get all the result in a txt file.

open command prompt and type OSQL /? for more options|||That sounds great, but as I am new to VB I might need some help on how to call that through vb. Any Ideas?

EXPORT Data From TXT FILE TO SQL SERVER

hi,

how can i export from sql sever table data to another text or csv file thorugh sql server stored procedure..import is working fine...

can u give me a solution ..

Thanks and Regards
ArulThere are ways to run though the File I/O in a stored procedure, but do you really want your sql server to have that type of file security abilities?

I'd suggest either doing through code, or running a loop that sets a file to become that text file. And blob that.

there's also DTS Capabilities that you can use.

Books Online is your best friend. Remember that.