Bulk Insert Runs out of space

Hello,
I am running a Bulk Insert and getting the error that the PRIMARY file group
is full. All the databases in the server have the Autogrow check box
selected and there is over a 100 GB space available on the hard drive. The
transaction log is set to autogrow as well.
Is there a maximum file size for the primary datafile? The size is
currently at 1.8 GB
Can someone help with some ideas for getting around this error.
Thanks
Hardee Mahoney
Washington, DC USA
Thank you for your reply. That explains it.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:51bm4eF1jbqr6U1@.mid.individual.net...
> hi,
> Hardee Mahoney wrote:
> MSDE has a limit of 2gb for each database (data file(s) only)...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
> -- remove DMO to reply
>
|||Remember that first, bulk copy does not write to the transaction log. Next,
SQL Express has twice (4GB) data capacity. However, if you're running out of
space with MSDE, I would reconsider what you're storing in the database. Are
you using it to store BLOBs? If so, consider storing those on a file share
and putting the path in the database.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...
------
Microsoft MVP, Author, Mentor
Microsoft MVP
"Hardee Mahoney" <hardee_mahoney@.abtassoc.com> wrote in message
news:OLMB568OHHA.2140@.TK2MSFTNGP03.phx.gbl...
> Thank you for your reply. That explains it.
>
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:51bm4eF1jbqr6U1@.mid.individual.net...
>

BULK INSERT not loading all rows from a text file.

I have written a stored procedure that is used to load data into a SQL
Server 2000 and 2005 databases from some 40ish different text files.
One of the text files is loading all of the rows apart from the last
one. No error message is issued when this happens.
The command I am using to load the data is as follows:
BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
\Basicdata.bad')
(I understand that the ERRORFILE option only works in SQL Server 2005,
but 2000 seems to be able to ignore it without a problem.)
To fix the problem, I have to insert a 'tab' after the last section of
data in the last row.
49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
02
49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
02 02<tab>
Unless the last tab is manually inserted, the last row does not get
loaded to the database, and no error message is issued. If the tab is
inserted all of the data is loaded correctly. However, the text files
come from a third party so I cannot ensure that this problem will not
occur again.
The .fmt file used to load the data is as follows:
8.0
11
1 SQLCHAR 0 12 "\t" 1
BasicDataID ""
2 SQLCHAR 0 12 "\t" 2
BasicTaxOverrideID ""
3 SQLCHAR 0 24 "\t" 3
LegalEffectiveDate ""
4 SQLCHAR 0 12 "\t" 4
ReleaseNo ""
5 SQLCHAR 0 50 "\t" 5
BasicDataDesc SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "\t" 6
PassFlag SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 2 "\t" 7
PassType SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 2 "\t" 8
BaseType SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "\t" 9
DateFlag SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 2 "\t" 10
RoundingLevel SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 8 "\r\n" 11
TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
My question is, is there any way to trigger an error message to denote
that (in my example) the last row did not get loaded successfully? Is
there a parameter associated with the BULK INSERT command that I am
not currently using that could help with this problem?
Hi Nick
"Nick" wrote:

