Hi All!!
I need to export a whole datatable to the database, through bulk insert or any other method..
Can any body help me in that??
Yes, col's in the database table and datatable are same!!
Thx,
Regards,
Nilee..
Try the link below to use INSERT INTO with column list it give you control of the order of the insert. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_5cl0.asp
Showing posts with label export. Show all posts
Showing posts with label export. Show all posts
Wednesday, March 7, 2012
Friday, February 24, 2012
Bulk import stored procedure
I am trying to devise the best way to perform the following:
Import/export word documents into and out of a table in SQL 2005 for a VB
program to access. The user of the program would be able to choose what doc
they want to import into the table thus the location of the file and name of
the file can change. I have made a simple inserst statement that works when I
explicitly state the file path, but I need a variable file path, but I can't
seem to find how to do this or if there is a better way. The following is
what I have come up with so far even thought I can't execute it to create the
stored procedure because it won't accept the @.doclink in the Bulk statement.
Anyone have ideas on how to do this or a better way?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DocInsert]
(
@.PermitID int,
@.DocLink char(255),
@.Title nchar(100)
)
AS
BEGIN
SET NOCOUNT ON;
Insert into documents(doclink,title,created,createdby,doc)
Select @.doclink,@.title,getdate(),'John Doe',* from openrowset(bulk
@.doclink,single_blob,codepage='raw') as doc
END
GO"Joe S." <joecrew@.news.postalias> wrote in message
news:4E3401D2-24BA-41B6-ACFE-144D7D15772B@.microsoft.com...
>I am trying to devise the best way to perform the following:
> Import/export word documents into and out of a table in SQL 2005 for a VB
> program to access. The user of the program would be able to choose what
> doc
> they want to import into the table thus the location of the file and name
> of
> the file can change. I have made a simple inserst statement that works
> when I
> explicitly state the file path, but I need a variable file path, but I
> can't
> seem to find how to do this or if there is a better way. The following is
> what I have come up with so far even thought I can't execute it to create
> the
> stored procedure because it won't accept the @.doclink in the Bulk
> statement.
> Anyone have ideas on how to do this or a better way?
>
This isn't going to work in the general case. For this to work the SQL
Server must be able to access the file location. If the file is on the
client location, the pathing would be different and the server's account
wouldn't be able to open the file.
Instead of passing the file path to the procedure, pass the file bits. Just
declare the procedure to take a parameter of type varbinary(max) and pass
the file bits from VB. From VB load the file into a byte array and pass it
to through a SqlParameter.
David
Import/export word documents into and out of a table in SQL 2005 for a VB
program to access. The user of the program would be able to choose what doc
they want to import into the table thus the location of the file and name of
the file can change. I have made a simple inserst statement that works when I
explicitly state the file path, but I need a variable file path, but I can't
seem to find how to do this or if there is a better way. The following is
what I have come up with so far even thought I can't execute it to create the
stored procedure because it won't accept the @.doclink in the Bulk statement.
Anyone have ideas on how to do this or a better way?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DocInsert]
(
@.PermitID int,
@.DocLink char(255),
@.Title nchar(100)
)
AS
BEGIN
SET NOCOUNT ON;
Insert into documents(doclink,title,created,createdby,doc)
Select @.doclink,@.title,getdate(),'John Doe',* from openrowset(bulk
@.doclink,single_blob,codepage='raw') as doc
END
GO"Joe S." <joecrew@.news.postalias> wrote in message
news:4E3401D2-24BA-41B6-ACFE-144D7D15772B@.microsoft.com...
>I am trying to devise the best way to perform the following:
> Import/export word documents into and out of a table in SQL 2005 for a VB
> program to access. The user of the program would be able to choose what
> doc
> they want to import into the table thus the location of the file and name
> of
> the file can change. I have made a simple inserst statement that works
> when I
> explicitly state the file path, but I need a variable file path, but I
> can't
> seem to find how to do this or if there is a better way. The following is
> what I have come up with so far even thought I can't execute it to create
> the
> stored procedure because it won't accept the @.doclink in the Bulk
> statement.
> Anyone have ideas on how to do this or a better way?
>
This isn't going to work in the general case. For this to work the SQL
Server must be able to access the file location. If the file is on the
client location, the pathing would be different and the server's account
wouldn't be able to open the file.
Instead of passing the file path to the procedure, pass the file bits. Just
declare the procedure to take a parameter of type varbinary(max) and pass
the file bits from VB. From VB load the file into a byte array and pass it
to through a SqlParameter.
David
Bulk import stored procedure
I am trying to devise the best way to perform the following:
Import/export word documents into and out of a table in SQL 2005 for a VB
program to access. The user of the program would be able to choose what doc
they want to import into the table thus the location of the file and name o
f
the file can change. I have made a simple inserst statement that works when
I
explicitly state the file path, but I need a variable file path, but I can't
seem to find how to do this or if there is a better way. The following is
what I have come up with so far even thought I can't execute it to create th
e
stored procedure because it won't accept the @.doclink in the Bulk statement.
Anyone have ideas on how to do this or a better way?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DocInsert]
(
@.PermitID int,
@.DocLink char(255),
@.Title nchar(100)
)
AS
BEGIN
SET NOCOUNT ON;
Insert into documents(doclink,title,created,createdb
y,doc)
Select @.doclink,@.title,getdate(),'John Doe',* from openrowset(bulk
@.doclink,single_blob,codepage='raw') as doc
END
GO"Joe S." <joecrew@.news.postalias> wrote in message
news:4E3401D2-24BA-41B6-ACFE-144D7D15772B@.microsoft.com...
>I am trying to devise the best way to perform the following:
> Import/export word documents into and out of a table in SQL 2005 for a VB
> program to access. The user of the program would be able to choose what
> doc
> they want to import into the table thus the location of the file and name
> of
> the file can change. I have made a simple inserst statement that works
> when I
> explicitly state the file path, but I need a variable file path, but I
> can't
> seem to find how to do this or if there is a better way. The following is
> what I have come up with so far even thought I can't execute it to create
> the
> stored procedure because it won't accept the @.doclink in the Bulk
> statement.
> Anyone have ideas on how to do this or a better way?
>
This isn't going to work in the general case. For this to work the SQL
Server must be able to access the file location. If the file is on the
client location, the pathing would be different and the server's account
wouldn't be able to open the file.
Instead of passing the file path to the procedure, pass the file bits. Just
declare the procedure to take a parameter of type varbinary(max) and pass
the file bits from VB. From VB load the file into a byte array and pass it
to through a SqlParameter.
David
Import/export word documents into and out of a table in SQL 2005 for a VB
program to access. The user of the program would be able to choose what doc
they want to import into the table thus the location of the file and name o
f
the file can change. I have made a simple inserst statement that works when
I
explicitly state the file path, but I need a variable file path, but I can't
seem to find how to do this or if there is a better way. The following is
what I have come up with so far even thought I can't execute it to create th
e
stored procedure because it won't accept the @.doclink in the Bulk statement.
Anyone have ideas on how to do this or a better way?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DocInsert]
(
@.PermitID int,
@.DocLink char(255),
@.Title nchar(100)
)
AS
BEGIN
SET NOCOUNT ON;
Insert into documents(doclink,title,created,createdb
y,doc)
Select @.doclink,@.title,getdate(),'John Doe',* from openrowset(bulk
@.doclink,single_blob,codepage='raw') as doc
END
GO"Joe S." <joecrew@.news.postalias> wrote in message
news:4E3401D2-24BA-41B6-ACFE-144D7D15772B@.microsoft.com...
>I am trying to devise the best way to perform the following:
> Import/export word documents into and out of a table in SQL 2005 for a VB
> program to access. The user of the program would be able to choose what
> doc
> they want to import into the table thus the location of the file and name
> of
> the file can change. I have made a simple inserst statement that works
> when I
> explicitly state the file path, but I need a variable file path, but I
> can't
> seem to find how to do this or if there is a better way. The following is
> what I have come up with so far even thought I can't execute it to create
> the
> stored procedure because it won't accept the @.doclink in the Bulk
> statement.
> Anyone have ideas on how to do this or a better way?
>
This isn't going to work in the general case. For this to work the SQL
Server must be able to access the file location. If the file is on the
client location, the pathing would be different and the server's account
wouldn't be able to open the file.
Instead of passing the file path to the procedure, pass the file bits. Just
declare the procedure to take a parameter of type varbinary(max) and pass
the file bits from VB. From VB load the file into a byte array and pass it
to through a SqlParameter.
David
Sunday, February 19, 2012
Bulk extract/export BLOB data?
Is there a tool (like Bulk Image Insert (bii.exe) from sql resource
kit) that will bulk BLOB export data?
MortenTextcopy.exe might be what you need.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"hansiman" <hansi@.hotmail.com> wrote in message
news:7a8pp050vnkp7f1se31lpr9o6rljg2ie2h@.
4ax.com...
> Is there a tool (like Bulk Image Insert (bii.exe) from sql resource
> kit) that will bulk BLOB export data?
> Morten
kit) that will bulk BLOB export data?
MortenTextcopy.exe might be what you need.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"hansiman" <hansi@.hotmail.com> wrote in message
news:7a8pp050vnkp7f1se31lpr9o6rljg2ie2h@.
4ax.com...
> Is there a tool (like Bulk Image Insert (bii.exe) from sql resource
> kit) that will bulk BLOB export data?
> Morten
Bulk extract/export BLOB data?
Is there a tool (like Bulk Image Insert (bii.exe) from sql resource
kit) that will bulk BLOB export data?
Morten
Textcopy.exe might be what you need.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"hansiman" <hansi@.hotmail.com> wrote in message
news:7a8pp050vnkp7f1se31lpr9o6rljg2ie2h@.4ax.com...
> Is there a tool (like Bulk Image Insert (bii.exe) from sql resource
> kit) that will bulk BLOB export data?
> Morten
kit) that will bulk BLOB export data?
Morten
Textcopy.exe might be what you need.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"hansiman" <hansi@.hotmail.com> wrote in message
news:7a8pp050vnkp7f1se31lpr9o6rljg2ie2h@.4ax.com...
> Is there a tool (like Bulk Image Insert (bii.exe) from sql resource
> kit) that will bulk BLOB export data?
> Morten
Bulk extract/export BLOB data?
Is there a tool (like Bulk Image Insert (bii.exe) from sql resource
kit) that will bulk BLOB export data?
MortenTextcopy.exe might be what you need.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"hansiman" <hansi@.hotmail.com> wrote in message
news:7a8pp050vnkp7f1se31lpr9o6rljg2ie2h@.4ax.com...
> Is there a tool (like Bulk Image Insert (bii.exe) from sql resource
> kit) that will bulk BLOB export data?
> Morten
kit) that will bulk BLOB export data?
MortenTextcopy.exe might be what you need.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"hansiman" <hansi@.hotmail.com> wrote in message
news:7a8pp050vnkp7f1se31lpr9o6rljg2ie2h@.4ax.com...
> Is there a tool (like Bulk Image Insert (bii.exe) from sql resource
> kit) that will bulk BLOB export data?
> Morten
Bulk export/import of the table with nullable and computed fields
I have a program that is doing significant refresh of data in tables with nullable and computed (based on values of other fields of the sdame table) fields at least 2 times a week.
I found that it maybe the most efficient way to do this is to dump unchanged rows to flat files of the native format, to add changed rows to the same files,to truncate tables,to drop indices,to bulk insert from flat files and to restore indices.
Can somebody explain to me what value has to be assigned in the native format of the bulk export to the prefix of nullable field if it is null?
What will happened when you bulk insert without format file to the table with computed fields?
You would use a fmt file to define what should be imported. Here is an old post that should get you started.
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/6a02e9de79a21a81/50045cbec46f5a8f
|||
Dear oj,
Thank you for your attempt to help, but it is completely unrelated:
1.Guys are completely confused native data file format with char, I want to do native.
2.They are trying to skip identity field which I don't have.
3.They don't trying to create programmatically native format which I am trying to do.
4.I am talking about computed fields(from other fields of the same table.
5.They arte trying to skip identity field with fmt file which by default will be skipped anyway without fmt.
6.Maybe, this isn't relevant because bcp is the same, but I am using sql2k5 vs them sql2k
|||I found solution on question 1:
In order to insert null to the nullable field you have to put -1 value to the prefix of that field in the native format datafile.
Regarding question 2 I found that without fmt file if you simply dumped content of the table in native format with computed fields
it will be succesfully bulk inserted to the table.
If you changed value of computed field it got ignored.
I am not sure if this has anything to do with caching of execution plans, but I truncated table and changed name of datafile and result the same.
Bulk copy size
If I have a 70GB data file that I want to export using bulk copy, what size
can I estimate the resulting exported file to be?
Message posted via http://www.sqlmonster.com
It depends somewhat on what mode you use to export in (native, char, wide
etc) but it will likely be close to the size of the actual data in the db
and not the size of the db itself. Fragmentation or how full your data
pages are can play a big part. Try a sample and see.
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:20af1d8e71d34c7b97012ab8bd70ba2a@.SQLMonster.c om...
> If I have a 70GB data file that I want to export using bulk copy, what
> size
> can I estimate the resulting exported file to be?
> --
> Message posted via http://www.sqlmonster.com
can I estimate the resulting exported file to be?
Message posted via http://www.sqlmonster.com
It depends somewhat on what mode you use to export in (native, char, wide
etc) but it will likely be close to the size of the actual data in the db
and not the size of the db itself. Fragmentation or how full your data
pages are can play a big part. Try a sample and see.
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:20af1d8e71d34c7b97012ab8bd70ba2a@.SQLMonster.c om...
> If I have a 70GB data file that I want to export using bulk copy, what
> size
> can I estimate the resulting exported file to be?
> --
> Message posted via http://www.sqlmonster.com
Bulk copy size
If I have a 70GB data file that I want to export using bulk copy, what size
can I estimate the resulting exported file to be?
Message posted via http://www.droptable.comIt depends somewhat on what mode you use to export in (native, char, wide
etc) but it will likely be close to the size of the actual data in the db
and not the size of the db itself. Fragmentation or how full your data
pages are can play a big part. Try a sample and see.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:20af1d8e71d34c7b97012ab8bd70ba2a@.SQ
droptable.com...
> If I have a 70GB data file that I want to export using bulk copy, what
> size
> can I estimate the resulting exported file to be?
> --
> Message posted via http://www.droptable.com
can I estimate the resulting exported file to be?
Message posted via http://www.droptable.comIt depends somewhat on what mode you use to export in (native, char, wide
etc) but it will likely be close to the size of the actual data in the db
and not the size of the db itself. Fragmentation or how full your data
pages are can play a big part. Try a sample and see.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:20af1d8e71d34c7b97012ab8bd70ba2a@.SQ
droptable.com...
> If I have a 70GB data file that I want to export using bulk copy, what
> size
> can I estimate the resulting exported file to be?
> --
> Message posted via http://www.droptable.com
Bulk copy size
If I have a 70GB data file that I want to export using bulk copy, what size
can I estimate the resulting exported file to be?
--
Message posted via http://www.sqlmonster.comIt depends somewhat on what mode you use to export in (native, char, wide
etc) but it will likely be close to the size of the actual data in the db
and not the size of the db itself. Fragmentation or how full your data
pages are can play a big part. Try a sample and see.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:20af1d8e71d34c7b97012ab8bd70ba2a@.SQLMonster.com...
> If I have a 70GB data file that I want to export using bulk copy, what
> size
> can I estimate the resulting exported file to be?
> --
> Message posted via http://www.sqlmonster.com
can I estimate the resulting exported file to be?
--
Message posted via http://www.sqlmonster.comIt depends somewhat on what mode you use to export in (native, char, wide
etc) but it will likely be close to the size of the actual data in the db
and not the size of the db itself. Fragmentation or how full your data
pages are can play a big part. Try a sample and see.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:20af1d8e71d34c7b97012ab8bd70ba2a@.SQLMonster.com...
> If I have a 70GB data file that I want to export using bulk copy, what
> size
> can I estimate the resulting exported file to be?
> --
> Message posted via http://www.sqlmonster.com
Bulk copy rather than SQL Server Import and Export Wizard (Access
I am exporting data to roughly 250 Access 2.0 databases from tables in SQL
Server 2005. For each table, I open one copy of the wizard and select a
specifically named Access 2.0 database, export the table (it has to be edited
to the correct format to keep the size down - in other words, varchar with a
field size vs ntext). I do this 250 times. I suspect that bulk copy will do
this:
bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
But I am having trouble locating the documentation for exporting to an older
Access (DAO Access 2.0) database.
Help?
--
Regards,
Jamie> I suspect that bulk copy will do
> this:
> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> But I am having trouble locating the documentation for exporting to an
> older
> Access (DAO Access 2.0) database.
BCP creates text files. You could use it to create a delimited
(tab-delimited) by default) text file for each table and then import into
Access using the Access tools. I don't remember much about Access 2.0 but I
think you can create a macro to facilitate doing this repeatedly. The BCP
command line (assuming local default SQL instance):
bcp mydatabase.dbo.mycountrytable out
C:\ExportedFiels\mycountrytable.tab -T -c
Another option is to create a SSIS package to automate the tasks. SQL
Server Integration Services is the underlying technology that the SQL Server
2005 export wizard uses. Even if you don't currently know SSIS, you might
find learning more about SSIS useful if you find yourself often coping data
to/from Access.
You can create a template SSIS package by going through the export wizard
and saving the package to the file system. Open SQL Server BI Studio,
create a new Integration Services project and add the previously created
package file to the new project. You can then edit the package and tweak
according to your needs.
One SSIS approach is to move the package control flow tasks (preparation
script and data flow) into a new For Each Loop Container and then use a "for
each file enumerator" that points to a directory containing your Access
database files. You can find package examples at http://www.sqlis.com/.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:5A6971AE-454E-42B1-A728-C00AB649F623@.microsoft.com...
>I am exporting data to roughly 250 Access 2.0 databases from tables in SQL
> Server 2005. For each table, I open one copy of the wizard and select a
> specifically named Access 2.0 database, export the table (it has to be
> edited
> to the correct format to keep the size down - in other words, varchar with
> a
> field size vs ntext). I do this 250 times. I suspect that bulk copy will
> do
> this:
> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> But I am having trouble locating the documentation for exporting to an
> older
> Access (DAO Access 2.0) database.
> Help?
> --
> Regards,
> Jamie|||I meant to include the error but hit return and sent the previous out:
Error = [Microsoft][SQL Native Client]Unable to resolve column level
collations
Full line looks as follows:
bcp "use MyTable.MyCities; select * from ##TempExportData2" queryout
"C:\Halloran\ITALY_IT.mdb" -c -CRAW -T
I can create the file as a unicode text file - the collation is tough to
begin with - many characters between ascii 128 and 256. I'd really like to
be able to pop this out in the legacy Access 2.0 format.
--
Regards,
Jamie
"thejamie" wrote:
> I am exporting data to roughly 250 Access 2.0 databases from tables in SQL
> Server 2005. For each table, I open one copy of the wizard and select a
> specifically named Access 2.0 database, export the table (it has to be edited
> to the correct format to keep the size down - in other words, varchar with a
> field size vs ntext). I do this 250 times. I suspect that bulk copy will do
> this:
> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> But I am having trouble locating the documentation for exporting to an older
> Access (DAO Access 2.0) database.
> Help?
> --
> Regards,
> Jamie|||Super... I thought this could be done... I had saved the package already but
the enumerator helps.
--
Regards,
Jamie
"Dan Guzman" wrote:
> > I suspect that bulk copy will do
> > this:
> >
> > bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> >
> > But I am having trouble locating the documentation for exporting to an
> > older
> > Access (DAO Access 2.0) database.
> BCP creates text files. You could use it to create a delimited
> (tab-delimited) by default) text file for each table and then import into
> Access using the Access tools. I don't remember much about Access 2.0 but I
> think you can create a macro to facilitate doing this repeatedly. The BCP
> command line (assuming local default SQL instance):
> bcp mydatabase.dbo.mycountrytable out
> C:\ExportedFiels\mycountrytable.tab -T -c
> Another option is to create a SSIS package to automate the tasks. SQL
> Server Integration Services is the underlying technology that the SQL Server
> 2005 export wizard uses. Even if you don't currently know SSIS, you might
> find learning more about SSIS useful if you find yourself often coping data
> to/from Access.
> You can create a template SSIS package by going through the export wizard
> and saving the package to the file system. Open SQL Server BI Studio,
> create a new Integration Services project and add the previously created
> package file to the new project. You can then edit the package and tweak
> according to your needs.
> One SSIS approach is to move the package control flow tasks (preparation
> script and data flow) into a new For Each Loop Container and then use a "for
> each file enumerator" that points to a directory containing your Access
> database files. You can find package examples at http://www.sqlis.com/.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:5A6971AE-454E-42B1-A728-C00AB649F623@.microsoft.com...
> >I am exporting data to roughly 250 Access 2.0 databases from tables in SQL
> > Server 2005. For each table, I open one copy of the wizard and select a
> > specifically named Access 2.0 database, export the table (it has to be
> > edited
> > to the correct format to keep the size down - in other words, varchar with
> > a
> > field size vs ntext). I do this 250 times. I suspect that bulk copy will
> > do
> > this:
> >
> > bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> >
> > But I am having trouble locating the documentation for exporting to an
> > older
> > Access (DAO Access 2.0) database.
> >
> > Help?
> >
> > --
> > Regards,
> > Jamie
>|||> I can create the file as a unicode text file - the collation is tough to
> begin with - many characters between ascii 128 and 256. I'd really like
> to
> be able to pop this out in the legacy Access 2.0 format.
Have you tried the -w option instead of -C RAW?
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:FB9A702A-963F-4AB0-8C39-A1FFD1FC6E88@.microsoft.com...
>I meant to include the error but hit return and sent the previous out:
> Error = [Microsoft][SQL Native Client]Unable to resolve column level
> collations
> Full line looks as follows:
> bcp "use MyTable.MyCities; select * from ##TempExportData2" queryout
> "C:\Halloran\ITALY_IT.mdb" -c -CRAW -T
> I can create the file as a unicode text file - the collation is tough to
> begin with - many characters between ascii 128 and 256. I'd really like
> to
> be able to pop this out in the legacy Access 2.0 format.
> --
> Regards,
> Jamie
>
> "thejamie" wrote:
>> I am exporting data to roughly 250 Access 2.0 databases from tables in
>> SQL
>> Server 2005. For each table, I open one copy of the wizard and select a
>> specifically named Access 2.0 database, export the table (it has to be
>> edited
>> to the correct format to keep the size down - in other words, varchar
>> with a
>> field size vs ntext). I do this 250 times. I suspect that bulk copy
>> will do
>> this:
>> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
>> But I am having trouble locating the documentation for exporting to an
>> older
>> Access (DAO Access 2.0) database.
>> Help?
>> --
>> Regards,
>> Jamie|||I like the SSIS direction better however I am lost now as there are two
connection strings and both should change with each enumeration. Before the
first enumeration runs though, I get the following error and I am not sure
where to start on this to get it fixed: (note, I grabbed the foreachlooper
and inserted the template saved from a working export in the wizard package -
I created an expression for the output connection by enumerating the list of
mdb files in the folder. I don't know how to tell it that it is an Access
2.0 so I have assumed that since the package that was saved worked okay that
the Access 2.0 connection would be assumed)
Error at AfghanTemplate [Connection manager "DestinationConnectionOLEDB"]:
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code:
0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC
Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager]
Data source name not found and no default driver specified".
Error at Data Flow Task [Destination - CITYDB [22]]: SSIS Error Code
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection
method call to the connection manager "DestinationConnectionOLEDB" failed
with error code 0xC0202009. There may be error messages posted before this
with more information on why the AcquireConnection method call failed.
(Microsoft Visual Studio)
--
Regards,
Jamie
"Dan Guzman" wrote:
> > I can create the file as a unicode text file - the collation is tough to
> > begin with - many characters between ascii 128 and 256. I'd really like
> > to
> > be able to pop this out in the legacy Access 2.0 format.
> Have you tried the -w option instead of -C RAW?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:FB9A702A-963F-4AB0-8C39-A1FFD1FC6E88@.microsoft.com...
> >I meant to include the error but hit return and sent the previous out:
> >
> > Error = [Microsoft][SQL Native Client]Unable to resolve column level
> > collations
> >
> > Full line looks as follows:
> > bcp "use MyTable.MyCities; select * from ##TempExportData2" queryout
> > "C:\Halloran\ITALY_IT.mdb" -c -CRAW -T
> >
> > I can create the file as a unicode text file - the collation is tough to
> > begin with - many characters between ascii 128 and 256. I'd really like
> > to
> > be able to pop this out in the legacy Access 2.0 format.
> >
> > --
> > Regards,
> > Jamie
> >
> >
> > "thejamie" wrote:
> >
> >> I am exporting data to roughly 250 Access 2.0 databases from tables in
> >> SQL
> >> Server 2005. For each table, I open one copy of the wizard and select a
> >> specifically named Access 2.0 database, export the table (it has to be
> >> edited
> >> to the correct format to keep the size down - in other words, varchar
> >> with a
> >> field size vs ntext). I do this 250 times. I suspect that bulk copy
> >> will do
> >> this:
> >>
> >> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> >>
> >> But I am having trouble locating the documentation for exporting to an
> >> older
> >> Access (DAO Access 2.0) database.
> >>
> >> Help?
> >>
> >> --
> >> Regards,
> >> Jamie
>|||I neglected to mention I am running an x64 copy of XP. It may have something
to do with it. I just discovered that mdac appears to be incorrectly
installed on this copy.
Error when trying to import a flat file:
An error occurred which the SQL Server Integration Services Wizard was not
prepared to handle.
--
Regards,
Jamie
"Dan Guzman" wrote:
> > I suspect that bulk copy will do
> > this:
> >
> > bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> >
> > But I am having trouble locating the documentation for exporting to an
> > older
> > Access (DAO Access 2.0) database.
> BCP creates text files. You could use it to create a delimited
> (tab-delimited) by default) text file for each table and then import into
> Access using the Access tools. I don't remember much about Access 2.0 but I
> think you can create a macro to facilitate doing this repeatedly. The BCP
> command line (assuming local default SQL instance):
> bcp mydatabase.dbo.mycountrytable out
> C:\ExportedFiels\mycountrytable.tab -T -c
> Another option is to create a SSIS package to automate the tasks. SQL
> Server Integration Services is the underlying technology that the SQL Server
> 2005 export wizard uses. Even if you don't currently know SSIS, you might
> find learning more about SSIS useful if you find yourself often coping data
> to/from Access.
> You can create a template SSIS package by going through the export wizard
> and saving the package to the file system. Open SQL Server BI Studio,
> create a new Integration Services project and add the previously created
> package file to the new project. You can then edit the package and tweak
> according to your needs.
> One SSIS approach is to move the package control flow tasks (preparation
> script and data flow) into a new For Each Loop Container and then use a "for
> each file enumerator" that points to a directory containing your Access
> database files. You can find package examples at http://www.sqlis.com/.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:5A6971AE-454E-42B1-A728-C00AB649F623@.microsoft.com...
> >I am exporting data to roughly 250 Access 2.0 databases from tables in SQL
> > Server 2005. For each table, I open one copy of the wizard and select a
> > specifically named Access 2.0 database, export the table (it has to be
> > edited
> > to the correct format to keep the size down - in other words, varchar with
> > a
> > field size vs ntext). I do this 250 times. I suspect that bulk copy will
> > do
> > this:
> >
> > bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> >
> > But I am having trouble locating the documentation for exporting to an
> > older
> > Access (DAO Access 2.0) database.
> >
> > Help?
> >
> > --
> > Regards,
> > Jamie
>|||>I like the SSIS direction better however I am lost now as there are two
> connection strings and both should change with each enumeration.
Two connection strings? I was under the impression that you had only one
SQL Server source database and was planning to push that SQL data to many
different Access databases. Since you need to change both connection
strings for each iteration, you'll need to use a different enumerator and 2
variables - one for the Access file and another for the SQL instance.
In the SQL Server connection, use an expression to set the ConnectionString
property using the server name variable. For example:
"Data Source=" + @.SqlServerName + ";Initial
Catalog=MyDatabase;Provider=SQLNCLI;Integrated Security=SSPI;Auto
Translate=false;"
In the Access database connection, use an expression to set the
ConnectionString property using the file path variable. For example:
"Data Source=" + @.FileName + ";OLE DB
SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;"
Set the Foreach Loop container Collection Enumerator to one that can provide
both the SQL Server name and Access file path. The example below uses a
Foreach NodeList Enumerator to extract the values from an embedded XML
document. Set the document source type to DirectInput and enter an XML
string like the one below as the DocumentSource:
<CopyList>
<Task>
<SqlServerName>SQLSERVER1</SqlServerName>
<FileName>C:\db1.mdb</FileName>
</Task>
<Task>
<SqlServerName>SQLSERVER2</SqlServerName>
<FileName>C:\db2.mdb</FileName>
</Task>
</CopyList>
Set the remaining collection properties as follows:
EnumerationType: ElementCollection
OuterXPathStringSourceType: DirectInput
OuterXPathString: /CopyList/Task
InnerElementType: NoteText
InnerXPathStringSourceType: DirectInput
InnerXPathString: *
Under VariableMappings, map the server name variable to index 0 and the file
name variable to index 1.
Or course, you can use other sources for the server/file list. Just change
the enumerator accordingly.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:E7FA5A2C-CE9B-4A53-9E43-E5AE00CCDFBF@.microsoft.com...
>I like the SSIS direction better however I am lost now as there are two
> connection strings and both should change with each enumeration. Before
> the
> first enumeration runs though, I get the following error and I am not sure
> where to start on this to get it fixed: (note, I grabbed the foreachlooper
> and inserted the template saved from a working export in the wizard
> package -
> I created an expression for the output connection by enumerating the list
> of
> mdb files in the folder. I don't know how to tell it that it is an Access
> 2.0 so I have assumed that since the package that was saved worked okay
> that
> the Access 2.0 connection would be assumed)
> Error at AfghanTemplate [Connection manager "DestinationConnectionOLEDB"]:
> SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error
> code:
> 0x80004005.
> An OLE DB record is available. Source: "Microsoft OLE DB Provider for
> ODBC
> Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver
> Manager]
> Data source name not found and no default driver specified".
> Error at Data Flow Task [Destination - CITYDB [22]]: SSIS Error Code
> DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection
> method call to the connection manager "DestinationConnectionOLEDB" failed
> with error code 0xC0202009. There may be error messages posted before
> this
> with more information on why the AcquireConnection method call failed.
> (Microsoft Visual Studio)
> --
> Regards,
> Jamie
>
> "Dan Guzman" wrote:
>> > I can create the file as a unicode text file - the collation is tough
>> > to
>> > begin with - many characters between ascii 128 and 256. I'd really
>> > like
>> > to
>> > be able to pop this out in the legacy Access 2.0 format.
>> Have you tried the -w option instead of -C RAW?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:FB9A702A-963F-4AB0-8C39-A1FFD1FC6E88@.microsoft.com...
>> >I meant to include the error but hit return and sent the previous out:
>> >
>> > Error = [Microsoft][SQL Native Client]Unable to resolve column level
>> > collations
>> >
>> > Full line looks as follows:
>> > bcp "use MyTable.MyCities; select * from ##TempExportData2" queryout
>> > "C:\Halloran\ITALY_IT.mdb" -c -CRAW -T
>> >
>> > I can create the file as a unicode text file - the collation is tough
>> > to
>> > begin with - many characters between ascii 128 and 256. I'd really
>> > like
>> > to
>> > be able to pop this out in the legacy Access 2.0 format.
>> >
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "thejamie" wrote:
>> >
>> >> I am exporting data to roughly 250 Access 2.0 databases from tables in
>> >> SQL
>> >> Server 2005. For each table, I open one copy of the wizard and select
>> >> a
>> >> specifically named Access 2.0 database, export the table (it has to be
>> >> edited
>> >> to the correct format to keep the size down - in other words, varchar
>> >> with a
>> >> field size vs ntext). I do this 250 times. I suspect that bulk copy
>> >> will do
>> >> this:
>> >>
>> >> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
>> >>
>> >> But I am having trouble locating the documentation for exporting to an
>> >> older
>> >> Access (DAO Access 2.0) database.
>> >>
>> >> Help?
>> >>
>> >> --
>> >> Regards,
>> >> Jamie|||Dan,
I did not mean to confuse you. I have a new laptop that is sitting in the
system and I see that it has 64 bit issues. It needs to be reinstalled.
MDAC was not working properly. At this point, and because I do some work
from the laptop and some from the desktop, I was letting you know (in my own
strange way) that I will need to revisit this because I don't know if it is
the machine or something in the SSIS code that is causing problems. You gave
me the correct answer the first time. I appreciate that. Thank you.
--
Regards,
Jamie
"Dan Guzman" wrote:
> >I like the SSIS direction better however I am lost now as there are two
> > connection strings and both should change with each enumeration.
> Two connection strings? I was under the impression that you had only one
> SQL Server source database and was planning to push that SQL data to many
> different Access databases. Since you need to change both connection
> strings for each iteration, you'll need to use a different enumerator and 2
> variables - one for the Access file and another for the SQL instance.
> In the SQL Server connection, use an expression to set the ConnectionString
> property using the server name variable. For example:
> "Data Source=" + @.SqlServerName + ";Initial
> Catalog=MyDatabase;Provider=SQLNCLI;Integrated Security=SSPI;Auto
> Translate=false;"
> In the Access database connection, use an expression to set the
> ConnectionString property using the file path variable. For example:
> "Data Source=" + @.FileName + ";OLE DB
> SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;"
> Set the Foreach Loop container Collection Enumerator to one that can provide
> both the SQL Server name and Access file path. The example below uses a
> Foreach NodeList Enumerator to extract the values from an embedded XML
> document. Set the document source type to DirectInput and enter an XML
> string like the one below as the DocumentSource:
> <CopyList>
> <Task>
> <SqlServerName>SQLSERVER1</SqlServerName>
> <FileName>C:\db1.mdb</FileName>
> </Task>
> <Task>
> <SqlServerName>SQLSERVER2</SqlServerName>
> <FileName>C:\db2.mdb</FileName>
> </Task>
> </CopyList>
> Set the remaining collection properties as follows:
> EnumerationType: ElementCollection
> OuterXPathStringSourceType: DirectInput
> OuterXPathString: /CopyList/Task
> InnerElementType: NoteText
> InnerXPathStringSourceType: DirectInput
> InnerXPathString: *
> Under VariableMappings, map the server name variable to index 0 and the file
> name variable to index 1.
> Or course, you can use other sources for the server/file list. Just change
> the enumerator accordingly.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:E7FA5A2C-CE9B-4A53-9E43-E5AE00CCDFBF@.microsoft.com...
> >I like the SSIS direction better however I am lost now as there are two
> > connection strings and both should change with each enumeration. Before
> > the
> > first enumeration runs though, I get the following error and I am not sure
> > where to start on this to get it fixed: (note, I grabbed the foreachlooper
> > and inserted the template saved from a working export in the wizard
> > package -
> > I created an expression for the output connection by enumerating the list
> > of
> > mdb files in the folder. I don't know how to tell it that it is an Access
> > 2.0 so I have assumed that since the package that was saved worked okay
> > that
> > the Access 2.0 connection would be assumed)
> >
> > Error at AfghanTemplate [Connection manager "DestinationConnectionOLEDB"]:
> > SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error
> > code:
> > 0x80004005.
> > An OLE DB record is available. Source: "Microsoft OLE DB Provider for
> > ODBC
> > Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver
> > Manager]
> > Data source name not found and no default driver specified".
> >
> > Error at Data Flow Task [Destination - CITYDB [22]]: SSIS Error Code
> > DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection
> > method call to the connection manager "DestinationConnectionOLEDB" failed
> > with error code 0xC0202009. There may be error messages posted before
> > this
> > with more information on why the AcquireConnection method call failed.
> >
> > (Microsoft Visual Studio)
> > --
> > Regards,
> > Jamie
> >
> >
> > "Dan Guzman" wrote:
> >
> >> > I can create the file as a unicode text file - the collation is tough
> >> > to
> >> > begin with - many characters between ascii 128 and 256. I'd really
> >> > like
> >> > to
> >> > be able to pop this out in the legacy Access 2.0 format.
> >>
> >> Have you tried the -w option instead of -C RAW?
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >> http://weblogs.sqlteam.com/dang/
> >>
> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> news:FB9A702A-963F-4AB0-8C39-A1FFD1FC6E88@.microsoft.com...
> >> >I meant to include the error but hit return and sent the previous out:
> >> >
> >> > Error = [Microsoft][SQL Native Client]Unable to resolve column level
> >> > collations
> >> >
> >> > Full line looks as follows:
> >> > bcp "use MyTable.MyCities; select * from ##TempExportData2" queryout
> >> > "C:\Halloran\ITALY_IT.mdb" -c -CRAW -T
> >> >
> >> > I can create the file as a unicode text file - the collation is tough
> >> > to
> >> > begin with - many characters between ascii 128 and 256. I'd really
> >> > like
> >> > to
> >> > be able to pop this out in the legacy Access 2.0 format.
> >> >
> >> > --
> >> > Regards,
> >> > Jamie
> >> >
> >> >
> >> > "thejamie" wrote:
> >> >
> >> >> I am exporting data to roughly 250 Access 2.0 databases from tables in
> >> >> SQL
> >> >> Server 2005. For each table, I open one copy of the wizard and select
> >> >> a
> >> >> specifically named Access 2.0 database, export the table (it has to be
> >> >> edited
> >> >> to the correct format to keep the size down - in other words, varchar
> >> >> with a
> >> >> field size vs ntext). I do this 250 times. I suspect that bulk copy
> >> >> will do
> >> >> this:
> >> >>
> >> >> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> >> >>
> >> >> But I am having trouble locating the documentation for exporting to an
> >> >> older
> >> >> Access (DAO Access 2.0) database.
> >> >>
> >> >> Help?
> >> >>
> >> >> --
> >> >> Regards,
> >> >> Jamie
> >>
>|||Keep in mind that if you will need 64-bit drivers to run 64-bit SSIS.
However, you can set the Run64bitRuntime SSIS project property to False in
order to use 32-bit drivers. I had to do that on my 64-bit Vista
development box to avoid a "Class not registered" error for the
Microsoft.Jet.OLEDB.4.0 provider. The same package runs fine with
Run64bitRuntime set to False.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:3400EAB6-CBB6-4074-8EE9-5B4A189CEBAD@.microsoft.com...
> Dan,
> I did not mean to confuse you. I have a new laptop that is sitting in the
> system and I see that it has 64 bit issues. It needs to be reinstalled.
> MDAC was not working properly. At this point, and because I do some work
> from the laptop and some from the desktop, I was letting you know (in my
> own
> strange way) that I will need to revisit this because I don't know if it
> is
> the machine or something in the SSIS code that is causing problems. You
> gave
> me the correct answer the first time. I appreciate that. Thank you.
> --
> Regards,
> Jamie
>
> "Dan Guzman" wrote:
>> >I like the SSIS direction better however I am lost now as there are two
>> > connection strings and both should change with each enumeration.
>> Two connection strings? I was under the impression that you had only one
>> SQL Server source database and was planning to push that SQL data to many
>> different Access databases. Since you need to change both connection
>> strings for each iteration, you'll need to use a different enumerator and
>> 2
>> variables - one for the Access file and another for the SQL instance.
>> In the SQL Server connection, use an expression to set the
>> ConnectionString
>> property using the server name variable. For example:
>> "Data Source=" + @.SqlServerName + ";Initial
>> Catalog=MyDatabase;Provider=SQLNCLI;Integrated Security=SSPI;Auto
>> Translate=false;"
>> In the Access database connection, use an expression to set the
>> ConnectionString property using the file path variable. For example:
>> "Data Source=" + @.FileName + ";OLE DB
>> SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;"
>> Set the Foreach Loop container Collection Enumerator to one that can
>> provide
>> both the SQL Server name and Access file path. The example below uses a
>> Foreach NodeList Enumerator to extract the values from an embedded XML
>> document. Set the document source type to DirectInput and enter an XML
>> string like the one below as the DocumentSource:
>> <CopyList>
>> <Task>
>> <SqlServerName>SQLSERVER1</SqlServerName>
>> <FileName>C:\db1.mdb</FileName>
>> </Task>
>> <Task>
>> <SqlServerName>SQLSERVER2</SqlServerName>
>> <FileName>C:\db2.mdb</FileName>
>> </Task>
>> </CopyList>
>> Set the remaining collection properties as follows:
>> EnumerationType: ElementCollection
>> OuterXPathStringSourceType: DirectInput
>> OuterXPathString: /CopyList/Task
>> InnerElementType: NoteText
>> InnerXPathStringSourceType: DirectInput
>> InnerXPathString: *
>> Under VariableMappings, map the server name variable to index 0 and the
>> file
>> name variable to index 1.
>> Or course, you can use other sources for the server/file list. Just
>> change
>> the enumerator accordingly.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:E7FA5A2C-CE9B-4A53-9E43-E5AE00CCDFBF@.microsoft.com...
>> >I like the SSIS direction better however I am lost now as there are two
>> > connection strings and both should change with each enumeration.
>> > Before
>> > the
>> > first enumeration runs though, I get the following error and I am not
>> > sure
>> > where to start on this to get it fixed: (note, I grabbed the
>> > foreachlooper
>> > and inserted the template saved from a working export in the wizard
>> > package -
>> > I created an expression for the output connection by enumerating the
>> > list
>> > of
>> > mdb files in the folder. I don't know how to tell it that it is an
>> > Access
>> > 2.0 so I have assumed that since the package that was saved worked okay
>> > that
>> > the Access 2.0 connection would be assumed)
>> >
>> > Error at AfghanTemplate [Connection manager
>> > "DestinationConnectionOLEDB"]:
>> > SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error
>> > code:
>> > 0x80004005.
>> > An OLE DB record is available. Source: "Microsoft OLE DB Provider for
>> > ODBC
>> > Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver
>> > Manager]
>> > Data source name not found and no default driver specified".
>> >
>> > Error at Data Flow Task [Destination - CITYDB [22]]: SSIS Error Code
>> > DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The
>> > AcquireConnection
>> > method call to the connection manager "DestinationConnectionOLEDB"
>> > failed
>> > with error code 0xC0202009. There may be error messages posted before
>> > this
>> > with more information on why the AcquireConnection method call failed.
>> >
>> > (Microsoft Visual Studio)
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "Dan Guzman" wrote:
>> >
>> >> > I can create the file as a unicode text file - the collation is
>> >> > tough
>> >> > to
>> >> > begin with - many characters between ascii 128 and 256. I'd really
>> >> > like
>> >> > to
>> >> > be able to pop this out in the legacy Access 2.0 format.
>> >>
>> >> Have you tried the -w option instead of -C RAW?
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >> http://weblogs.sqlteam.com/dang/
>> >>
>> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> news:FB9A702A-963F-4AB0-8C39-A1FFD1FC6E88@.microsoft.com...
>> >> >I meant to include the error but hit return and sent the previous
>> >> >out:
>> >> >
>> >> > Error = [Microsoft][SQL Native Client]Unable to resolve column level
>> >> > collations
>> >> >
>> >> > Full line looks as follows:
>> >> > bcp "use MyTable.MyCities; select * from ##TempExportData2" queryout
>> >> > "C:\Halloran\ITALY_IT.mdb" -c -CRAW -T
>> >> >
>> >> > I can create the file as a unicode text file - the collation is
>> >> > tough
>> >> > to
>> >> > begin with - many characters between ascii 128 and 256. I'd really
>> >> > like
>> >> > to
>> >> > be able to pop this out in the legacy Access 2.0 format.
>> >> >
>> >> > --
>> >> > Regards,
>> >> > Jamie
>> >> >
>> >> >
>> >> > "thejamie" wrote:
>> >> >
>> >> >> I am exporting data to roughly 250 Access 2.0 databases from tables
>> >> >> in
>> >> >> SQL
>> >> >> Server 2005. For each table, I open one copy of the wizard and
>> >> >> select
>> >> >> a
>> >> >> specifically named Access 2.0 database, export the table (it has to
>> >> >> be
>> >> >> edited
>> >> >> to the correct format to keep the size down - in other words,
>> >> >> varchar
>> >> >> with a
>> >> >> field size vs ntext). I do this 250 times. I suspect that bulk
>> >> >> copy
>> >> >> will do
>> >> >> this:
>> >> >>
>> >> >> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
>> >> >>
>> >> >> But I am having trouble locating the documentation for exporting to
>> >> >> an
>> >> >> older
>> >> >> Access (DAO Access 2.0) database.
>> >> >>
>> >> >> Help?
>> >> >>
>> >> >> --
>> >> >> Regards,
>> >> >> Jamie
>> >>|||Yep, that helps, bigtime.
--
Regards,
Jamie
"Dan Guzman" wrote:
> Keep in mind that if you will need 64-bit drivers to run 64-bit SSIS.
> However, you can set the Run64bitRuntime SSIS project property to False in
> order to use 32-bit drivers. I had to do that on my 64-bit Vista
> development box to avoid a "Class not registered" error for the
> Microsoft.Jet.OLEDB.4.0 provider. The same package runs fine with
> Run64bitRuntime set to False.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:3400EAB6-CBB6-4074-8EE9-5B4A189CEBAD@.microsoft.com...
> > Dan,
> > I did not mean to confuse you. I have a new laptop that is sitting in the
> > system and I see that it has 64 bit issues. It needs to be reinstalled.
> > MDAC was not working properly. At this point, and because I do some work
> > from the laptop and some from the desktop, I was letting you know (in my
> > own
> > strange way) that I will need to revisit this because I don't know if it
> > is
> > the machine or something in the SSIS code that is causing problems. You
> > gave
> > me the correct answer the first time. I appreciate that. Thank you.
> > --
> > Regards,
> > Jamie
> >
> >
> > "Dan Guzman" wrote:
> >
> >> >I like the SSIS direction better however I am lost now as there are two
> >> > connection strings and both should change with each enumeration.
> >>
> >> Two connection strings? I was under the impression that you had only one
> >> SQL Server source database and was planning to push that SQL data to many
> >> different Access databases. Since you need to change both connection
> >> strings for each iteration, you'll need to use a different enumerator and
> >> 2
> >> variables - one for the Access file and another for the SQL instance.
> >>
> >> In the SQL Server connection, use an expression to set the
> >> ConnectionString
> >> property using the server name variable. For example:
> >>
> >> "Data Source=" + @.SqlServerName + ";Initial
> >> Catalog=MyDatabase;Provider=SQLNCLI;Integrated Security=SSPI;Auto
> >> Translate=false;"
> >>
> >> In the Access database connection, use an expression to set the
> >> ConnectionString property using the file path variable. For example:
> >>
> >> "Data Source=" + @.FileName + ";OLE DB
> >> SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;"
> >>
> >> Set the Foreach Loop container Collection Enumerator to one that can
> >> provide
> >> both the SQL Server name and Access file path. The example below uses a
> >> Foreach NodeList Enumerator to extract the values from an embedded XML
> >> document. Set the document source type to DirectInput and enter an XML
> >> string like the one below as the DocumentSource:
> >>
> >> <CopyList>
> >> <Task>
> >> <SqlServerName>SQLSERVER1</SqlServerName>
> >> <FileName>C:\db1.mdb</FileName>
> >> </Task>
> >> <Task>
> >> <SqlServerName>SQLSERVER2</SqlServerName>
> >> <FileName>C:\db2.mdb</FileName>
> >> </Task>
> >> </CopyList>
> >>
> >> Set the remaining collection properties as follows:
> >>
> >> EnumerationType: ElementCollection
> >> OuterXPathStringSourceType: DirectInput
> >> OuterXPathString: /CopyList/Task
> >> InnerElementType: NoteText
> >> InnerXPathStringSourceType: DirectInput
> >> InnerXPathString: *
> >>
> >> Under VariableMappings, map the server name variable to index 0 and the
> >> file
> >> name variable to index 1.
> >>
> >> Or course, you can use other sources for the server/file list. Just
> >> change
> >> the enumerator accordingly.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >> http://weblogs.sqlteam.com/dang/
> >>
> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> news:E7FA5A2C-CE9B-4A53-9E43-E5AE00CCDFBF@.microsoft.com...
> >> >I like the SSIS direction better however I am lost now as there are two
> >> > connection strings and both should change with each enumeration.
> >> > Before
> >> > the
> >> > first enumeration runs though, I get the following error and I am not
> >> > sure
> >> > where to start on this to get it fixed: (note, I grabbed the
> >> > foreachlooper
> >> > and inserted the template saved from a working export in the wizard
> >> > package -
> >> > I created an expression for the output connection by enumerating the
> >> > list
> >> > of
> >> > mdb files in the folder. I don't know how to tell it that it is an
> >> > Access
> >> > 2.0 so I have assumed that since the package that was saved worked okay
> >> > that
> >> > the Access 2.0 connection would be assumed)
> >> >
> >> > Error at AfghanTemplate [Connection manager
> >> > "DestinationConnectionOLEDB"]:
> >> > SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error
> >> > code:
> >> > 0x80004005.
> >> > An OLE DB record is available. Source: "Microsoft OLE DB Provider for
> >> > ODBC
> >> > Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver
> >> > Manager]
> >> > Data source name not found and no default driver specified".
> >> >
> >> > Error at Data Flow Task [Destination - CITYDB [22]]: SSIS Error Code
> >> > DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The
> >> > AcquireConnection
> >> > method call to the connection manager "DestinationConnectionOLEDB"
> >> > failed
> >> > with error code 0xC0202009. There may be error messages posted before
> >> > this
> >> > with more information on why the AcquireConnection method call failed.
> >> >
> >> > (Microsoft Visual Studio)
> >> > --
> >> > Regards,
> >> > Jamie
> >> >
> >> >
> >> > "Dan Guzman" wrote:
> >> >
> >> >> > I can create the file as a unicode text file - the collation is
> >> >> > tough
> >> >> > to
> >> >> > begin with - many characters between ascii 128 and 256. I'd really
> >> >> > like
> >> >> > to
> >> >> > be able to pop this out in the legacy Access 2.0 format.
> >> >>
> >> >> Have you tried the -w option instead of -C RAW?
> >> >>
> >> >> --
> >> >> Hope this helps.
> >> >>
> >> >> Dan Guzman
> >> >> SQL Server MVP
> >> >> http://weblogs.sqlteam.com/dang/
> >> >>
> >> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> >> news:FB9A702A-963F-4AB0-8C39-A1FFD1FC6E88@.microsoft.com...
> >> >> >I meant to include the error but hit return and sent the previous
> >> >> >out:
> >> >> >
> >> >> > Error = [Microsoft][SQL Native Client]Unable to resolve column level
> >> >> > collations
> >> >> >
> >> >> > Full line looks as follows:
> >> >> > bcp "use MyTable.MyCities; select * from ##TempExportData2" queryout
> >> >> > "C:\Halloran\ITALY_IT.mdb" -c -CRAW -T
> >> >> >
> >> >> > I can create the file as a unicode text file - the collation is
> >> >> > tough
> >> >> > to
> >> >> > begin with - many characters between ascii 128 and 256. I'd really
> >> >> > like
> >> >> > to
> >> >> > be able to pop this out in the legacy Access 2.0 format.
> >> >> >
> >> >> > --
> >> >> > Regards,
> >> >> > Jamie
> >> >> >
> >> >> >
> >> >> > "thejamie" wrote:
> >> >> >
> >> >> >> I am exporting data to roughly 250 Access 2.0 databases from tables
> >> >> >> in
> >> >> >> SQL
> >> >> >> Server 2005. For each table, I open one copy of the wizard and
> >> >> >> select
> >> >> >> a
> >> >> >> specifically named Access 2.0 database, export the table (it has to
> >> >> >> be
> >> >> >> edited
> >> >> >> to the correct format to keep the size down - in other words,
> >> >> >> varchar
> >> >> >> with a
> >> >> >> field size vs ntext). I do this 250 times. I suspect that bulk
> >> >> >> copy
> >> >> >> will do
> >> >> >> this:
> >> >> >>
> >> >> >> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> >> >> >>
> >> >> >> But I am having trouble locating the documentation for exporting to
> >> >> >> an
> >> >> >> older
> >> >> >> Access (DAO Access 2.0) database.
> >> >> >>
> >> >> >> Help?
> >> >> >>
> >> >> >> --
> >> >> >> Regards,
> >> >> >> Jamie
> >> >>
> >>
>|||That first connection from the database is also the same name as the database
itself... for example if the table in the SQL Database is call Mongolia, then
the Access Database target is called Mongolia.MDB. The target databases can
be quickly enumerated as they are simply files in a folder.
What would be really nice would be to use the same variable in the source
connection as I use in the target connection.
I can think of a couple of ways to do this... first is to create a text file
with all the table names in them and somehow script that as the source
variable, but I can't help but think I should also be able to enumerate the
table names that are in the source folder with the variable name from the
destination folder.
It would work like:
Source table Mongolia (source connection)=> data conversion => Destination
database Mongolia.mdb (destination file variable name)
Since the destination name and the source name are the same (minus the
".mdb" extension) could I somehow use scripting to remove the ".mdb" from the
destination variable name?
ie Replace(@.[User::@.mdbVariablename],'.mdb','') From your example below it
almost appears that I could also write an XML script to provide the named
variables of the tables.
Also, it looks like (worst case) I can actually copy the dataflow object 252
times and change the source and destination name physically - it would take
less time than what I do currently.
--
Regards,
Jamie
"Dan Guzman" wrote:
> >I like the SSIS direction better however I am lost now as there are two
> > connection strings and both should change with each enumeration.
> Two connection strings? I was under the impression that you had only one
> SQL Server source database and was planning to push that SQL data to many
> different Access databases. Since you need to change both connection
> strings for each iteration, you'll need to use a different enumerator and 2
> variables - one for the Access file and another for the SQL instance.
> In the SQL Server connection, use an expression to set the ConnectionString
> property using the server name variable. For example:
> "Data Source=" + @.SqlServerName + ";Initial
> Catalog=MyDatabase;Provider=SQLNCLI;Integrated Security=SSPI;Auto
> Translate=false;"
> In the Access database connection, use an expression to set the
> ConnectionString property using the file path variable. For example:
> "Data Source=" + @.FileName + ";OLE DB
> SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;"
> Set the Foreach Loop container Collection Enumerator to one that can provide
> both the SQL Server name and Access file path. The example below uses a
> Foreach NodeList Enumerator to extract the values from an embedded XML
> document. Set the document source type to DirectInput and enter an XML
> string like the one below as the DocumentSource:
> <CopyList>
> <Task>
> <SqlServerName>SQLSERVER1</SqlServerName>
> <FileName>C:\db1.mdb</FileName>
> </Task>
> <Task>
> <SqlServerName>SQLSERVER2</SqlServerName>
> <FileName>C:\db2.mdb</FileName>
> </Task>
> </CopyList>
> Set the remaining collection properties as follows:
> EnumerationType: ElementCollection
> OuterXPathStringSourceType: DirectInput
> OuterXPathString: /CopyList/Task
> InnerElementType: NoteText
> InnerXPathStringSourceType: DirectInput
> InnerXPathString: *
> Under VariableMappings, map the server name variable to index 0 and the file
> name variable to index 1.
> Or course, you can use other sources for the server/file list. Just change
> the enumerator accordingly.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:E7FA5A2C-CE9B-4A53-9E43-E5AE00CCDFBF@.microsoft.com...
> >I like the SSIS direction better however I am lost now as there are two
> > connection strings and both should change with each enumeration. Before
> > the
> > first enumeration runs though, I get the following error and I am not sure
> > where to start on this to get it fixed: (note, I grabbed the foreachlooper
> > and inserted the template saved from a working export in the wizard
> > package -
> > I created an expression for the output connection by enumerating the list
> > of
> > mdb files in the folder. I don't know how to tell it that it is an Access
> > 2.0 so I have assumed that since the package that was saved worked okay
> > that
> > the Access 2.0 connection would be assumed)
> >
> > Error at AfghanTemplate [Connection manager "DestinationConnectionOLEDB"]:
> > SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error
> > code:
> > 0x80004005.
> > An OLE DB record is available. Source: "Microsoft OLE DB Provider for
> > ODBC
> > Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver
> > Manager]
> > Data source name not found and no default driver specified".
> >
> > Error at Data Flow Task [Destination - CITYDB [22]]: SSIS Error Code
> > DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection
> > method call to the connection manager "DestinationConnectionOLEDB" failed
> > with error code 0xC0202009. There may be error messages posted before
> > this
> > with more information on why the AcquireConnection method call failed.
> >
> > (Microsoft Visual Studio)
> > --
> > Regards,
> > Jamie
> >
> >
> > "Dan Guzman" wrote:
> >
> >> > I can create the file as a unicode text file - the collation is tough
> >> > to
> >> > begin with - many characters between ascii 128 and 256. I'd really
> >> > like
> >> > to
> >> > be able to pop this out in the legacy Access 2.0 format.
> >>
> >> Have you tried the -w option instead of -C RAW?
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >> http://weblogs.sqlteam.com/dang/
> >>
> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> news:FB9A702A-963F-4AB0-8C39-A1FFD1FC6E88@.microsoft.com...
> >> >I meant to include the error but hit return and sent the previous out:
> >> >
> >> > Error = [Microsoft][SQL Native Client]Unable to resolve column level
> >> > collations
> >> >
> >> > Full line looks as follows:
> >> > bcp "use MyTable.MyCities; select * from ##TempExportData2" queryout
> >> > "C:\Halloran\ITALY_IT.mdb" -c -CRAW -T
> >> >
> >> > I can create the file as a unicode text file - the collation is tough
> >> > to
> >> > begin with - many characters between ascii 128 and 256. I'd really
> >> > like
> >> > to
> >> > be able to pop this out in the legacy Access 2.0 format.
> >> >
> >> > --
> >> > Regards,
> >> > Jamie
> >> >
> >> >
> >> > "thejamie" wrote:
> >> >
> >> >> I am exporting data to roughly 250 Access 2.0 databases from tables in
> >> >> SQL
> >> >> Server 2005. For each table, I open one copy of the wizard and select
> >> >> a
> >> >> specifically named Access 2.0 database, export the table (it has to be
> >> >> edited
> >> >> to the correct format to keep the size down - in other words, varchar
> >> >> with a
> >> >> field size vs ntext). I do this 250 times. I suspect that bulk copy
> >> >> will do
> >> >> this:
> >> >>
> >> >> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> >> >>
> >> >> But I am having trouble locating the documentation for exporting to an
> >> >> older
> >> >> Access (DAO Access 2.0) database.
> >> >>
> >> >> Help?
> >> >>
> >> >> --
> >> >> Regards,
> >> >> Jamie
> >>
>|||> That first connection from the database is also the same name as the
> database
> itself... for example if the table in the SQL Database is call Mongolia,
> then
> the Access Database target is called Mongolia.MDB. The target databases
> can
> be quickly enumerated as they are simply files in a folder.
I see - you have different source table names. That messes up the single
data flow approach because I don't think you can change the source table
name at runtime. You can change the source and target connections, though.
> Also, it looks like (worst case) I can actually copy the dataflow object
> 252
> times and change the source and destination name physically - it would
> take
> less time than what I do currently.
Be aware that if the table schema is different, you will also need to update
the meta-data at design time.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:709CC288-41CD-485F-9EF7-399D8C2AE95D@.microsoft.com...
> That first connection from the database is also the same name as the
> database
> itself... for example if the table in the SQL Database is call Mongolia,
> then
> the Access Database target is called Mongolia.MDB. The target databases
> can
> be quickly enumerated as they are simply files in a folder.
> What would be really nice would be to use the same variable in the source
> connection as I use in the target connection.
> I can think of a couple of ways to do this... first is to create a text
> file
> with all the table names in them and somehow script that as the source
> variable, but I can't help but think I should also be able to enumerate
> the
> table names that are in the source folder with the variable name from the
> destination folder.
> It would work like:
> Source table Mongolia (source connection)=> data conversion => Destination
> database Mongolia.mdb (destination file variable name)
> Since the destination name and the source name are the same (minus the
> ".mdb" extension) could I somehow use scripting to remove the ".mdb" from
> the
> destination variable name?
> ie Replace(@.[User::@.mdbVariablename],'.mdb','') From your example below
> it
> almost appears that I could also write an XML script to provide the named
> variables of the tables.
> Also, it looks like (worst case) I can actually copy the dataflow object
> 252
> times and change the source and destination name physically - it would
> take
> less time than what I do currently.
> --
> Regards,
> Jamie
>
> "Dan Guzman" wrote:
>> >I like the SSIS direction better however I am lost now as there are two
>> > connection strings and both should change with each enumeration.
>> Two connection strings? I was under the impression that you had only one
>> SQL Server source database and was planning to push that SQL data to many
>> different Access databases. Since you need to change both connection
>> strings for each iteration, you'll need to use a different enumerator and
>> 2
>> variables - one for the Access file and another for the SQL instance.
>> In the SQL Server connection, use an expression to set the
>> ConnectionString
>> property using the server name variable. For example:
>> "Data Source=" + @.SqlServerName + ";Initial
>> Catalog=MyDatabase;Provider=SQLNCLI;Integrated Security=SSPI;Auto
>> Translate=false;"
>> In the Access database connection, use an expression to set the
>> ConnectionString property using the file path variable. For example:
>> "Data Source=" + @.FileName + ";OLE DB
>> SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;"
>> Set the Foreach Loop container Collection Enumerator to one that can
>> provide
>> both the SQL Server name and Access file path. The example below uses a
>> Foreach NodeList Enumerator to extract the values from an embedded XML
>> document. Set the document source type to DirectInput and enter an XML
>> string like the one below as the DocumentSource:
>> <CopyList>
>> <Task>
>> <SqlServerName>SQLSERVER1</SqlServerName>
>> <FileName>C:\db1.mdb</FileName>
>> </Task>
>> <Task>
>> <SqlServerName>SQLSERVER2</SqlServerName>
>> <FileName>C:\db2.mdb</FileName>
>> </Task>
>> </CopyList>
>> Set the remaining collection properties as follows:
>> EnumerationType: ElementCollection
>> OuterXPathStringSourceType: DirectInput
>> OuterXPathString: /CopyList/Task
>> InnerElementType: NoteText
>> InnerXPathStringSourceType: DirectInput
>> InnerXPathString: *
>> Under VariableMappings, map the server name variable to index 0 and the
>> file
>> name variable to index 1.
>> Or course, you can use other sources for the server/file list. Just
>> change
>> the enumerator accordingly.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:E7FA5A2C-CE9B-4A53-9E43-E5AE00CCDFBF@.microsoft.com...
>> >I like the SSIS direction better however I am lost now as there are two
>> > connection strings and both should change with each enumeration.
>> > Before
>> > the
>> > first enumeration runs though, I get the following error and I am not
>> > sure
>> > where to start on this to get it fixed: (note, I grabbed the
>> > foreachlooper
>> > and inserted the template saved from a working export in the wizard
>> > package -
>> > I created an expression for the output connection by enumerating the
>> > list
>> > of
>> > mdb files in the folder. I don't know how to tell it that it is an
>> > Access
>> > 2.0 so I have assumed that since the package that was saved worked okay
>> > that
>> > the Access 2.0 connection would be assumed)
>> >
>> > Error at AfghanTemplate [Connection manager
>> > "DestinationConnectionOLEDB"]:
>> > SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error
>> > code:
>> > 0x80004005.
>> > An OLE DB record is available. Source: "Microsoft OLE DB Provider for
>> > ODBC
>> > Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver
>> > Manager]
>> > Data source name not found and no default driver specified".
>> >
>> > Error at Data Flow Task [Destination - CITYDB [22]]: SSIS Error Code
>> > DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The
>> > AcquireConnection
>> > method call to the connection manager "DestinationConnectionOLEDB"
>> > failed
>> > with error code 0xC0202009. There may be error messages posted before
>> > this
>> > with more information on why the AcquireConnection method call failed.
>> >
>> > (Microsoft Visual Studio)
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "Dan Guzman" wrote:
>> >
>> >> > I can create the file as a unicode text file - the collation is
>> >> > tough
>> >> > to
>> >> > begin with - many characters between ascii 128 and 256. I'd really
>> >> > like
>> >> > to
>> >> > be able to pop this out in the legacy Access 2.0 format.
>> >>
>> >> Have you tried the -w option instead of -C RAW?
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >> http://weblogs.sqlteam.com/dang/
>> >>
>> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> news:FB9A702A-963F-4AB0-8C39-A1FFD1FC6E88@.microsoft.com...
>> >> >I meant to include the error but hit return and sent the previous
>> >> >out:
>> >> >
>> >> > Error = [Microsoft][SQL Native Client]Unable to resolve column level
>> >> > collations
>> >> >
>> >> > Full line looks as follows:
>> >> > bcp "use MyTable.MyCities; select * from ##TempExportData2" queryout
>> >> > "C:\Halloran\ITALY_IT.mdb" -c -CRAW -T
>> >> >
>> >> > I can create the file as a unicode text file - the collation is
>> >> > tough
>> >> > to
>> >> > begin with - many characters between ascii 128 and 256. I'd really
>> >> > like
>> >> > to
>> >> > be able to pop this out in the legacy Access 2.0 format.
>> >> >
>> >> > --
>> >> > Regards,
>> >> > Jamie
>> >> >
>> >> >
>> >> > "thejamie" wrote:
>> >> >
>> >> >> I am exporting data to roughly 250 Access 2.0 databases from tables
>> >> >> in
>> >> >> SQL
>> >> >> Server 2005. For each table, I open one copy of the wizard and
>> >> >> select
>> >> >> a
>> >> >> specifically named Access 2.0 database, export the table (it has to
>> >> >> be
>> >> >> edited
>> >> >> to the correct format to keep the size down - in other words,
>> >> >> varchar
>> >> >> with a
>> >> >> field size vs ntext). I do this 250 times. I suspect that bulk
>> >> >> copy
>> >> >> will do
>> >> >> this:
>> >> >>
>> >> >> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
>> >> >>
>> >> >> But I am having trouble locating the documentation for exporting to
>> >> >> an
>> >> >> older
>> >> >> Access (DAO Access 2.0) database.
>> >> >>
>> >> >> Help?
>> >> >>
>> >> >> --
>> >> >> Regards,
>> >> >> Jamie
>> >>|||Many thanks for your perseverance Dan,
Currently because my arm is about to fall off from moving data, it seemed
feasible to write code in NET 2005 to do half of this for me. (see below).
This portion creates the tables in the target Access databases thus
automating half of the steps I hope to eliminate using SSIS (I haven't gotten
the hang of SSIS just yet for the enumeration and I am already almost a week
past my deadline on this project so...). While I'm still running the wizard,
it is now without nearly as many clicks.
Thought it might be better said in code.
Imports System.Data.SqlClient
Imports System.IO
Imports System
Imports ADOX
Imports System.Data
Imports System.Data.OleDb
Imports System.Reflection
Private msAccessCn As String = "Data Source=dbname;OLE DB
SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;"
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim path As String = "C:\Access 2.0\"
Dim searchPattern As String = ".mdb"
Dim fso As New Scripting.FileSystemObject
Dim f As Scripting.File
Dim s As String
Dim sTableName As String
For Each f In fso.GetFolder(path).Files
If InStr(LCase(f.Name), searchPattern) Then
s = f.Name
sTableName = Replace(f.Name.ToUpper, ".MDB", "")
s = Replace(msAccessCn.ToUpper, "dbname", s.ToUpper)
CreateTable(f.Name, s)
End If
Next
End Sub
Private Sub CreateTable(ByVal sfilename As String, ByVal scn As String)
'Table creation could also be automated except that the tables are Access
2.0 and
'this is likely to create the wrong version
'Dim cat As New ADOX.CatalogClass()
'cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" &
sfilename & ";" + "Jet OLEDB:Engine Type=5")
' Open Connection
Dim myConnection As New OleDbConnection(scn)
myConnection.Open()
'Create table base on your dataset tables.Below is an example
Dim strSQL As String
strSQL = "CREATE TABLE CITYDB " + "(abc varchar(110), Cityname
varchar(90))"
' Command for Creating Table
Dim myCommand As New OleDbCommand(strSQL, myConnection)
myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub
Regards,
Jamie
"Dan Guzman" wrote:
> > That first connection from the database is also the same name as the
> > database
> > itself... for example if the table in the SQL Database is call Mongolia,
> > then
> > the Access Database target is called Mongolia.MDB. The target databases
> > can
> > be quickly enumerated as they are simply files in a folder.
> I see - you have different source table names. That messes up the single
> data flow approach because I don't think you can change the source table
> name at runtime. You can change the source and target connections, though.
> > Also, it looks like (worst case) I can actually copy the dataflow object
> > 252
> > times and change the source and destination name physically - it would
> > take
> > less time than what I do currently.
> Be aware that if the table schema is different, you will also need to update
> the meta-data at design time.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:709CC288-41CD-485F-9EF7-399D8C2AE95D@.microsoft.com...
> > That first connection from the database is also the same name as the
> > database
> > itself... for example if the table in the SQL Database is call Mongolia,
> > then
> > the Access Database target is called Mongolia.MDB. The target databases
> > can
> > be quickly enumerated as they are simply files in a folder.
> >
> > What would be really nice would be to use the same variable in the source
> > connection as I use in the target connection.
> >
> > I can think of a couple of ways to do this... first is to create a text
> > file
> > with all the table names in them and somehow script that as the source
> > variable, but I can't help but think I should also be able to enumerate
> > the
> > table names that are in the source folder with the variable name from the
> > destination folder.
> >
> > It would work like:
> > Source table Mongolia (source connection)=> data conversion => Destination
> > database Mongolia.mdb (destination file variable name)
> >
> > Since the destination name and the source name are the same (minus the
> > ".mdb" extension) could I somehow use scripting to remove the ".mdb" from
> > the
> > destination variable name?
> >
> > ie Replace(@.[User::@.mdbVariablename],'.mdb','') From your example below
> > it
> > almost appears that I could also write an XML script to provide the named
> > variables of the tables.
> >
> > Also, it looks like (worst case) I can actually copy the dataflow object
> > 252
> > times and change the source and destination name physically - it would
> > take
> > less time than what I do currently.
> > --
> > Regards,
> > Jamie
> >
> >
> > "Dan Guzman" wrote:
> >
> >> >I like the SSIS direction better however I am lost now as there are two
> >> > connection strings and both should change with each enumeration.
> >>
> >> Two connection strings? I was under the impression that you had only one
> >> SQL Server source database and was planning to push that SQL data to many
> >> different Access databases. Since you need to change both connection
> >> strings for each iteration, you'll need to use a different enumerator and
> >> 2
> >> variables - one for the Access file and another for the SQL instance.
> >>
> >> In the SQL Server connection, use an expression to set the
> >> ConnectionString
> >> property using the server name variable. For example:
> >>
> >> "Data Source=" + @.SqlServerName + ";Initial
> >> Catalog=MyDatabase;Provider=SQLNCLI;Integrated Security=SSPI;Auto
> >> Translate=false;"
> >>
> >> In the Access database connection, use an expression to set the
> >> ConnectionString property using the file path variable. For example:
> >>
> >> "Data Source=" + @.FileName + ";OLE DB
> >> SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;"
> >>
> >> Set the Foreach Loop container Collection Enumerator to one that can
> >> provide
> >> both the SQL Server name and Access file path. The example below uses a
> >> Foreach NodeList Enumerator to extract the values from an embedded XML
> >> document. Set the document source type to DirectInput and enter an XML
> >> string like the one below as the DocumentSource:
> >>
> >> <CopyList>
> >> <Task>
> >> <SqlServerName>SQLSERVER1</SqlServerName>
> >> <FileName>C:\db1.mdb</FileName>
> >> </Task>
> >> <Task>
> >> <SqlServerName>SQLSERVER2</SqlServerName>
> >> <FileName>C:\db2.mdb</FileName>
> >> </Task>
> >> </CopyList>
> >>
> >> Set the remaining collection properties as follows:
> >>
> >> EnumerationType: ElementCollection
> >> OuterXPathStringSourceType: DirectInput
> >> OuterXPathString: /CopyList/Task
> >> InnerElementType: NoteText
> >> InnerXPathStringSourceType: DirectInput
> >> InnerXPathString: *
> >>
> >> Under VariableMappings, map the server name variable to index 0 and the
> >> file
> >> name variable to index 1.
> >>
> >> Or course, you can use other sources for the server/file list. Just
> >> change
> >> the enumerator accordingly.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >> http://weblogs.sqlteam.com/dang/
> >>
> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> news:E7FA5A2C-CE9B-4A53-9E43-E5AE00CCDFBF@.microsoft.com...
> >> >I like the SSIS direction better however I am lost now as there are two
> >> > connection strings and both should change with each enumeration.
> >> > Before
> >> > the
> >> > first enumeration runs though, I get the following error and I am not
> >> > sure
> >> > where to start on this to get it fixed: (note, I grabbed the
> >> > foreachlooper
> >> > and inserted the template saved from a working export in the wizard
> >> > package -
> >> > I created an expression for the output connection by enumerating the
> >> > list
> >> > of
> >> > mdb files in the folder. I don't know how to tell it that it is an
> >> > Access
> >> > 2.0 so I have assumed that since the package that was saved worked okay
> >> > that
> >> > the Access 2.0 connection would be assumed)
> >> >
> >> > Error at AfghanTemplate [Connection manager
> >> > "DestinationConnectionOLEDB"]:
> >> > SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error
> >> > code:
> >> > 0x80004005.
> >> > An OLE DB record is available. Source: "Microsoft OLE DB Provider for
> >> > ODBC
> >> > Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver
> >> > Manager]
> >> > Data source name not found and no default driver specified".
> >> >
> >> > Error at Data Flow Task [Destination - CITYDB [22]]: SSIS Error Code
> >> > DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The
> >> > AcquireConnection
> >> > method call to the connection manager "DestinationConnectionOLEDB"
> >> > failed
> >> > with error code 0xC0202009. There may be error messages posted before
> >> > this
> >> > with more information on why the AcquireConnection method call failed.
> >> >
> >> > (Microsoft Visual Studio)
> >> > --
> >> > Regards,
> >> > Jamie
> >> >
> >> >
> >> > "Dan Guzman" wrote:
> >> >
> >> >> > I can create the file as a unicode text file - the collation is
> >> >> > tough
> >> >> > to
> >> >> > begin with - many characters between ascii 128 and 256. I'd really
> >> >> > like
> >> >> > to
> >> >> > be able to pop this out in the legacy Access 2.0 format.
> >> >>
> >> >> Have you tried the -w option instead of -C RAW?
> >> >>
> >> >> --
> >> >> Hope this helps.
> >> >>
> >> >> Dan Guzman
> >> >> SQL Server MVP
> >> >> http://weblogs.sqlteam.com/dang/
> >> >>
> >> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> >> news:FB9A702A-963F-4AB0-8C39-A1FFD1FC6E88@.microsoft.com...
> >> >> >I meant to include the error but hit return and sent the previous
> >> >> >out:
> >> >> >
> >> >> > Error = [Microsoft][SQL Native Client]Unable to resolve column level
> >> >> > collations
> >> >> >
> >> >> > Full line looks as follows:
> >> >> > bcp "use MyTable.MyCities; select * from ##TempExportData2" queryout
> >> >> > "C:\Halloran\ITALY_IT.mdb" -c -CRAW -T
> >> >> >
> >> >> > I can create the file as a unicode text file - the collation is
> >> >> > tough
> >> >> > to
> >> >> > begin with - many characters between ascii 128 and 256. I'd really
> >> >> > like
> >> >> > to
> >> >> > be able to pop this out in the legacy Access 2.0 format.
> >> >> >
> >> >> > --
> >> >> > Regards,
> >> >> > Jamie
> >> >> >
> >> >> >
> >> >> > "thejamie" wrote:
> >> >> >
> >> >> >> I am exporting data to roughly 250 Access 2.0 databases from tables
> >> >> >> in
> >> >> >> SQL
> >> >> >> Server 2005. For each table, I open one copy of the wizard and
> >> >> >> select
> >> >> >> a
> >> >> >> specifically named Access 2.0 database, export the table (it has to
> >> >> >> be
> >> >> >> edited
> >> >> >> to the correct format to keep the size down - in other words,
> >> >> >> varchar
> >> >> >> with a
> >> >> >> field size vs ntext). I do this 250 times. I suspect that bulk
> >> >> >> copy
> >> >> >> will do
> >> >> >> this:
> >> >> >>
> >> >> >> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> >> >> >>
> >> >> >> But I am having trouble locating the documentation for exporting to
> >> >> >> an
> >> >> >> older
> >> >> >> Access (DAO Access 2.0) database.
> >> >> >>
> >> >> >> Help?
> >> >> >>
> >> >> >> --
> >> >> >> Regards,
> >> >> >> Jamie
> >> >>
> >>
>
Server 2005. For each table, I open one copy of the wizard and select a
specifically named Access 2.0 database, export the table (it has to be edited
to the correct format to keep the size down - in other words, varchar with a
field size vs ntext). I do this 250 times. I suspect that bulk copy will do
this:
bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
But I am having trouble locating the documentation for exporting to an older
Access (DAO Access 2.0) database.
Help?
--
Regards,
Jamie> I suspect that bulk copy will do
> this:
> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> But I am having trouble locating the documentation for exporting to an
> older
> Access (DAO Access 2.0) database.
BCP creates text files. You could use it to create a delimited
(tab-delimited) by default) text file for each table and then import into
Access using the Access tools. I don't remember much about Access 2.0 but I
think you can create a macro to facilitate doing this repeatedly. The BCP
command line (assuming local default SQL instance):
bcp mydatabase.dbo.mycountrytable out
C:\ExportedFiels\mycountrytable.tab -T -c
Another option is to create a SSIS package to automate the tasks. SQL
Server Integration Services is the underlying technology that the SQL Server
2005 export wizard uses. Even if you don't currently know SSIS, you might
find learning more about SSIS useful if you find yourself often coping data
to/from Access.
You can create a template SSIS package by going through the export wizard
and saving the package to the file system. Open SQL Server BI Studio,
create a new Integration Services project and add the previously created
package file to the new project. You can then edit the package and tweak
according to your needs.
One SSIS approach is to move the package control flow tasks (preparation
script and data flow) into a new For Each Loop Container and then use a "for
each file enumerator" that points to a directory containing your Access
database files. You can find package examples at http://www.sqlis.com/.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:5A6971AE-454E-42B1-A728-C00AB649F623@.microsoft.com...
>I am exporting data to roughly 250 Access 2.0 databases from tables in SQL
> Server 2005. For each table, I open one copy of the wizard and select a
> specifically named Access 2.0 database, export the table (it has to be
> edited
> to the correct format to keep the size down - in other words, varchar with
> a
> field size vs ntext). I do this 250 times. I suspect that bulk copy will
> do
> this:
> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> But I am having trouble locating the documentation for exporting to an
> older
> Access (DAO Access 2.0) database.
> Help?
> --
> Regards,
> Jamie|||I meant to include the error but hit return and sent the previous out:
Error = [Microsoft][SQL Native Client]Unable to resolve column level
collations
Full line looks as follows:
bcp "use MyTable.MyCities; select * from ##TempExportData2" queryout
"C:\Halloran\ITALY_IT.mdb" -c -CRAW -T
I can create the file as a unicode text file - the collation is tough to
begin with - many characters between ascii 128 and 256. I'd really like to
be able to pop this out in the legacy Access 2.0 format.
--
Regards,
Jamie
"thejamie" wrote:
> I am exporting data to roughly 250 Access 2.0 databases from tables in SQL
> Server 2005. For each table, I open one copy of the wizard and select a
> specifically named Access 2.0 database, export the table (it has to be edited
> to the correct format to keep the size down - in other words, varchar with a
> field size vs ntext). I do this 250 times. I suspect that bulk copy will do
> this:
> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> But I am having trouble locating the documentation for exporting to an older
> Access (DAO Access 2.0) database.
> Help?
> --
> Regards,
> Jamie|||Super... I thought this could be done... I had saved the package already but
the enumerator helps.
--
Regards,
Jamie
"Dan Guzman" wrote:
> > I suspect that bulk copy will do
> > this:
> >
> > bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> >
> > But I am having trouble locating the documentation for exporting to an
> > older
> > Access (DAO Access 2.0) database.
> BCP creates text files. You could use it to create a delimited
> (tab-delimited) by default) text file for each table and then import into
> Access using the Access tools. I don't remember much about Access 2.0 but I
> think you can create a macro to facilitate doing this repeatedly. The BCP
> command line (assuming local default SQL instance):
> bcp mydatabase.dbo.mycountrytable out
> C:\ExportedFiels\mycountrytable.tab -T -c
> Another option is to create a SSIS package to automate the tasks. SQL
> Server Integration Services is the underlying technology that the SQL Server
> 2005 export wizard uses. Even if you don't currently know SSIS, you might
> find learning more about SSIS useful if you find yourself often coping data
> to/from Access.
> You can create a template SSIS package by going through the export wizard
> and saving the package to the file system. Open SQL Server BI Studio,
> create a new Integration Services project and add the previously created
> package file to the new project. You can then edit the package and tweak
> according to your needs.
> One SSIS approach is to move the package control flow tasks (preparation
> script and data flow) into a new For Each Loop Container and then use a "for
> each file enumerator" that points to a directory containing your Access
> database files. You can find package examples at http://www.sqlis.com/.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:5A6971AE-454E-42B1-A728-C00AB649F623@.microsoft.com...
> >I am exporting data to roughly 250 Access 2.0 databases from tables in SQL
> > Server 2005. For each table, I open one copy of the wizard and select a
> > specifically named Access 2.0 database, export the table (it has to be
> > edited
> > to the correct format to keep the size down - in other words, varchar with
> > a
> > field size vs ntext). I do this 250 times. I suspect that bulk copy will
> > do
> > this:
> >
> > bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> >
> > But I am having trouble locating the documentation for exporting to an
> > older
> > Access (DAO Access 2.0) database.
> >
> > Help?
> >
> > --
> > Regards,
> > Jamie
>|||> I can create the file as a unicode text file - the collation is tough to
> begin with - many characters between ascii 128 and 256. I'd really like
> to
> be able to pop this out in the legacy Access 2.0 format.
Have you tried the -w option instead of -C RAW?
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:FB9A702A-963F-4AB0-8C39-A1FFD1FC6E88@.microsoft.com...
>I meant to include the error but hit return and sent the previous out:
> Error = [Microsoft][SQL Native Client]Unable to resolve column level
> collations
> Full line looks as follows:
> bcp "use MyTable.MyCities; select * from ##TempExportData2" queryout
> "C:\Halloran\ITALY_IT.mdb" -c -CRAW -T
> I can create the file as a unicode text file - the collation is tough to
> begin with - many characters between ascii 128 and 256. I'd really like
> to
> be able to pop this out in the legacy Access 2.0 format.
> --
> Regards,
> Jamie
>
> "thejamie" wrote:
>> I am exporting data to roughly 250 Access 2.0 databases from tables in
>> SQL
>> Server 2005. For each table, I open one copy of the wizard and select a
>> specifically named Access 2.0 database, export the table (it has to be
>> edited
>> to the correct format to keep the size down - in other words, varchar
>> with a
>> field size vs ntext). I do this 250 times. I suspect that bulk copy
>> will do
>> this:
>> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
>> But I am having trouble locating the documentation for exporting to an
>> older
>> Access (DAO Access 2.0) database.
>> Help?
>> --
>> Regards,
>> Jamie|||I like the SSIS direction better however I am lost now as there are two
connection strings and both should change with each enumeration. Before the
first enumeration runs though, I get the following error and I am not sure
where to start on this to get it fixed: (note, I grabbed the foreachlooper
and inserted the template saved from a working export in the wizard package -
I created an expression for the output connection by enumerating the list of
mdb files in the folder. I don't know how to tell it that it is an Access
2.0 so I have assumed that since the package that was saved worked okay that
the Access 2.0 connection would be assumed)
Error at AfghanTemplate [Connection manager "DestinationConnectionOLEDB"]:
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code:
0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC
Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager]
Data source name not found and no default driver specified".
Error at Data Flow Task [Destination - CITYDB [22]]: SSIS Error Code
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection
method call to the connection manager "DestinationConnectionOLEDB" failed
with error code 0xC0202009. There may be error messages posted before this
with more information on why the AcquireConnection method call failed.
(Microsoft Visual Studio)
--
Regards,
Jamie
"Dan Guzman" wrote:
> > I can create the file as a unicode text file - the collation is tough to
> > begin with - many characters between ascii 128 and 256. I'd really like
> > to
> > be able to pop this out in the legacy Access 2.0 format.
> Have you tried the -w option instead of -C RAW?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:FB9A702A-963F-4AB0-8C39-A1FFD1FC6E88@.microsoft.com...
> >I meant to include the error but hit return and sent the previous out:
> >
> > Error = [Microsoft][SQL Native Client]Unable to resolve column level
> > collations
> >
> > Full line looks as follows:
> > bcp "use MyTable.MyCities; select * from ##TempExportData2" queryout
> > "C:\Halloran\ITALY_IT.mdb" -c -CRAW -T
> >
> > I can create the file as a unicode text file - the collation is tough to
> > begin with - many characters between ascii 128 and 256. I'd really like
> > to
> > be able to pop this out in the legacy Access 2.0 format.
> >
> > --
> > Regards,
> > Jamie
> >
> >
> > "thejamie" wrote:
> >
> >> I am exporting data to roughly 250 Access 2.0 databases from tables in
> >> SQL
> >> Server 2005. For each table, I open one copy of the wizard and select a
> >> specifically named Access 2.0 database, export the table (it has to be
> >> edited
> >> to the correct format to keep the size down - in other words, varchar
> >> with a
> >> field size vs ntext). I do this 250 times. I suspect that bulk copy
> >> will do
> >> this:
> >>
> >> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> >>
> >> But I am having trouble locating the documentation for exporting to an
> >> older
> >> Access (DAO Access 2.0) database.
> >>
> >> Help?
> >>
> >> --
> >> Regards,
> >> Jamie
>|||I neglected to mention I am running an x64 copy of XP. It may have something
to do with it. I just discovered that mdac appears to be incorrectly
installed on this copy.
Error when trying to import a flat file:
An error occurred which the SQL Server Integration Services Wizard was not
prepared to handle.
--
Regards,
Jamie
"Dan Guzman" wrote:
> > I suspect that bulk copy will do
> > this:
> >
> > bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> >
> > But I am having trouble locating the documentation for exporting to an
> > older
> > Access (DAO Access 2.0) database.
> BCP creates text files. You could use it to create a delimited
> (tab-delimited) by default) text file for each table and then import into
> Access using the Access tools. I don't remember much about Access 2.0 but I
> think you can create a macro to facilitate doing this repeatedly. The BCP
> command line (assuming local default SQL instance):
> bcp mydatabase.dbo.mycountrytable out
> C:\ExportedFiels\mycountrytable.tab -T -c
> Another option is to create a SSIS package to automate the tasks. SQL
> Server Integration Services is the underlying technology that the SQL Server
> 2005 export wizard uses. Even if you don't currently know SSIS, you might
> find learning more about SSIS useful if you find yourself often coping data
> to/from Access.
> You can create a template SSIS package by going through the export wizard
> and saving the package to the file system. Open SQL Server BI Studio,
> create a new Integration Services project and add the previously created
> package file to the new project. You can then edit the package and tweak
> according to your needs.
> One SSIS approach is to move the package control flow tasks (preparation
> script and data flow) into a new For Each Loop Container and then use a "for
> each file enumerator" that points to a directory containing your Access
> database files. You can find package examples at http://www.sqlis.com/.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:5A6971AE-454E-42B1-A728-C00AB649F623@.microsoft.com...
> >I am exporting data to roughly 250 Access 2.0 databases from tables in SQL
> > Server 2005. For each table, I open one copy of the wizard and select a
> > specifically named Access 2.0 database, export the table (it has to be
> > edited
> > to the correct format to keep the size down - in other words, varchar with
> > a
> > field size vs ntext). I do this 250 times. I suspect that bulk copy will
> > do
> > this:
> >
> > bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> >
> > But I am having trouble locating the documentation for exporting to an
> > older
> > Access (DAO Access 2.0) database.
> >
> > Help?
> >
> > --
> > Regards,
> > Jamie
>|||>I like the SSIS direction better however I am lost now as there are two
> connection strings and both should change with each enumeration.
Two connection strings? I was under the impression that you had only one
SQL Server source database and was planning to push that SQL data to many
different Access databases. Since you need to change both connection
strings for each iteration, you'll need to use a different enumerator and 2
variables - one for the Access file and another for the SQL instance.
In the SQL Server connection, use an expression to set the ConnectionString
property using the server name variable. For example:
"Data Source=" + @.SqlServerName + ";Initial
Catalog=MyDatabase;Provider=SQLNCLI;Integrated Security=SSPI;Auto
Translate=false;"
In the Access database connection, use an expression to set the
ConnectionString property using the file path variable. For example:
"Data Source=" + @.FileName + ";OLE DB
SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;"
Set the Foreach Loop container Collection Enumerator to one that can provide
both the SQL Server name and Access file path. The example below uses a
Foreach NodeList Enumerator to extract the values from an embedded XML
document. Set the document source type to DirectInput and enter an XML
string like the one below as the DocumentSource:
<CopyList>
<Task>
<SqlServerName>SQLSERVER1</SqlServerName>
<FileName>C:\db1.mdb</FileName>
</Task>
<Task>
<SqlServerName>SQLSERVER2</SqlServerName>
<FileName>C:\db2.mdb</FileName>
</Task>
</CopyList>
Set the remaining collection properties as follows:
EnumerationType: ElementCollection
OuterXPathStringSourceType: DirectInput
OuterXPathString: /CopyList/Task
InnerElementType: NoteText
InnerXPathStringSourceType: DirectInput
InnerXPathString: *
Under VariableMappings, map the server name variable to index 0 and the file
name variable to index 1.
Or course, you can use other sources for the server/file list. Just change
the enumerator accordingly.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:E7FA5A2C-CE9B-4A53-9E43-E5AE00CCDFBF@.microsoft.com...
>I like the SSIS direction better however I am lost now as there are two
> connection strings and both should change with each enumeration. Before
> the
> first enumeration runs though, I get the following error and I am not sure
> where to start on this to get it fixed: (note, I grabbed the foreachlooper
> and inserted the template saved from a working export in the wizard
> package -
> I created an expression for the output connection by enumerating the list
> of
> mdb files in the folder. I don't know how to tell it that it is an Access
> 2.0 so I have assumed that since the package that was saved worked okay
> that
> the Access 2.0 connection would be assumed)
> Error at AfghanTemplate [Connection manager "DestinationConnectionOLEDB"]:
> SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error
> code:
> 0x80004005.
> An OLE DB record is available. Source: "Microsoft OLE DB Provider for
> ODBC
> Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver
> Manager]
> Data source name not found and no default driver specified".
> Error at Data Flow Task [Destination - CITYDB [22]]: SSIS Error Code
> DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection
> method call to the connection manager "DestinationConnectionOLEDB" failed
> with error code 0xC0202009. There may be error messages posted before
> this
> with more information on why the AcquireConnection method call failed.
> (Microsoft Visual Studio)
> --
> Regards,
> Jamie
>
> "Dan Guzman" wrote:
>> > I can create the file as a unicode text file - the collation is tough
>> > to
>> > begin with - many characters between ascii 128 and 256. I'd really
>> > like
>> > to
>> > be able to pop this out in the legacy Access 2.0 format.
>> Have you tried the -w option instead of -C RAW?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:FB9A702A-963F-4AB0-8C39-A1FFD1FC6E88@.microsoft.com...
>> >I meant to include the error but hit return and sent the previous out:
>> >
>> > Error = [Microsoft][SQL Native Client]Unable to resolve column level
>> > collations
>> >
>> > Full line looks as follows:
>> > bcp "use MyTable.MyCities; select * from ##TempExportData2" queryout
>> > "C:\Halloran\ITALY_IT.mdb" -c -CRAW -T
>> >
>> > I can create the file as a unicode text file - the collation is tough
>> > to
>> > begin with - many characters between ascii 128 and 256. I'd really
>> > like
>> > to
>> > be able to pop this out in the legacy Access 2.0 format.
>> >
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "thejamie" wrote:
>> >
>> >> I am exporting data to roughly 250 Access 2.0 databases from tables in
>> >> SQL
>> >> Server 2005. For each table, I open one copy of the wizard and select
>> >> a
>> >> specifically named Access 2.0 database, export the table (it has to be
>> >> edited
>> >> to the correct format to keep the size down - in other words, varchar
>> >> with a
>> >> field size vs ntext). I do this 250 times. I suspect that bulk copy
>> >> will do
>> >> this:
>> >>
>> >> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
>> >>
>> >> But I am having trouble locating the documentation for exporting to an
>> >> older
>> >> Access (DAO Access 2.0) database.
>> >>
>> >> Help?
>> >>
>> >> --
>> >> Regards,
>> >> Jamie|||Dan,
I did not mean to confuse you. I have a new laptop that is sitting in the
system and I see that it has 64 bit issues. It needs to be reinstalled.
MDAC was not working properly. At this point, and because I do some work
from the laptop and some from the desktop, I was letting you know (in my own
strange way) that I will need to revisit this because I don't know if it is
the machine or something in the SSIS code that is causing problems. You gave
me the correct answer the first time. I appreciate that. Thank you.
--
Regards,
Jamie
"Dan Guzman" wrote:
> >I like the SSIS direction better however I am lost now as there are two
> > connection strings and both should change with each enumeration.
> Two connection strings? I was under the impression that you had only one
> SQL Server source database and was planning to push that SQL data to many
> different Access databases. Since you need to change both connection
> strings for each iteration, you'll need to use a different enumerator and 2
> variables - one for the Access file and another for the SQL instance.
> In the SQL Server connection, use an expression to set the ConnectionString
> property using the server name variable. For example:
> "Data Source=" + @.SqlServerName + ";Initial
> Catalog=MyDatabase;Provider=SQLNCLI;Integrated Security=SSPI;Auto
> Translate=false;"
> In the Access database connection, use an expression to set the
> ConnectionString property using the file path variable. For example:
> "Data Source=" + @.FileName + ";OLE DB
> SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;"
> Set the Foreach Loop container Collection Enumerator to one that can provide
> both the SQL Server name and Access file path. The example below uses a
> Foreach NodeList Enumerator to extract the values from an embedded XML
> document. Set the document source type to DirectInput and enter an XML
> string like the one below as the DocumentSource:
> <CopyList>
> <Task>
> <SqlServerName>SQLSERVER1</SqlServerName>
> <FileName>C:\db1.mdb</FileName>
> </Task>
> <Task>
> <SqlServerName>SQLSERVER2</SqlServerName>
> <FileName>C:\db2.mdb</FileName>
> </Task>
> </CopyList>
> Set the remaining collection properties as follows:
> EnumerationType: ElementCollection
> OuterXPathStringSourceType: DirectInput
> OuterXPathString: /CopyList/Task
> InnerElementType: NoteText
> InnerXPathStringSourceType: DirectInput
> InnerXPathString: *
> Under VariableMappings, map the server name variable to index 0 and the file
> name variable to index 1.
> Or course, you can use other sources for the server/file list. Just change
> the enumerator accordingly.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:E7FA5A2C-CE9B-4A53-9E43-E5AE00CCDFBF@.microsoft.com...
> >I like the SSIS direction better however I am lost now as there are two
> > connection strings and both should change with each enumeration. Before
> > the
> > first enumeration runs though, I get the following error and I am not sure
> > where to start on this to get it fixed: (note, I grabbed the foreachlooper
> > and inserted the template saved from a working export in the wizard
> > package -
> > I created an expression for the output connection by enumerating the list
> > of
> > mdb files in the folder. I don't know how to tell it that it is an Access
> > 2.0 so I have assumed that since the package that was saved worked okay
> > that
> > the Access 2.0 connection would be assumed)
> >
> > Error at AfghanTemplate [Connection manager "DestinationConnectionOLEDB"]:
> > SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error
> > code:
> > 0x80004005.
> > An OLE DB record is available. Source: "Microsoft OLE DB Provider for
> > ODBC
> > Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver
> > Manager]
> > Data source name not found and no default driver specified".
> >
> > Error at Data Flow Task [Destination - CITYDB [22]]: SSIS Error Code
> > DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection
> > method call to the connection manager "DestinationConnectionOLEDB" failed
> > with error code 0xC0202009. There may be error messages posted before
> > this
> > with more information on why the AcquireConnection method call failed.
> >
> > (Microsoft Visual Studio)
> > --
> > Regards,
> > Jamie
> >
> >
> > "Dan Guzman" wrote:
> >
> >> > I can create the file as a unicode text file - the collation is tough
> >> > to
> >> > begin with - many characters between ascii 128 and 256. I'd really
> >> > like
> >> > to
> >> > be able to pop this out in the legacy Access 2.0 format.
> >>
> >> Have you tried the -w option instead of -C RAW?
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >> http://weblogs.sqlteam.com/dang/
> >>
> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> news:FB9A702A-963F-4AB0-8C39-A1FFD1FC6E88@.microsoft.com...
> >> >I meant to include the error but hit return and sent the previous out:
> >> >
> >> > Error = [Microsoft][SQL Native Client]Unable to resolve column level
> >> > collations
> >> >
> >> > Full line looks as follows:
> >> > bcp "use MyTable.MyCities; select * from ##TempExportData2" queryout
> >> > "C:\Halloran\ITALY_IT.mdb" -c -CRAW -T
> >> >
> >> > I can create the file as a unicode text file - the collation is tough
> >> > to
> >> > begin with - many characters between ascii 128 and 256. I'd really
> >> > like
> >> > to
> >> > be able to pop this out in the legacy Access 2.0 format.
> >> >
> >> > --
> >> > Regards,
> >> > Jamie
> >> >
> >> >
> >> > "thejamie" wrote:
> >> >
> >> >> I am exporting data to roughly 250 Access 2.0 databases from tables in
> >> >> SQL
> >> >> Server 2005. For each table, I open one copy of the wizard and select
> >> >> a
> >> >> specifically named Access 2.0 database, export the table (it has to be
> >> >> edited
> >> >> to the correct format to keep the size down - in other words, varchar
> >> >> with a
> >> >> field size vs ntext). I do this 250 times. I suspect that bulk copy
> >> >> will do
> >> >> this:
> >> >>
> >> >> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> >> >>
> >> >> But I am having trouble locating the documentation for exporting to an
> >> >> older
> >> >> Access (DAO Access 2.0) database.
> >> >>
> >> >> Help?
> >> >>
> >> >> --
> >> >> Regards,
> >> >> Jamie
> >>
>|||Keep in mind that if you will need 64-bit drivers to run 64-bit SSIS.
However, you can set the Run64bitRuntime SSIS project property to False in
order to use 32-bit drivers. I had to do that on my 64-bit Vista
development box to avoid a "Class not registered" error for the
Microsoft.Jet.OLEDB.4.0 provider. The same package runs fine with
Run64bitRuntime set to False.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:3400EAB6-CBB6-4074-8EE9-5B4A189CEBAD@.microsoft.com...
> Dan,
> I did not mean to confuse you. I have a new laptop that is sitting in the
> system and I see that it has 64 bit issues. It needs to be reinstalled.
> MDAC was not working properly. At this point, and because I do some work
> from the laptop and some from the desktop, I was letting you know (in my
> own
> strange way) that I will need to revisit this because I don't know if it
> is
> the machine or something in the SSIS code that is causing problems. You
> gave
> me the correct answer the first time. I appreciate that. Thank you.
> --
> Regards,
> Jamie
>
> "Dan Guzman" wrote:
>> >I like the SSIS direction better however I am lost now as there are two
>> > connection strings and both should change with each enumeration.
>> Two connection strings? I was under the impression that you had only one
>> SQL Server source database and was planning to push that SQL data to many
>> different Access databases. Since you need to change both connection
>> strings for each iteration, you'll need to use a different enumerator and
>> 2
>> variables - one for the Access file and another for the SQL instance.
>> In the SQL Server connection, use an expression to set the
>> ConnectionString
>> property using the server name variable. For example:
>> "Data Source=" + @.SqlServerName + ";Initial
>> Catalog=MyDatabase;Provider=SQLNCLI;Integrated Security=SSPI;Auto
>> Translate=false;"
>> In the Access database connection, use an expression to set the
>> ConnectionString property using the file path variable. For example:
>> "Data Source=" + @.FileName + ";OLE DB
>> SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;"
>> Set the Foreach Loop container Collection Enumerator to one that can
>> provide
>> both the SQL Server name and Access file path. The example below uses a
>> Foreach NodeList Enumerator to extract the values from an embedded XML
>> document. Set the document source type to DirectInput and enter an XML
>> string like the one below as the DocumentSource:
>> <CopyList>
>> <Task>
>> <SqlServerName>SQLSERVER1</SqlServerName>
>> <FileName>C:\db1.mdb</FileName>
>> </Task>
>> <Task>
>> <SqlServerName>SQLSERVER2</SqlServerName>
>> <FileName>C:\db2.mdb</FileName>
>> </Task>
>> </CopyList>
>> Set the remaining collection properties as follows:
>> EnumerationType: ElementCollection
>> OuterXPathStringSourceType: DirectInput
>> OuterXPathString: /CopyList/Task
>> InnerElementType: NoteText
>> InnerXPathStringSourceType: DirectInput
>> InnerXPathString: *
>> Under VariableMappings, map the server name variable to index 0 and the
>> file
>> name variable to index 1.
>> Or course, you can use other sources for the server/file list. Just
>> change
>> the enumerator accordingly.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:E7FA5A2C-CE9B-4A53-9E43-E5AE00CCDFBF@.microsoft.com...
>> >I like the SSIS direction better however I am lost now as there are two
>> > connection strings and both should change with each enumeration.
>> > Before
>> > the
>> > first enumeration runs though, I get the following error and I am not
>> > sure
>> > where to start on this to get it fixed: (note, I grabbed the
>> > foreachlooper
>> > and inserted the template saved from a working export in the wizard
>> > package -
>> > I created an expression for the output connection by enumerating the
>> > list
>> > of
>> > mdb files in the folder. I don't know how to tell it that it is an
>> > Access
>> > 2.0 so I have assumed that since the package that was saved worked okay
>> > that
>> > the Access 2.0 connection would be assumed)
>> >
>> > Error at AfghanTemplate [Connection manager
>> > "DestinationConnectionOLEDB"]:
>> > SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error
>> > code:
>> > 0x80004005.
>> > An OLE DB record is available. Source: "Microsoft OLE DB Provider for
>> > ODBC
>> > Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver
>> > Manager]
>> > Data source name not found and no default driver specified".
>> >
>> > Error at Data Flow Task [Destination - CITYDB [22]]: SSIS Error Code
>> > DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The
>> > AcquireConnection
>> > method call to the connection manager "DestinationConnectionOLEDB"
>> > failed
>> > with error code 0xC0202009. There may be error messages posted before
>> > this
>> > with more information on why the AcquireConnection method call failed.
>> >
>> > (Microsoft Visual Studio)
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "Dan Guzman" wrote:
>> >
>> >> > I can create the file as a unicode text file - the collation is
>> >> > tough
>> >> > to
>> >> > begin with - many characters between ascii 128 and 256. I'd really
>> >> > like
>> >> > to
>> >> > be able to pop this out in the legacy Access 2.0 format.
>> >>
>> >> Have you tried the -w option instead of -C RAW?
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >> http://weblogs.sqlteam.com/dang/
>> >>
>> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> news:FB9A702A-963F-4AB0-8C39-A1FFD1FC6E88@.microsoft.com...
>> >> >I meant to include the error but hit return and sent the previous
>> >> >out:
>> >> >
>> >> > Error = [Microsoft][SQL Native Client]Unable to resolve column level
>> >> > collations
>> >> >
>> >> > Full line looks as follows:
>> >> > bcp "use MyTable.MyCities; select * from ##TempExportData2" queryout
>> >> > "C:\Halloran\ITALY_IT.mdb" -c -CRAW -T
>> >> >
>> >> > I can create the file as a unicode text file - the collation is
>> >> > tough
>> >> > to
>> >> > begin with - many characters between ascii 128 and 256. I'd really
>> >> > like
>> >> > to
>> >> > be able to pop this out in the legacy Access 2.0 format.
>> >> >
>> >> > --
>> >> > Regards,
>> >> > Jamie
>> >> >
>> >> >
>> >> > "thejamie" wrote:
>> >> >
>> >> >> I am exporting data to roughly 250 Access 2.0 databases from tables
>> >> >> in
>> >> >> SQL
>> >> >> Server 2005. For each table, I open one copy of the wizard and
>> >> >> select
>> >> >> a
>> >> >> specifically named Access 2.0 database, export the table (it has to
>> >> >> be
>> >> >> edited
>> >> >> to the correct format to keep the size down - in other words,
>> >> >> varchar
>> >> >> with a
>> >> >> field size vs ntext). I do this 250 times. I suspect that bulk
>> >> >> copy
>> >> >> will do
>> >> >> this:
>> >> >>
>> >> >> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
>> >> >>
>> >> >> But I am having trouble locating the documentation for exporting to
>> >> >> an
>> >> >> older
>> >> >> Access (DAO Access 2.0) database.
>> >> >>
>> >> >> Help?
>> >> >>
>> >> >> --
>> >> >> Regards,
>> >> >> Jamie
>> >>|||Yep, that helps, bigtime.
--
Regards,
Jamie
"Dan Guzman" wrote:
> Keep in mind that if you will need 64-bit drivers to run 64-bit SSIS.
> However, you can set the Run64bitRuntime SSIS project property to False in
> order to use 32-bit drivers. I had to do that on my 64-bit Vista
> development box to avoid a "Class not registered" error for the
> Microsoft.Jet.OLEDB.4.0 provider. The same package runs fine with
> Run64bitRuntime set to False.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:3400EAB6-CBB6-4074-8EE9-5B4A189CEBAD@.microsoft.com...
> > Dan,
> > I did not mean to confuse you. I have a new laptop that is sitting in the
> > system and I see that it has 64 bit issues. It needs to be reinstalled.
> > MDAC was not working properly. At this point, and because I do some work
> > from the laptop and some from the desktop, I was letting you know (in my
> > own
> > strange way) that I will need to revisit this because I don't know if it
> > is
> > the machine or something in the SSIS code that is causing problems. You
> > gave
> > me the correct answer the first time. I appreciate that. Thank you.
> > --
> > Regards,
> > Jamie
> >
> >
> > "Dan Guzman" wrote:
> >
> >> >I like the SSIS direction better however I am lost now as there are two
> >> > connection strings and both should change with each enumeration.
> >>
> >> Two connection strings? I was under the impression that you had only one
> >> SQL Server source database and was planning to push that SQL data to many
> >> different Access databases. Since you need to change both connection
> >> strings for each iteration, you'll need to use a different enumerator and
> >> 2
> >> variables - one for the Access file and another for the SQL instance.
> >>
> >> In the SQL Server connection, use an expression to set the
> >> ConnectionString
> >> property using the server name variable. For example:
> >>
> >> "Data Source=" + @.SqlServerName + ";Initial
> >> Catalog=MyDatabase;Provider=SQLNCLI;Integrated Security=SSPI;Auto
> >> Translate=false;"
> >>
> >> In the Access database connection, use an expression to set the
> >> ConnectionString property using the file path variable. For example:
> >>
> >> "Data Source=" + @.FileName + ";OLE DB
> >> SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;"
> >>
> >> Set the Foreach Loop container Collection Enumerator to one that can
> >> provide
> >> both the SQL Server name and Access file path. The example below uses a
> >> Foreach NodeList Enumerator to extract the values from an embedded XML
> >> document. Set the document source type to DirectInput and enter an XML
> >> string like the one below as the DocumentSource:
> >>
> >> <CopyList>
> >> <Task>
> >> <SqlServerName>SQLSERVER1</SqlServerName>
> >> <FileName>C:\db1.mdb</FileName>
> >> </Task>
> >> <Task>
> >> <SqlServerName>SQLSERVER2</SqlServerName>
> >> <FileName>C:\db2.mdb</FileName>
> >> </Task>
> >> </CopyList>
> >>
> >> Set the remaining collection properties as follows:
> >>
> >> EnumerationType: ElementCollection
> >> OuterXPathStringSourceType: DirectInput
> >> OuterXPathString: /CopyList/Task
> >> InnerElementType: NoteText
> >> InnerXPathStringSourceType: DirectInput
> >> InnerXPathString: *
> >>
> >> Under VariableMappings, map the server name variable to index 0 and the
> >> file
> >> name variable to index 1.
> >>
> >> Or course, you can use other sources for the server/file list. Just
> >> change
> >> the enumerator accordingly.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >> http://weblogs.sqlteam.com/dang/
> >>
> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> news:E7FA5A2C-CE9B-4A53-9E43-E5AE00CCDFBF@.microsoft.com...
> >> >I like the SSIS direction better however I am lost now as there are two
> >> > connection strings and both should change with each enumeration.
> >> > Before
> >> > the
> >> > first enumeration runs though, I get the following error and I am not
> >> > sure
> >> > where to start on this to get it fixed: (note, I grabbed the
> >> > foreachlooper
> >> > and inserted the template saved from a working export in the wizard
> >> > package -
> >> > I created an expression for the output connection by enumerating the
> >> > list
> >> > of
> >> > mdb files in the folder. I don't know how to tell it that it is an
> >> > Access
> >> > 2.0 so I have assumed that since the package that was saved worked okay
> >> > that
> >> > the Access 2.0 connection would be assumed)
> >> >
> >> > Error at AfghanTemplate [Connection manager
> >> > "DestinationConnectionOLEDB"]:
> >> > SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error
> >> > code:
> >> > 0x80004005.
> >> > An OLE DB record is available. Source: "Microsoft OLE DB Provider for
> >> > ODBC
> >> > Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver
> >> > Manager]
> >> > Data source name not found and no default driver specified".
> >> >
> >> > Error at Data Flow Task [Destination - CITYDB [22]]: SSIS Error Code
> >> > DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The
> >> > AcquireConnection
> >> > method call to the connection manager "DestinationConnectionOLEDB"
> >> > failed
> >> > with error code 0xC0202009. There may be error messages posted before
> >> > this
> >> > with more information on why the AcquireConnection method call failed.
> >> >
> >> > (Microsoft Visual Studio)
> >> > --
> >> > Regards,
> >> > Jamie
> >> >
> >> >
> >> > "Dan Guzman" wrote:
> >> >
> >> >> > I can create the file as a unicode text file - the collation is
> >> >> > tough
> >> >> > to
> >> >> > begin with - many characters between ascii 128 and 256. I'd really
> >> >> > like
> >> >> > to
> >> >> > be able to pop this out in the legacy Access 2.0 format.
> >> >>
> >> >> Have you tried the -w option instead of -C RAW?
> >> >>
> >> >> --
> >> >> Hope this helps.
> >> >>
> >> >> Dan Guzman
> >> >> SQL Server MVP
> >> >> http://weblogs.sqlteam.com/dang/
> >> >>
> >> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> >> news:FB9A702A-963F-4AB0-8C39-A1FFD1FC6E88@.microsoft.com...
> >> >> >I meant to include the error but hit return and sent the previous
> >> >> >out:
> >> >> >
> >> >> > Error = [Microsoft][SQL Native Client]Unable to resolve column level
> >> >> > collations
> >> >> >
> >> >> > Full line looks as follows:
> >> >> > bcp "use MyTable.MyCities; select * from ##TempExportData2" queryout
> >> >> > "C:\Halloran\ITALY_IT.mdb" -c -CRAW -T
> >> >> >
> >> >> > I can create the file as a unicode text file - the collation is
> >> >> > tough
> >> >> > to
> >> >> > begin with - many characters between ascii 128 and 256. I'd really
> >> >> > like
> >> >> > to
> >> >> > be able to pop this out in the legacy Access 2.0 format.
> >> >> >
> >> >> > --
> >> >> > Regards,
> >> >> > Jamie
> >> >> >
> >> >> >
> >> >> > "thejamie" wrote:
> >> >> >
> >> >> >> I am exporting data to roughly 250 Access 2.0 databases from tables
> >> >> >> in
> >> >> >> SQL
> >> >> >> Server 2005. For each table, I open one copy of the wizard and
> >> >> >> select
> >> >> >> a
> >> >> >> specifically named Access 2.0 database, export the table (it has to
> >> >> >> be
> >> >> >> edited
> >> >> >> to the correct format to keep the size down - in other words,
> >> >> >> varchar
> >> >> >> with a
> >> >> >> field size vs ntext). I do this 250 times. I suspect that bulk
> >> >> >> copy
> >> >> >> will do
> >> >> >> this:
> >> >> >>
> >> >> >> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> >> >> >>
> >> >> >> But I am having trouble locating the documentation for exporting to
> >> >> >> an
> >> >> >> older
> >> >> >> Access (DAO Access 2.0) database.
> >> >> >>
> >> >> >> Help?
> >> >> >>
> >> >> >> --
> >> >> >> Regards,
> >> >> >> Jamie
> >> >>
> >>
>|||That first connection from the database is also the same name as the database
itself... for example if the table in the SQL Database is call Mongolia, then
the Access Database target is called Mongolia.MDB. The target databases can
be quickly enumerated as they are simply files in a folder.
What would be really nice would be to use the same variable in the source
connection as I use in the target connection.
I can think of a couple of ways to do this... first is to create a text file
with all the table names in them and somehow script that as the source
variable, but I can't help but think I should also be able to enumerate the
table names that are in the source folder with the variable name from the
destination folder.
It would work like:
Source table Mongolia (source connection)=> data conversion => Destination
database Mongolia.mdb (destination file variable name)
Since the destination name and the source name are the same (minus the
".mdb" extension) could I somehow use scripting to remove the ".mdb" from the
destination variable name?
ie Replace(@.[User::@.mdbVariablename],'.mdb','') From your example below it
almost appears that I could also write an XML script to provide the named
variables of the tables.
Also, it looks like (worst case) I can actually copy the dataflow object 252
times and change the source and destination name physically - it would take
less time than what I do currently.
--
Regards,
Jamie
"Dan Guzman" wrote:
> >I like the SSIS direction better however I am lost now as there are two
> > connection strings and both should change with each enumeration.
> Two connection strings? I was under the impression that you had only one
> SQL Server source database and was planning to push that SQL data to many
> different Access databases. Since you need to change both connection
> strings for each iteration, you'll need to use a different enumerator and 2
> variables - one for the Access file and another for the SQL instance.
> In the SQL Server connection, use an expression to set the ConnectionString
> property using the server name variable. For example:
> "Data Source=" + @.SqlServerName + ";Initial
> Catalog=MyDatabase;Provider=SQLNCLI;Integrated Security=SSPI;Auto
> Translate=false;"
> In the Access database connection, use an expression to set the
> ConnectionString property using the file path variable. For example:
> "Data Source=" + @.FileName + ";OLE DB
> SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;"
> Set the Foreach Loop container Collection Enumerator to one that can provide
> both the SQL Server name and Access file path. The example below uses a
> Foreach NodeList Enumerator to extract the values from an embedded XML
> document. Set the document source type to DirectInput and enter an XML
> string like the one below as the DocumentSource:
> <CopyList>
> <Task>
> <SqlServerName>SQLSERVER1</SqlServerName>
> <FileName>C:\db1.mdb</FileName>
> </Task>
> <Task>
> <SqlServerName>SQLSERVER2</SqlServerName>
> <FileName>C:\db2.mdb</FileName>
> </Task>
> </CopyList>
> Set the remaining collection properties as follows:
> EnumerationType: ElementCollection
> OuterXPathStringSourceType: DirectInput
> OuterXPathString: /CopyList/Task
> InnerElementType: NoteText
> InnerXPathStringSourceType: DirectInput
> InnerXPathString: *
> Under VariableMappings, map the server name variable to index 0 and the file
> name variable to index 1.
> Or course, you can use other sources for the server/file list. Just change
> the enumerator accordingly.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:E7FA5A2C-CE9B-4A53-9E43-E5AE00CCDFBF@.microsoft.com...
> >I like the SSIS direction better however I am lost now as there are two
> > connection strings and both should change with each enumeration. Before
> > the
> > first enumeration runs though, I get the following error and I am not sure
> > where to start on this to get it fixed: (note, I grabbed the foreachlooper
> > and inserted the template saved from a working export in the wizard
> > package -
> > I created an expression for the output connection by enumerating the list
> > of
> > mdb files in the folder. I don't know how to tell it that it is an Access
> > 2.0 so I have assumed that since the package that was saved worked okay
> > that
> > the Access 2.0 connection would be assumed)
> >
> > Error at AfghanTemplate [Connection manager "DestinationConnectionOLEDB"]:
> > SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error
> > code:
> > 0x80004005.
> > An OLE DB record is available. Source: "Microsoft OLE DB Provider for
> > ODBC
> > Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver
> > Manager]
> > Data source name not found and no default driver specified".
> >
> > Error at Data Flow Task [Destination - CITYDB [22]]: SSIS Error Code
> > DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection
> > method call to the connection manager "DestinationConnectionOLEDB" failed
> > with error code 0xC0202009. There may be error messages posted before
> > this
> > with more information on why the AcquireConnection method call failed.
> >
> > (Microsoft Visual Studio)
> > --
> > Regards,
> > Jamie
> >
> >
> > "Dan Guzman" wrote:
> >
> >> > I can create the file as a unicode text file - the collation is tough
> >> > to
> >> > begin with - many characters between ascii 128 and 256. I'd really
> >> > like
> >> > to
> >> > be able to pop this out in the legacy Access 2.0 format.
> >>
> >> Have you tried the -w option instead of -C RAW?
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >> http://weblogs.sqlteam.com/dang/
> >>
> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> news:FB9A702A-963F-4AB0-8C39-A1FFD1FC6E88@.microsoft.com...
> >> >I meant to include the error but hit return and sent the previous out:
> >> >
> >> > Error = [Microsoft][SQL Native Client]Unable to resolve column level
> >> > collations
> >> >
> >> > Full line looks as follows:
> >> > bcp "use MyTable.MyCities; select * from ##TempExportData2" queryout
> >> > "C:\Halloran\ITALY_IT.mdb" -c -CRAW -T
> >> >
> >> > I can create the file as a unicode text file - the collation is tough
> >> > to
> >> > begin with - many characters between ascii 128 and 256. I'd really
> >> > like
> >> > to
> >> > be able to pop this out in the legacy Access 2.0 format.
> >> >
> >> > --
> >> > Regards,
> >> > Jamie
> >> >
> >> >
> >> > "thejamie" wrote:
> >> >
> >> >> I am exporting data to roughly 250 Access 2.0 databases from tables in
> >> >> SQL
> >> >> Server 2005. For each table, I open one copy of the wizard and select
> >> >> a
> >> >> specifically named Access 2.0 database, export the table (it has to be
> >> >> edited
> >> >> to the correct format to keep the size down - in other words, varchar
> >> >> with a
> >> >> field size vs ntext). I do this 250 times. I suspect that bulk copy
> >> >> will do
> >> >> this:
> >> >>
> >> >> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> >> >>
> >> >> But I am having trouble locating the documentation for exporting to an
> >> >> older
> >> >> Access (DAO Access 2.0) database.
> >> >>
> >> >> Help?
> >> >>
> >> >> --
> >> >> Regards,
> >> >> Jamie
> >>
>|||> That first connection from the database is also the same name as the
> database
> itself... for example if the table in the SQL Database is call Mongolia,
> then
> the Access Database target is called Mongolia.MDB. The target databases
> can
> be quickly enumerated as they are simply files in a folder.
I see - you have different source table names. That messes up the single
data flow approach because I don't think you can change the source table
name at runtime. You can change the source and target connections, though.
> Also, it looks like (worst case) I can actually copy the dataflow object
> 252
> times and change the source and destination name physically - it would
> take
> less time than what I do currently.
Be aware that if the table schema is different, you will also need to update
the meta-data at design time.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:709CC288-41CD-485F-9EF7-399D8C2AE95D@.microsoft.com...
> That first connection from the database is also the same name as the
> database
> itself... for example if the table in the SQL Database is call Mongolia,
> then
> the Access Database target is called Mongolia.MDB. The target databases
> can
> be quickly enumerated as they are simply files in a folder.
> What would be really nice would be to use the same variable in the source
> connection as I use in the target connection.
> I can think of a couple of ways to do this... first is to create a text
> file
> with all the table names in them and somehow script that as the source
> variable, but I can't help but think I should also be able to enumerate
> the
> table names that are in the source folder with the variable name from the
> destination folder.
> It would work like:
> Source table Mongolia (source connection)=> data conversion => Destination
> database Mongolia.mdb (destination file variable name)
> Since the destination name and the source name are the same (minus the
> ".mdb" extension) could I somehow use scripting to remove the ".mdb" from
> the
> destination variable name?
> ie Replace(@.[User::@.mdbVariablename],'.mdb','') From your example below
> it
> almost appears that I could also write an XML script to provide the named
> variables of the tables.
> Also, it looks like (worst case) I can actually copy the dataflow object
> 252
> times and change the source and destination name physically - it would
> take
> less time than what I do currently.
> --
> Regards,
> Jamie
>
> "Dan Guzman" wrote:
>> >I like the SSIS direction better however I am lost now as there are two
>> > connection strings and both should change with each enumeration.
>> Two connection strings? I was under the impression that you had only one
>> SQL Server source database and was planning to push that SQL data to many
>> different Access databases. Since you need to change both connection
>> strings for each iteration, you'll need to use a different enumerator and
>> 2
>> variables - one for the Access file and another for the SQL instance.
>> In the SQL Server connection, use an expression to set the
>> ConnectionString
>> property using the server name variable. For example:
>> "Data Source=" + @.SqlServerName + ";Initial
>> Catalog=MyDatabase;Provider=SQLNCLI;Integrated Security=SSPI;Auto
>> Translate=false;"
>> In the Access database connection, use an expression to set the
>> ConnectionString property using the file path variable. For example:
>> "Data Source=" + @.FileName + ";OLE DB
>> SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;"
>> Set the Foreach Loop container Collection Enumerator to one that can
>> provide
>> both the SQL Server name and Access file path. The example below uses a
>> Foreach NodeList Enumerator to extract the values from an embedded XML
>> document. Set the document source type to DirectInput and enter an XML
>> string like the one below as the DocumentSource:
>> <CopyList>
>> <Task>
>> <SqlServerName>SQLSERVER1</SqlServerName>
>> <FileName>C:\db1.mdb</FileName>
>> </Task>
>> <Task>
>> <SqlServerName>SQLSERVER2</SqlServerName>
>> <FileName>C:\db2.mdb</FileName>
>> </Task>
>> </CopyList>
>> Set the remaining collection properties as follows:
>> EnumerationType: ElementCollection
>> OuterXPathStringSourceType: DirectInput
>> OuterXPathString: /CopyList/Task
>> InnerElementType: NoteText
>> InnerXPathStringSourceType: DirectInput
>> InnerXPathString: *
>> Under VariableMappings, map the server name variable to index 0 and the
>> file
>> name variable to index 1.
>> Or course, you can use other sources for the server/file list. Just
>> change
>> the enumerator accordingly.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:E7FA5A2C-CE9B-4A53-9E43-E5AE00CCDFBF@.microsoft.com...
>> >I like the SSIS direction better however I am lost now as there are two
>> > connection strings and both should change with each enumeration.
>> > Before
>> > the
>> > first enumeration runs though, I get the following error and I am not
>> > sure
>> > where to start on this to get it fixed: (note, I grabbed the
>> > foreachlooper
>> > and inserted the template saved from a working export in the wizard
>> > package -
>> > I created an expression for the output connection by enumerating the
>> > list
>> > of
>> > mdb files in the folder. I don't know how to tell it that it is an
>> > Access
>> > 2.0 so I have assumed that since the package that was saved worked okay
>> > that
>> > the Access 2.0 connection would be assumed)
>> >
>> > Error at AfghanTemplate [Connection manager
>> > "DestinationConnectionOLEDB"]:
>> > SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error
>> > code:
>> > 0x80004005.
>> > An OLE DB record is available. Source: "Microsoft OLE DB Provider for
>> > ODBC
>> > Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver
>> > Manager]
>> > Data source name not found and no default driver specified".
>> >
>> > Error at Data Flow Task [Destination - CITYDB [22]]: SSIS Error Code
>> > DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The
>> > AcquireConnection
>> > method call to the connection manager "DestinationConnectionOLEDB"
>> > failed
>> > with error code 0xC0202009. There may be error messages posted before
>> > this
>> > with more information on why the AcquireConnection method call failed.
>> >
>> > (Microsoft Visual Studio)
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "Dan Guzman" wrote:
>> >
>> >> > I can create the file as a unicode text file - the collation is
>> >> > tough
>> >> > to
>> >> > begin with - many characters between ascii 128 and 256. I'd really
>> >> > like
>> >> > to
>> >> > be able to pop this out in the legacy Access 2.0 format.
>> >>
>> >> Have you tried the -w option instead of -C RAW?
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >> http://weblogs.sqlteam.com/dang/
>> >>
>> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> news:FB9A702A-963F-4AB0-8C39-A1FFD1FC6E88@.microsoft.com...
>> >> >I meant to include the error but hit return and sent the previous
>> >> >out:
>> >> >
>> >> > Error = [Microsoft][SQL Native Client]Unable to resolve column level
>> >> > collations
>> >> >
>> >> > Full line looks as follows:
>> >> > bcp "use MyTable.MyCities; select * from ##TempExportData2" queryout
>> >> > "C:\Halloran\ITALY_IT.mdb" -c -CRAW -T
>> >> >
>> >> > I can create the file as a unicode text file - the collation is
>> >> > tough
>> >> > to
>> >> > begin with - many characters between ascii 128 and 256. I'd really
>> >> > like
>> >> > to
>> >> > be able to pop this out in the legacy Access 2.0 format.
>> >> >
>> >> > --
>> >> > Regards,
>> >> > Jamie
>> >> >
>> >> >
>> >> > "thejamie" wrote:
>> >> >
>> >> >> I am exporting data to roughly 250 Access 2.0 databases from tables
>> >> >> in
>> >> >> SQL
>> >> >> Server 2005. For each table, I open one copy of the wizard and
>> >> >> select
>> >> >> a
>> >> >> specifically named Access 2.0 database, export the table (it has to
>> >> >> be
>> >> >> edited
>> >> >> to the correct format to keep the size down - in other words,
>> >> >> varchar
>> >> >> with a
>> >> >> field size vs ntext). I do this 250 times. I suspect that bulk
>> >> >> copy
>> >> >> will do
>> >> >> this:
>> >> >>
>> >> >> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
>> >> >>
>> >> >> But I am having trouble locating the documentation for exporting to
>> >> >> an
>> >> >> older
>> >> >> Access (DAO Access 2.0) database.
>> >> >>
>> >> >> Help?
>> >> >>
>> >> >> --
>> >> >> Regards,
>> >> >> Jamie
>> >>|||Many thanks for your perseverance Dan,
Currently because my arm is about to fall off from moving data, it seemed
feasible to write code in NET 2005 to do half of this for me. (see below).
This portion creates the tables in the target Access databases thus
automating half of the steps I hope to eliminate using SSIS (I haven't gotten
the hang of SSIS just yet for the enumeration and I am already almost a week
past my deadline on this project so...). While I'm still running the wizard,
it is now without nearly as many clicks.
Thought it might be better said in code.
Imports System.Data.SqlClient
Imports System.IO
Imports System
Imports ADOX
Imports System.Data
Imports System.Data.OleDb
Imports System.Reflection
Private msAccessCn As String = "Data Source=dbname;OLE DB
SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;"
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim path As String = "C:\Access 2.0\"
Dim searchPattern As String = ".mdb"
Dim fso As New Scripting.FileSystemObject
Dim f As Scripting.File
Dim s As String
Dim sTableName As String
For Each f In fso.GetFolder(path).Files
If InStr(LCase(f.Name), searchPattern) Then
s = f.Name
sTableName = Replace(f.Name.ToUpper, ".MDB", "")
s = Replace(msAccessCn.ToUpper, "dbname", s.ToUpper)
CreateTable(f.Name, s)
End If
Next
End Sub
Private Sub CreateTable(ByVal sfilename As String, ByVal scn As String)
'Table creation could also be automated except that the tables are Access
2.0 and
'this is likely to create the wrong version
'Dim cat As New ADOX.CatalogClass()
'cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" &
sfilename & ";" + "Jet OLEDB:Engine Type=5")
' Open Connection
Dim myConnection As New OleDbConnection(scn)
myConnection.Open()
'Create table base on your dataset tables.Below is an example
Dim strSQL As String
strSQL = "CREATE TABLE CITYDB " + "(abc varchar(110), Cityname
varchar(90))"
' Command for Creating Table
Dim myCommand As New OleDbCommand(strSQL, myConnection)
myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub
Regards,
Jamie
"Dan Guzman" wrote:
> > That first connection from the database is also the same name as the
> > database
> > itself... for example if the table in the SQL Database is call Mongolia,
> > then
> > the Access Database target is called Mongolia.MDB. The target databases
> > can
> > be quickly enumerated as they are simply files in a folder.
> I see - you have different source table names. That messes up the single
> data flow approach because I don't think you can change the source table
> name at runtime. You can change the source and target connections, though.
> > Also, it looks like (worst case) I can actually copy the dataflow object
> > 252
> > times and change the source and destination name physically - it would
> > take
> > less time than what I do currently.
> Be aware that if the table schema is different, you will also need to update
> the meta-data at design time.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:709CC288-41CD-485F-9EF7-399D8C2AE95D@.microsoft.com...
> > That first connection from the database is also the same name as the
> > database
> > itself... for example if the table in the SQL Database is call Mongolia,
> > then
> > the Access Database target is called Mongolia.MDB. The target databases
> > can
> > be quickly enumerated as they are simply files in a folder.
> >
> > What would be really nice would be to use the same variable in the source
> > connection as I use in the target connection.
> >
> > I can think of a couple of ways to do this... first is to create a text
> > file
> > with all the table names in them and somehow script that as the source
> > variable, but I can't help but think I should also be able to enumerate
> > the
> > table names that are in the source folder with the variable name from the
> > destination folder.
> >
> > It would work like:
> > Source table Mongolia (source connection)=> data conversion => Destination
> > database Mongolia.mdb (destination file variable name)
> >
> > Since the destination name and the source name are the same (minus the
> > ".mdb" extension) could I somehow use scripting to remove the ".mdb" from
> > the
> > destination variable name?
> >
> > ie Replace(@.[User::@.mdbVariablename],'.mdb','') From your example below
> > it
> > almost appears that I could also write an XML script to provide the named
> > variables of the tables.
> >
> > Also, it looks like (worst case) I can actually copy the dataflow object
> > 252
> > times and change the source and destination name physically - it would
> > take
> > less time than what I do currently.
> > --
> > Regards,
> > Jamie
> >
> >
> > "Dan Guzman" wrote:
> >
> >> >I like the SSIS direction better however I am lost now as there are two
> >> > connection strings and both should change with each enumeration.
> >>
> >> Two connection strings? I was under the impression that you had only one
> >> SQL Server source database and was planning to push that SQL data to many
> >> different Access databases. Since you need to change both connection
> >> strings for each iteration, you'll need to use a different enumerator and
> >> 2
> >> variables - one for the Access file and another for the SQL instance.
> >>
> >> In the SQL Server connection, use an expression to set the
> >> ConnectionString
> >> property using the server name variable. For example:
> >>
> >> "Data Source=" + @.SqlServerName + ";Initial
> >> Catalog=MyDatabase;Provider=SQLNCLI;Integrated Security=SSPI;Auto
> >> Translate=false;"
> >>
> >> In the Access database connection, use an expression to set the
> >> ConnectionString property using the file path variable. For example:
> >>
> >> "Data Source=" + @.FileName + ";OLE DB
> >> SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;"
> >>
> >> Set the Foreach Loop container Collection Enumerator to one that can
> >> provide
> >> both the SQL Server name and Access file path. The example below uses a
> >> Foreach NodeList Enumerator to extract the values from an embedded XML
> >> document. Set the document source type to DirectInput and enter an XML
> >> string like the one below as the DocumentSource:
> >>
> >> <CopyList>
> >> <Task>
> >> <SqlServerName>SQLSERVER1</SqlServerName>
> >> <FileName>C:\db1.mdb</FileName>
> >> </Task>
> >> <Task>
> >> <SqlServerName>SQLSERVER2</SqlServerName>
> >> <FileName>C:\db2.mdb</FileName>
> >> </Task>
> >> </CopyList>
> >>
> >> Set the remaining collection properties as follows:
> >>
> >> EnumerationType: ElementCollection
> >> OuterXPathStringSourceType: DirectInput
> >> OuterXPathString: /CopyList/Task
> >> InnerElementType: NoteText
> >> InnerXPathStringSourceType: DirectInput
> >> InnerXPathString: *
> >>
> >> Under VariableMappings, map the server name variable to index 0 and the
> >> file
> >> name variable to index 1.
> >>
> >> Or course, you can use other sources for the server/file list. Just
> >> change
> >> the enumerator accordingly.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >> http://weblogs.sqlteam.com/dang/
> >>
> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> news:E7FA5A2C-CE9B-4A53-9E43-E5AE00CCDFBF@.microsoft.com...
> >> >I like the SSIS direction better however I am lost now as there are two
> >> > connection strings and both should change with each enumeration.
> >> > Before
> >> > the
> >> > first enumeration runs though, I get the following error and I am not
> >> > sure
> >> > where to start on this to get it fixed: (note, I grabbed the
> >> > foreachlooper
> >> > and inserted the template saved from a working export in the wizard
> >> > package -
> >> > I created an expression for the output connection by enumerating the
> >> > list
> >> > of
> >> > mdb files in the folder. I don't know how to tell it that it is an
> >> > Access
> >> > 2.0 so I have assumed that since the package that was saved worked okay
> >> > that
> >> > the Access 2.0 connection would be assumed)
> >> >
> >> > Error at AfghanTemplate [Connection manager
> >> > "DestinationConnectionOLEDB"]:
> >> > SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error
> >> > code:
> >> > 0x80004005.
> >> > An OLE DB record is available. Source: "Microsoft OLE DB Provider for
> >> > ODBC
> >> > Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver
> >> > Manager]
> >> > Data source name not found and no default driver specified".
> >> >
> >> > Error at Data Flow Task [Destination - CITYDB [22]]: SSIS Error Code
> >> > DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The
> >> > AcquireConnection
> >> > method call to the connection manager "DestinationConnectionOLEDB"
> >> > failed
> >> > with error code 0xC0202009. There may be error messages posted before
> >> > this
> >> > with more information on why the AcquireConnection method call failed.
> >> >
> >> > (Microsoft Visual Studio)
> >> > --
> >> > Regards,
> >> > Jamie
> >> >
> >> >
> >> > "Dan Guzman" wrote:
> >> >
> >> >> > I can create the file as a unicode text file - the collation is
> >> >> > tough
> >> >> > to
> >> >> > begin with - many characters between ascii 128 and 256. I'd really
> >> >> > like
> >> >> > to
> >> >> > be able to pop this out in the legacy Access 2.0 format.
> >> >>
> >> >> Have you tried the -w option instead of -C RAW?
> >> >>
> >> >> --
> >> >> Hope this helps.
> >> >>
> >> >> Dan Guzman
> >> >> SQL Server MVP
> >> >> http://weblogs.sqlteam.com/dang/
> >> >>
> >> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> >> news:FB9A702A-963F-4AB0-8C39-A1FFD1FC6E88@.microsoft.com...
> >> >> >I meant to include the error but hit return and sent the previous
> >> >> >out:
> >> >> >
> >> >> > Error = [Microsoft][SQL Native Client]Unable to resolve column level
> >> >> > collations
> >> >> >
> >> >> > Full line looks as follows:
> >> >> > bcp "use MyTable.MyCities; select * from ##TempExportData2" queryout
> >> >> > "C:\Halloran\ITALY_IT.mdb" -c -CRAW -T
> >> >> >
> >> >> > I can create the file as a unicode text file - the collation is
> >> >> > tough
> >> >> > to
> >> >> > begin with - many characters between ascii 128 and 256. I'd really
> >> >> > like
> >> >> > to
> >> >> > be able to pop this out in the legacy Access 2.0 format.
> >> >> >
> >> >> > --
> >> >> > Regards,
> >> >> > Jamie
> >> >> >
> >> >> >
> >> >> > "thejamie" wrote:
> >> >> >
> >> >> >> I am exporting data to roughly 250 Access 2.0 databases from tables
> >> >> >> in
> >> >> >> SQL
> >> >> >> Server 2005. For each table, I open one copy of the wizard and
> >> >> >> select
> >> >> >> a
> >> >> >> specifically named Access 2.0 database, export the table (it has to
> >> >> >> be
> >> >> >> edited
> >> >> >> to the correct format to keep the size down - in other words,
> >> >> >> varchar
> >> >> >> with a
> >> >> >> field size vs ntext). I do this 250 times. I suspect that bulk
> >> >> >> copy
> >> >> >> will do
> >> >> >> this:
> >> >> >>
> >> >> >> bcp mydatabase.mycountrytable out mycountrytable.mdb -T -c
> >> >> >>
> >> >> >> But I am having trouble locating the documentation for exporting to
> >> >> >> an
> >> >> >> older
> >> >> >> Access (DAO Access 2.0) database.
> >> >> >>
> >> >> >> Help?
> >> >> >>
> >> >> >> --
> >> >> >> Regards,
> >> >> >> Jamie
> >> >>
> >>
>
Subscribe to:
Posts (Atom)