> I have written a stored procedure that is used to load data into a SQL
> Server 2000 and 2005 databases from some 40ish different text files.
> One of the text files is loading all of the rows apart from the last
> one. No error message is issued when this happens.
> The command I am using to load the data is as follows:
> BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
> ( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
> MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
> \Basicdata.bad')
> (I understand that the ERRORFILE option only works in SQL Server 2005,
> but 2000 seems to be able to ignore it without a problem.)
> To fix the problem, I have to insert a 'tab' after the last section of
> data in the last row.
>
> 49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
> 02
> 49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
> 02 02<tab>
>
> Unless the last tab is manually inserted, the last row does not get
> loaded to the database, and no error message is issued. If the tab is
> inserted all of the data is loaded correctly. However, the text files
> come from a third party so I cannot ensure that this problem will not
> occur again.
> The .fmt file used to load the data is as follows:
> 8.0
> 11
> 1 SQLCHAR 0 12 "\t" 1
> BasicDataID ""
> 2 SQLCHAR 0 12 "\t" 2
> BasicTaxOverrideID ""
> 3 SQLCHAR 0 24 "\t" 3
> LegalEffectiveDate ""
> 4 SQLCHAR 0 12 "\t" 4
> ReleaseNo ""
> 5 SQLCHAR 0 50 "\t" 5
> BasicDataDesc SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 1 "\t" 6
> PassFlag SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 2 "\t" 7
> PassType SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 2 "\t" 8
> BaseType SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "\t" 9
> DateFlag SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 2 "\t" 10
> RoundingLevel SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 8 "\r\n" 11
> TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
> My question is, is there any way to trigger an error message to denote
> that (in my example) the last row did not get loaded successfully? Is
> there a parameter associated with the BULK INSERT command that I am
> not currently using that could help with this problem?
>
It looks like the last field is not present if you removed this from the
format file it should work!
John

BULK INSERT not loading all rows from a text file.

I have written a stored procedure that is used to load data into a SQL
Server 2000 and 2005 databases from some 40ish different text files.
One of the text files is loading all of the rows apart from the last
one. No error message is issued when this happens.
The command I am using to load the data is as follows:
BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicD
ata.fmt',
MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
\Basicdata.bad')
(I understand that the ERRORFILE option only works in SQL Server 2005,
but 2000 seems to be able to ignore it without a problem.)
To fix the problem, I have to insert a 'tab' after the last section of
data in the last row.
49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
02
49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
02 02<tab>
Unless the last tab is manually inserted, the last row does not get
loaded to the database, and no error message is issued. If the tab is
inserted all of the data is loaded correctly. However, the text files
come from a third party so I cannot ensure that this problem will not
occur again.
The .fmt file used to load the data is as follows:
8.0
11
1 SQLCHAR 0 12 "\t" 1
BasicDataID ""
2 SQLCHAR 0 12 "\t" 2
BasicTaxOverrideID ""
3 SQLCHAR 0 24 "\t" 3
LegalEffectiveDate ""
4 SQLCHAR 0 12 "\t" 4
ReleaseNo ""
5 SQLCHAR 0 50 "\t" 5
BasicDataDesc SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "\t" 6
PassFlag SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 2 "\t" 7
PassType SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 2 "\t" 8
BaseType SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "\t" 9
DateFlag SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 2 "\t" 10
RoundingLevel SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 8 "\r\n" 11
TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
My question is, is there any way to trigger an error message to denote
that (in my example) the last row did not get loaded successfully? Is
there a parameter associated with the BULK INSERT command that I am
not currently using that could help with this problem?Hi Nick
"Nick" wrote:

> I have written a stored procedure that is used to load data into a SQL
> Server 2000 and 2005 databases from some 40ish different text files.
> One of the text files is loading all of the rows apart from the last
> one. No error message is issued when this happens.
> The command I am using to load the data is as follows:
> BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
> ( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicD
ata.fmt',
> MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
> \Basicdata.bad')
> (I understand that the ERRORFILE option only works in SQL Server 2005,
> but 2000 seems to be able to ignore it without a problem.)
> To fix the problem, I have to insert a 'tab' after the last section of
> data in the last row.
>
> 49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
> 02
> 49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
> 02 02<tab>
>
> Unless the last tab is manually inserted, the last row does not get
> loaded to the database, and no error message is issued. If the tab is
> inserted all of the data is loaded correctly. However, the text files
> come from a third party so I cannot ensure that this problem will not
> occur again.
> The .fmt file used to load the data is as follows:
> 8.0
> 11
> 1 SQLCHAR 0 12 "\t" 1
> BasicDataID ""
> 2 SQLCHAR 0 12 "\t" 2
> BasicTaxOverrideID ""
> 3 SQLCHAR 0 24 "\t" 3
> LegalEffectiveDate ""
> 4 SQLCHAR 0 12 "\t" 4
> ReleaseNo ""
> 5 SQLCHAR 0 50 "\t" 5
> BasicDataDesc SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 1 "\t" 6
> PassFlag SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 2 "\t" 7
> PassType SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 2 "\t" 8
> BaseType SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "\t" 9
> DateFlag SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 2 "\t" 10
> RoundingLevel SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 8 "\r\n" 11
> TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
> My question is, is there any way to trigger an error message to denote
> that (in my example) the last row did not get loaded successfully? Is
> there a parameter associated with the BULK INSERT command that I am
> not currently using that could help with this problem?
>
It looks like the last field is not present if you removed this from the
format file it should work!
John

BULK INSERT not loading all rows from a text file.

I have written a stored procedure that is used to load data into a SQL
Server 2000 and 2005 databases from some 40ish different text files.
One of the text files is loading all of the rows apart from the last
one. No error message is issued when this happens.
The command I am using to load the data is as follows:
BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
\Basicdata.bad')
(I understand that the ERRORFILE option only works in SQL Server 2005,
but 2000 seems to be able to ignore it without a problem.)
To fix the problem, I have to insert a 'tab' after the last section of
data in the last row.
49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
02
49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
02 02<tab>
Unless the last tab is manually inserted, the last row does not get
loaded to the database, and no error message is issued. If the tab is
inserted all of the data is loaded correctly. However, the text files
come from a third party so I cannot ensure that this problem will not
occur again.
The .fmt file used to load the data is as follows:
8.0
11
1 SQLCHAR 0 12 "\t" 1
BasicDataID ""
2 SQLCHAR 0 12 "\t" 2
BasicTaxOverrideID ""
3 SQLCHAR 0 24 "\t" 3
LegalEffectiveDate ""
4 SQLCHAR 0 12 "\t" 4
ReleaseNo ""
5 SQLCHAR 0 50 "\t" 5
BasicDataDesc SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "\t" 6
PassFlag SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 2 "\t" 7
PassType SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 2 "\t" 8
BaseType SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "\t" 9
DateFlag SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 2 "\t" 10
RoundingLevel SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 8 "\r\n" 11
TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
My question is, is there any way to trigger an error message to denote
that (in my example) the last row did not get loaded successfully? Is
there a parameter associated with the BULK INSERT command that I am
not currently using that could help with this problem?Hi Nick
"Nick" wrote:
> I have written a stored procedure that is used to load data into a SQL
> Server 2000 and 2005 databases from some 40ish different text files.
> One of the text files is loading all of the rows apart from the last
> one. No error message is issued when this happens.
> The command I am using to load the data is as follows:
> BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
> ( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
> MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
> \Basicdata.bad')
> (I understand that the ERRORFILE option only works in SQL Server 2005,
> but 2000 seems to be able to ignore it without a problem.)
> To fix the problem, I have to insert a 'tab' after the last section of
> data in the last row.
>
> 49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
> 02
> 49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
> 02 02<tab>
>
> Unless the last tab is manually inserted, the last row does not get
> loaded to the database, and no error message is issued. If the tab is
> inserted all of the data is loaded correctly. However, the text files
> come from a third party so I cannot ensure that this problem will not
> occur again.
> The .fmt file used to load the data is as follows:
> 8.0
> 11
> 1 SQLCHAR 0 12 "\t" 1
> BasicDataID ""
> 2 SQLCHAR 0 12 "\t" 2
> BasicTaxOverrideID ""
> 3 SQLCHAR 0 24 "\t" 3
> LegalEffectiveDate ""
> 4 SQLCHAR 0 12 "\t" 4
> ReleaseNo ""
> 5 SQLCHAR 0 50 "\t" 5
> BasicDataDesc SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 1 "\t" 6
> PassFlag SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 2 "\t" 7
> PassType SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 2 "\t" 8
> BaseType SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "\t" 9
> DateFlag SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 2 "\t" 10
> RoundingLevel SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 8 "\r\n" 11
> TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
> My question is, is there any way to trigger an error message to denote
> that (in my example) the last row did not get loaded successfully? Is
> there a parameter associated with the BULK INSERT command that I am
> not currently using that could help with this problem?
>
It looks like the last field is not present if you removed this from the
format file it should work!
John

bulk insert into partition view (sql 2000)

Hi, experts
I have ~ 50 table with the same structure in 4 databases. These tables are
union'ed in partition view.
Insert into partition view works perfect. I would like to load data into PV
with bulk insert from text file. But there is the restriction of PV - I
cannot do the bulk load into PV.
Then I created 3'rd table - and created the "instaed of insert" trigger.
This trigger do the insert into PV from inserted table. But I get the same
error.
Is it possible to implement insert into PV from text file using sql 2000 or
even sql 2005. Thanks in advance.
Ramunas BalukonisRamunas
ad for as I know, you should be able to do from bulk file also. Your problem
prablbly be with constraints. see especially that all NOT NULL columns are
updated and .txt is in proper format.
--
Regards
R.D
--Knowledge gets doubled when shared
"Ramunas Balukonis" wrote:

> Hi, experts
> I have ~ 50 table with the same structure in 4 databases. These tables are
> union'ed in partition view.
> Insert into partition view works perfect. I would like to load data into P
V
> with bulk insert from text file. But there is the restriction of PV - I
> cannot do the bulk load into PV.
> Then I created 3'rd table - and created the "instaed of insert" trigger.
> This trigger do the insert into PV from inserted table. But I get the same
> error.
> Is it possible to implement insert into PV from text file using sql 2000 o
r
> even sql 2005. Thanks in advance.
> Ramunas Balukonis
>
>|||RD, thanks for answer!
but when I do bulk insert into table directly, bulk insert success! So, the
problem is about bulk inserting into PV. Now I'm looking for a workaround.
Thanks
Ramunas
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:1FC22B8E-5B9C-4978-8DFA-63766189AA27@.microsoft.com...
> Ramunas
> ad for as I know, you should be able to do from bulk file also. Your
problem
> prablbly be with constraints. see especially that all NOT NULL columns are
> updated and .txt is in proper format.
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Ramunas Balukonis" wrote:
>
are
PV
same
or|||I found solution!
I do insert into PV using linked servers!
Ramunas
"Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
news:1128423828.988065@.loger.vpmarket.int...
> RD, thanks for answer!
> but when I do bulk insert into table directly, bulk insert success! So,
the
> problem is about bulk inserting into PV. Now I'm looking for a workaround.
> Thanks
> Ramunas
>
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:1FC22B8E-5B9C-4978-8DFA-63766189AA27@.microsoft.com...
> problem
are
> are
into
> PV
I
trigger.
> same
2000
> or
>

Bulk Insert and Data Aggregation

I manage a legacy system that dumps it's data into a number of different
databases (same schema) on a nightly basis using bulk insert. I need to
formulate a strategy for efficiently aggregating that data into a single
database right after these nightly extractions complete. Here is my current
stategy:
1. Duplicate the legacy system's database schema and add an identifier column
to specify which database the data loaded from.
2. Each night, delete all records in the table.
3. Each night, for each database:
3a. Set each table's default value to a value that references the current
database being loaded.
3b. Use the legacy system's flat files and format files to bulk insert into
the database.
3c. Clear the default value.
What other steps would faciliate performance? Dropping and recreating the
indexes? Does anyone forsee faults in this strategy?
Thanks,
Matt
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1Hi
You could add a view with your extra "column" or use a query to export from
each database and then you don't have to worry about the defaults as the
value is in the flat files. This would mean that you could load in parrallel
although having separate tables for each database may allow you to reduce
contention when doing this. If you had separate tables then you could use a
partitioned view to "join" them all, this view could identify the source if
necessary.
John
"lotek via SQLMonster.com" <u16539@.uwe> wrote in message
news:7f0a31b9b1f67@.uwe...
>I manage a legacy system that dumps it's data into a number of different
> databases (same schema) on a nightly basis using bulk insert. I need to
> formulate a strategy for efficiently aggregating that data into a single
> database right after these nightly extractions complete. Here is my
> current
> stategy:
> 1. Duplicate the legacy system's database schema and add an identifier
> column
> to specify which database the data loaded from.
> 2. Each night, delete all records in the table.
> 3. Each night, for each database:
> 3a. Set each table's default value to a value that references the current
> database being loaded.
> 3b. Use the legacy system's flat files and format files to bulk insert
> into
> the database.
> 3c. Clear the default value.
>
> What other steps would faciliate performance? Dropping and recreating the
> indexes? Does anyone forsee faults in this strategy?
> Thanks,
> Matt
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1
>

BULK INSERT and BCP Active Directory Delegation after SQL 2005 migration

Not sure if anyone else out there is having this problem but we recently migrated some of our databases to SQL 2005. We have some processes that use BULK INSERT or BCP to import the data. On the new server I required that the files we import are stored and held on the FileStore not the database server.

If the user uses SQL authentication this work fine. However, if the user is using Integrated Authentication it fails saying that it could not find the file or access is denied.

The SQL Server services are using a domain account and they have access to the share where the file resides, otherwise SQL authentication would not work.

After some web searches I found out that in SQL 2000 would automatically impersonate as the SQL Service account and they figured that was a security hole (which it is) so now in 2005 you can't do that without allowing kerberos delegation which my Active Directory guys forbid because it is "evil".

So the only other thing I can think of is to use certificates but how do I go about that? Or is there anything else I can do besides use SQL logins.

Just a side note since I'm talking about BCP what happened in version 9.0? We had to grab version 8.0 in order for the bloody thing to work.

Thanks

Rich

Ok, worked with HP and figured out how to fix it. In case any of you are interested below is the documentation I created for myself:

Process Details and Instructions

1. The issue here is using Integrated Authentication to execute a BULK INSERT command (or BCP) from a client workstation, which will retrieve a file that is located in a different location other than the target SQL Server. This will result in a "double hop" authentication that will not work unless you have certificates or delegation.

A couple of things to make sure that are done before reaching this stage.

ü The SQL Service is running under an Active Directory account

ü The SQL Service account has sufficient rights on the SQL Server (Local Admin)

ü The SQL Service account has rights to the remote file via network share

ü The user executing the command has rights to the remote file via network share.

SQL 2000 - This should not be an issue with SQL 2000 because it will automatically delegate on your behalf that is a considered a security hole.

SQL 2005 - They fixed up the security hole by not allowing delegation unless allowed in Active Directory.

You can reproduce the error by executing something like this against the SQL 2005 server:

CREATE TABLE #In_File(In_Data varchar(2000))

BULK INSERT #In_File FROM '\\share\SQL\DEV\20061106.csv' WITH (FIELDTERMINATOR='\0', ROWTERMINATOR='')

The results should look something like this:

Msg 4861, Level 16, State 1, Line 6

Cannot bulk load because the file "\\share\SQL\DEV\20061106.csv" could not be opened. Operating system error code 5(Access is denied.).

If you were to execute the statement on the SQL Server itself then it will work. If you were to move the file locally to the SQL Server this will work. Both will work because you are no longer doing a double hop to execute and get the file.

2. Now that we have the error we are going to check the Service Principal Name in Active Directory to ensure that the service can be delegated to do this task. To do this we need the utility setspn.exe which is available in the Windows Resource Kit or can be downloaded here (http://www.microsoft.com/downloads/details.aspx?familyid=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en <blocked::http://www.microsoft.com/downloads/details.aspx?familyid=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en> )

After installation on your workstation run the following from command prompt:

C:\Program Files\Support Tools> setspn -L serviceaccount

(eg C:\Program Files\Support Tools> setspn -L SQLAccount)

One result you will get is this:

Registered ServicePrincipalNames for CN=SQLAccount,OU=Users,OU=Vancouver,DC=Domain,DC=corp:

The result you should get is this:

Registered ServicePrincipalNames for CN=SQLAccount,OU=Users,OU=Vancouver,DC=domain,DC=corp:

MSSQLSvc/sqlserver.domain.corp:1433

Notice the extra line MSSQLSvc/sqlserver.domain.com:1433? This "binds" the account to the SQL Service to allow the Service to delegate as the account.

3. Now that we verified that there is no SPN for the MSSQLSvc let's create one. All we do is use SETSPN to add the service by executing the following:

Setspn -A MSSQLSvc/sqlserver.domain.corp:1433 Domain\SQLAccount

4. Now that we created the SPN for the SQL Service we must allow it to delegate. Using Active Directory Users and Computers go to the properties of the account the SQL Service is running under.

There should be a DELEGATION tab there, if there isn't then the SPN was not set correctly (or not done at all) and you need to repeat step 2 and 3.

In the Delegation tab you will want to select "Trust this user for delegation to any service (Kerberos only)

5. Last step is to restart the SQL Service that can be done in the services section on the server. This will re-authenticate the user to the domain and it will get the new delegation changes.

6. Retry the BULK INSERT command from step 1 and it should work now.

Additional Information

Here are the resources used to solve the problem:

Windows 2003 Resource Kit (includes SPN)

http://www.microsoft.com/downloads/details.aspx?familyid=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en <blocked::http://www.microsoft.com/downloads/details.aspx?familyid=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en>

MSDN Article on using SETSPN and the Delegation Issue

http://msdn.microsoft.com/msdnmag/issues/05/09/SecurityBriefs/default.aspx <blocked::http://msdn.microsoft.com/msdnmag/issues/05/09/SecurityBriefs/default.aspx>

TechNet Article on Allowing an User to be Delegated

http://technet2.microsoft.com/WindowsServer/en/library/bef202b0-c8e9-4999-9af7-f56b991a4fd41033.mspx?mfr=true <blocked::http://technet2.microsoft.com/WindowsServer/en/library/bef202b0-c8e9-4999-9af7-f56b991a4fd41033.mspx?mfr=true>

BULK INSERT and BCP Active Directory Delegation after SQL 2005 migration

Not sure if anyone else out there is having this problem but we recently migrated some of our databases to SQL 2005. We have some processes that use BULK INSERT or BCP to import the data. On the new server I required that the files we import are stored and held on the FileStore not the database server.

If the user uses SQL authentication this work fine. However, if the user is using Integrated Authentication it fails saying that it could not find the file or access is denied.

The SQL Server services are using a domain account and they have access to the share where the file resides, otherwise SQL authentication would not work.

After some web searches I found out that in SQL 2000 would automatically impersonate as the SQL Service account and they figured that was a security hole (which it is) so now in 2005 you can't do that without allowing kerberos delegation which my Active Directory guys forbid because it is "evil".

So the only other thing I can think of is to use certificates but how do I go about that? Or is there anything else I can do besides use SQL logins.

Just a side note since I'm talking about BCP what happened in version 9.0? We had to grab version 8.0 in order for the bloody thing to work.

Thanks

Rich

Ok, worked with HP and figured out how to fix it. In case any of you are interested below is the documentation I created for myself:

Process Details and Instructions

1. The issue here is using Integrated Authentication to execute a BULK INSERT command (or BCP) from a client workstation, which will retrieve a file that is located in a different location other than the target SQL Server. This will result in a "double hop" authentication that will not work unless you have certificates or delegation.

A couple of things to make sure that are done before reaching this stage.

ü The SQL Service is running under an Active Directory account

ü The SQL Service account has sufficient rights on the SQL Server (Local Admin)

ü The SQL Service account has rights to the remote file via network share

ü The user executing the command has rights to the remote file via network share.

SQL 2000 - This should not be an issue with SQL 2000 because it will automatically delegate on your behalf that is a considered a security hole.

SQL 2005 - They fixed up the security hole by not allowing delegation unless allowed in Active Directory.

You can reproduce the error by executing something like this against the SQL 2005 server:

CREATE TABLE #In_File(In_Data varchar(2000))

BULK INSERT #In_File FROM '\\share\SQL\DEV\20061106.csv' WITH (FIELDTERMINATOR='\0', ROWTERMINATOR='')

The results should look something like this:

Msg 4861, Level 16, State 1, Line 6

Cannot bulk load because the file "\\share\SQL\DEV\20061106.csv" could not be opened. Operating system error code 5(Access is denied.).

If you were to execute the statement on the SQL Server itself then it will work. If you were to move the file locally to the SQL Server this will work. Both will work because you are no longer doing a double hop to execute and get the file.

2. Now that we have the error we are going to check the Service Principal Name in Active Directory to ensure that the service can be delegated to do this task. To do this we need the utility setspn.exe which is available in the Windows Resource Kit or can be downloaded here (http://www.microsoft.com/downloads/details.aspx?familyid=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en <blocked::http://www.microsoft.com/downloads/details.aspx?familyid=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en> )

After installation on your workstation run the following from command prompt:

C:\Program Files\Support Tools> setspn -L serviceaccount

(eg C:\Program Files\Support Tools> setspn -L SQLAccount)

One result you will get is this:

Registered ServicePrincipalNames for CN=SQLAccount,OU=Users,OU=Vancouver,DC=Domain,DC=corp:

The result you should get is this:

Registered ServicePrincipalNames for CN=SQLAccount,OU=Users,OU=Vancouver,DC=domain,DC=corp:

MSSQLSvc/sqlserver.domain.corp:1433

Notice the extra line MSSQLSvc/sqlserver.domain.com:1433? This "binds" the account to the SQL Service to allow the Service to delegate as the account.

3. Now that we verified that there is no SPN for the MSSQLSvc let's create one. All we do is use SETSPN to add the service by executing the following:

Setspn -A MSSQLSvc/sqlserver.domain.corp:1433 Domain\SQLAccount

4. Now that we created the SPN for the SQL Service we must allow it to delegate. Using Active Directory Users and Computers go to the properties of the account the SQL Service is running under.

There should be a DELEGATION tab there, if there isn't then the SPN was not set correctly (or not done at all) and you need to repeat step 2 and 3.

In the Delegation tab you will want to select "Trust this user for delegation to any service (Kerberos only)

5. Last step is to restart the SQL Service that can be done in the services section on the server. This will re-authenticate the user to the domain and it will get the new delegation changes.

6. Retry the BULK INSERT command from step 1 and it should work now.

Additional Information

Here are the resources used to solve the problem:

Windows 2003 Resource Kit (includes SPN)

http://www.microsoft.com/downloads/details.aspx?familyid=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en <blocked::http://www.microsoft.com/downloads/details.aspx?familyid=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en>

MSDN Article on using SETSPN and the Delegation Issue

http://msdn.microsoft.com/msdnmag/issues/05/09/SecurityBriefs/default.aspx <blocked::http://msdn.microsoft.com/msdnmag/issues/05/09/SecurityBriefs/default.aspx>

TechNet Article on Allowing an User to be Delegated

http://technet2.microsoft.com/WindowsServer/en/library/bef202b0-c8e9-4999-9af7-f56b991a4fd41033.mspx?mfr=true <blocked::http://technet2.microsoft.com/WindowsServer/en/library/bef202b0-c8e9-4999-9af7-f56b991a4fd41033.mspx?mfr=true>

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
> >> >>
> >>
>