Bulk Logging - Took longer.

Hello,
We have a job that runs for a duration - n minutes.
There are quite a few stored procedures in it that use the 'select into'
statement.
Tried switching over to 'Bulk logging' to reduce the duration of the run.
Observed that the run actually took n+30 minutes.
Our expectation was that it would be either n or n minus something.
Did not expect the duration to increase.
What could the reason be behind this?
Cheers
SQLCatZ
SQLCatz
Have you looked at an execution plan of the SELECT * INTO .. statements
Do you have any WHERE conditions in the source table? How big is the
table/s? Have you seen optimizer was available to use index to produce the
query?
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:092FC6D0-FC93-4143-9886-4D1C0D61CED7@.microsoft.com...
> Hello,
> We have a job that runs for a duration - n minutes.
> There are quite a few stored procedures in it that use the 'select into'
> statement.
> Tried switching over to 'Bulk logging' to reduce the duration of the run.
> Observed that the run actually took n+30 minutes.
> Our expectation was that it would be either n or n minus something.
> Did not expect the duration to increase.
> What could the reason be behind this?
> Cheers
> SQLCatZ
>
|||Uri,
I have'nt looked at the execution plans as yet.
But can confirm that the source table has a where clause and the columns in
it are part of existing indexes.
Cheers!
SQLCatZ
sql

Bulk Insert.

Is there any way I can use the following command like that ?
BULK INSERT Northwind.dbo.[Order Details]
FROM (select CSVTextFile from tblCSV)
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
Thanks in advance
The syntax of BULK INSERT is

quote:

---

BULK INSERT [ [ 'database_name'.] [ 'owner' ].] { 'table_name' FROM
'data_file' }...
'data_file'
Is the full path of the data file that contains data to copy into the
specified table or view. BULK INSERT can copy data from a disk (including
network, floppy disk, hard disk, and so on).
data_file must specify a valid path from the server on which SQL Server is
running. If data_file is a remote file, specify the Universal Naming
Convention (UNC) name.

---

Please refer to Books Online for examples of how to use BULK INSERT
http://msdn.microsoft.com/library/de...ba-bz_4fec.asp
Cristian Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET
"Rogers" <Rogers@.mailstuff.com> wrote in message
news:Oyx71bHuFHA.2624@.TK2MSFTNGP12.phx.gbl...
> Is there any way I can use the following command like that ?
> BULK INSERT Northwind.dbo.[Order Details]
> FROM (select CSVTextFile from tblCSV)
> WITH
> (
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n'
> )
> Thanks in advance
>
sql

Bulk Insert statement

Hi,

I was using a BULK INSERT statement in a stored procedure.Could any one help me out on one prob.I wanted to let the user select the file he wants to update and then i want to pass this address as a parameter in the stored procedure.

Suppose there is a parameter @.loc,so i want to use this parameter as

Bulk insert TableName from @.loc with(fieldterminator=',')
plz help me out thxI haven't test this but you may take help of Dynamic SQL, http://www.sommarskog.se/dynamic_sql.html fyi.|||Hi its been days now is there no one who could help me out?Is it because i am asking something that is impossible or something wrong plz let me know so that i would not waste my time n go ahead with something that would fulfill my task|||Did you even try dynamic sql as suggested to you?
declare @.fn varchar(255)
set @.fn='\\mypc\tmp\tmp1.txt'
exec ('bulk insert mytable from '''+@.fn+''' with(fieldterminator='','')')|||Hi its been days now is there no one who could help me out?Is it because i am asking something that is impossible or something wrong plz let me know so that i would not waste my time n go ahead with something that would fulfill my task
u haven't replied to Satya's solution.Then how we know that u got the answer or not?|||Even here u are giving the static location of the file from which u want the bulk insert the data from.What i wanted was to allow the user to define the path of the file and i wanted to pass this path as a parameter .In the example u have given its seems to me the same thing.May be if you could explain me more as i am a newbie.Sorry for the inconvienience.|||also i did try this code-:
@.loc nvarchar (50)
Bulk Insert Tablename From '''@.loc'' with(fieldterminator='','')
but i get an error while saving this stored procedure which says
"Cant find @.loc"|||post ur stored procedure|||Even here u are giving the static location
No, I am using a variable. Here are some code for you to play with
Note: UNC path used for the load as the server is not running on my local PC.
C:\tmp>echo aaa,bbb >tmp.txt

C:\tmp>more tmp.txt
aaa,bbb

C:\tmp>"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\isql" -U sa -S devdb
Password:
1> use tempdb
2> go
1> create proc pdreyer_load
2> @.filename varchar(255)
3> as
4> exec ('bulk insert #t1 from '''+@.filename+''' with(fieldterminator='','')')
5> go
1> create table #t1 (f1 varchar(10),f2 varchar(10))
2> exec pdreyer_load '\\pdreyer\tmp\tmp.txt'
3> select * from #t1
4> go
f1 f2
---- ----
aaa bbb

(1 row affected)
1> drop table #t1
2> drop procedure pdreyer_load
3> go
1> exit

C:\tmp>del tmp.txt|||Is the source file changed all the time?

BULK INSERT permissions for non-admin?

I've got a user, "joe", logging into a SqlServer 2005 database using
sqlserver authentication. Joe can create tables, insert, select, drop --
whatever -- but when he tries to do a bulk insert, he gets:
"The current user is not the database or object owner of table 'myTable'.
Cannot perform SET operation."
WARNING: I don't know much about SQL Server permissions administration. I
write queries. :)
When I look at the database in SQL Server Management Studio, I see that the
table is in the schema "dbo". What's the safest way to give Joe permission
to do a bulk insert into this table?
Thanks!"Jesse" <nospam@.nospam.com> wrote in message
news:OI6lteXZGHA.3848@.TK2MSFTNGP05.phx.gbl...
> I've got a user, "joe", logging into a SqlServer 2005 database using
> sqlserver authentication. Joe can create tables, insert, select, drop --
> whatever -- but when he tries to do a bulk insert, he gets:
> "The current user is not the database or object owner of table 'myTable'.
> Cannot perform SET operation."
> WARNING: I don't know much about SQL Server permissions administration. I
> write queries. :)
> When I look at the database in SQL Server Management Studio, I see that
> the table is in the schema "dbo". What's the safest way to give Joe
> permission to do a bulk insert into this table?
>
The user needs INSERT and ADMINISTER BULK OPERATIONS permissions and perhaps
ALTER TABLE. In addition if the user is connected using SQL Server
authentication then the SQL Server account must be able to read the file.
If the user is authenticated by Windows Integrated Authentication, then the
user, not the SQL Server account, needs to be able to read the file.
It's all in the BOL:
BULK INSERT (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188365(SQL.90).aspx
Security Considerations for Using Transact-SQL to Bulk Import Data
http://msdn2.microsoft.com/en-us/library/ms186286.aspx
David|||David --
Thanks for the quick reply! Unfortunately, I must still be missing
something -- I've gone through and granted everything I can think of, but I
still get the same error.
Specifically, here's what I did:
grant alter to [joe]
grant ADMINISTER BULK OPERATIONS to [joe]
grant insert to [joe]
Then, from Server Management Studio,
add the bulkadmin role to joe
add the symin role to joe
add the db_ddladmin role to joe
add the db_owner role to joe
Joe now has the power to destroy the whole database -- but not to bulk
insert. I still get, "The current user is not the database or object owner
of table 'myTable'. Cannot perform SET operation."
What else could I be missing?
Thanks again for your help!
Jesse
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OusnToXZGHA.428@.TK2MSFTNGP02.phx.gbl...
> "Jesse" <nospam@.nospam.com> wrote in message
> news:OI6lteXZGHA.3848@.TK2MSFTNGP05.phx.gbl...
>
> The user needs INSERT and ADMINISTER BULK OPERATIONS permissions and
> perhaps ALTER TABLE. In addition if the user is connected using SQL
> Server authentication then the SQL Server account must be able to read the
> file. If the user is authenticated by Windows Integrated Authentication,
> then the user, not the SQL Server account, needs to be able to read the
> file.
> It's all in the BOL:
>
> BULK INSERT (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms188365(SQL.90).aspx
>
> Security Considerations for Using Transact-SQL to Bulk Import Data
> http://msdn2.microsoft.com/en-us/library/ms186286.aspx
>
>
> David
>
>|||One more detail:
- if I log into the database using Server Management Studio, I can run the
bulk insert OK.
- if I try to run it from an ASP.NET script, I get the permissions error.
The asp.net script is connecting to the database with the same credentials I
use to log in via SMS! Something must be working differently, but what?
This is driving me nuts. Thanks for any help you can give!
Here's how my asp.net script connects:
<add name="production" connectionString="data source=myLocalMachine; initial
catalog=myCatalog; user=joe; password=joe;"
providerName="System.Data.SqlClient"/>
"Jesse" <nospam@.nospam.com> wrote in message
news:e8jp30XZGHA.4884@.TK2MSFTNGP02.phx.gbl...
> David --
> Thanks for the quick reply! Unfortunately, I must still be missing
> something -- I've gone through and granted everything I can think of, but
> I still get the same error.
> Specifically, here's what I did:
> grant alter to [joe]
> grant ADMINISTER BULK OPERATIONS to [joe]
> grant insert to [joe]
> Then, from Server Management Studio,
> add the bulkadmin role to joe
> add the symin role to joe
> add the db_ddladmin role to joe
> add the db_owner role to joe
> Joe now has the power to destroy the whole database -- but not to bulk
> insert. I still get, "The current user is not the database or object owner
> of table 'myTable'. Cannot perform SET operation."
> What else could I be missing?
> Thanks again for your help!
>
> Jesse|||"Jesse" <nospam@.nospam.com> wrote in message
news:eiNZPSYZGHA.4884@.TK2MSFTNGP02.phx.gbl...
> One more detail:
> - if I log into the database using Server Management Studio, I can run the
> bulk insert OK.
> - if I try to run it from an ASP.NET script, I get the permissions error.
> The asp.net script is connecting to the database with the same credentials
> I use to log in via SMS! Something must be working differently, but what?
> This is driving me nuts. Thanks for any help you can give!
>
> Here's how my asp.net script connects:
> <add name="production" connectionString="data source=myLocalMachine;
> initial catalog=myCatalog; user=joe; password=joe;"
> providerName="System.Data.SqlClient"/>
>
Strange.
This works for me.
Run this as a symin. I have a database called test and xp_cmdshell
enabled.
You might run Profiler to see what's actually being sent from the web site.
David
--setup.sql--
use test
master..xp_cmdshell 'dir > c:\data\foo.txt'
master..xp_cmdshell 'type c:\data\foo.txt'
create table LoadTable(data varchar(4000))
bulk insert LoadTable from 'c:\data\foo.txt'
select * from LoadTable
truncate table LoadTable
go
use master
create login Joe with password='Joe'
grant administer bulk operations to Joe
go
use test
create user Joe for login Joe
grant view definition on schema::dbo to Joe
grant select,insert,delete on LoadTable to Joe
--end setup.sql--
then run this .NET program
--Program.cs--
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Data.SqlClient;
namespace csTest
{
public static class Program
{
public static void Main()
{
SqlConnectionStringBuilder cb1 = new SqlConnectionStringBuilder();
cb1.DataSource = "192.168.2.10";
cb1.IntegratedSecurity = false;
cb1.UserID = "Joe";
cb1.Password = "Joe";
cb1.InitialCatalog = "Test";
using (SqlConnection con = new SqlConnection(cb1.ConnectionString))
{
con.Open();
con.FireInfoMessageEventOnUserErrors = true;
con.InfoMessage += new SqlInfoMessageEventHandler(con_InfoMessa
ge);
new SqlCommand(@."delete from LoadTable", con).ExecuteNonQuery();
new SqlCommand(@."bulk insert LoadTable from
'c:\data\foo.txt'",con).ExecuteNonQuery();
using (SqlDataReader rdr = new SqlCommand("select * from
LoadTable",con).ExecuteReader())
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
}
}
Console.ReadLine();
}
static void con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
Console.WriteLine(e.Message);
}
}
}
--end Program.cs---

Bulk insert in SSIS

Has anyone else had this problem?

I am using 'OLE DB Destination' task in a data flow. When I select 'Table or view - fast load', check 'Table lock' and 'Check constraints' and run the flow, I get only one row inserted. The data flow edge shows hundreds of thousands of rows flowing to destination and the task completes (turns green).

The problem goes away if I uncheck bulk load (e.g., select 'Table or view').

Is fast load using bulk insert or some internal code?

?

That is weird. Have you tried to set a value for 'Maximux insert commit size'?

As far as I know SSIS uses bulk inserts when fast load is selected.

Use SQL Server profiler to see the databse activity while the package is being run and insepect the results of the progress tab during the debug session...

Other than that; no idea

Rafael Salas

BULK INSERT HELP

Hi

I am trying to do the following

DECLARE @.hdoc int

DECLARE @.doc varchar(max)

DECLARE @.FilePath varchar(50)

SELECT @.FilePath = 'C:/Test/Test.log'

SELECT

@.doc = c from OpenRowset (BULK + @.Filepath + , SINGLE_BLOB) as T (c)

I get the following error

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near '@.Filepath'.

I am sure this is really basic.Can some one let me know what am i doing wrong

Thanks

You will have to use dynamic sql.

declare @.doc varbinary(max)

declare @.FilePath nvarchar(50)

declare @.sql nvarchar(max)

set @.FilePath = N'C:\Test\Test.log'

set @.sql = N'select @.doc = c from openrowset(bulk ''' + @.FilePath + N''', SINGLE_BLOB) as T(c)'

exec sp_executesql @.sql, N'@.doc varbinary(max) output', @.doc

go

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB

|||

Thanks for the reply

Cna i do something like :

declare @.doc varbinary(max)

declare @.FilePath nvarchar(50)

declare @.sql nvarchar(max)

declare @.hdoc int

set @.FilePath = N'C:\Test\Test.log'

set @.sql = N'select @.doc = c from openrowset(bulk ''' + @.FilePath + N''', SINGLE_BLOB) as T(c)'

exec sp_executesql @.sql, N'@.doc varbinary(max) output', @.doc

exec sp_xml_preparedocument @.hdoc OUTPUT,@.doc

Thanks

|||

Use the new XML datatype, If you use XML datatype, you need not to use the sp_xmlpreparedocuement(which uses more expensive resources & legecy com objects).

See XQuery on BOL to understand how to work with new XML data typed values. if you failed to use XML datatype / XML casting you only get xml content as binary..

Code Snippet

declare @.doc XML

declare @.FilePath nvarchar(50)

declare @.sql nvarchar(max)

declare @.hdoc int

set @.FilePath = N'C:\Test\Test.log'

set @.sql = N'select @.doc = convert(xml,c) from openrowset(bulk ''' + @.FilePath + N''', SINGLE_BLOB) as T(c)'

exec sp_executesql @.sql , N'@.doc XML OUTPUT', @.doc OUTPUT

Select @.doc

|||

DECLARE @.hdoc int

DECLARE @.doc varchar(max)

--DECLARE @.doca varchar(max)

DECLARE @.SQL nvarchar(200)

DECLARE @.Filepath varchar(50)

SET @.Filepath = 'C:\XML_Processing\Test.LOG'

SET @.SQL = N'select @.doc = c from OpenRowset(BULK''' + @.Filepath + N''', SINGLE_CLOB) as T(c)'

exec

sp_executesql @.SQL, N'@.doc varchar(max) output' ,@.doc

SELECT @.doc

print @.doc

print @.SQL

I was expecting @.doc to have the whole XML from Test,LOG but it is returning me NULL

Any ideas whats going on?

Thanks

Hemanshu

Bulk Grant Select failing

Hi there
I'm still finding my way in SQL server so the problem might be very simple (hopefully...).
Would anybody have any idea why:

grant select on table1 to ReadGroup

works fine, and

grant create table to ReadGroup

works fine, yet

grant select to ReadGroup

results in
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'to'.?

Any help would be immeasurably appreciated
Cheers!Hi
I think you need to specify what table they are being granted select ON...
ie.
GRANT SELECT
ON table
TO user
GO

add them to db_datareader role if you want database wide select ...
des|||if u are looking for granting select permissions to all the user tables
then :

declare @.table varchar(100)
select [name] into #temp from sysobjects where xtype='u'
while exists (select * from #temp)
begin
select top 1 @.table=[name] from #temp
exec ('Grant select on '+@.table+' to ReadGroup')
delete from #temp where [name]=@.Table

end
drop table #temp|||Hi

Thanks for this - Yep, wanted to set permissions to over 300 tbales in one go. Wondered if the failure was due to not specifying object types but wasn't sure how to - thanks for your help guys

Cheers

Bulk Delete

we are trying to delete data from a huge 75 million records table
it takes 4hr to prune data

delete from Company where recordid in (select top 10000 recordid from
recordid_Fed3 where flag = 0)

we have a loop that prunes 10000 records at a time in a while loop
let me know if there is a better way to acheive thisIf you just want to delete *every* row in the table you can use
TRUNCATE TABLE :

<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ta-tz_2hk5.asp
If you want to selectively delete rows, that won't work, though. If
you want to delete most of the rows (but not all of them) you could
insert the ones you want to keep into some other (temporary) table,
truncate the main table, then insert the rows back.|||kumar (svengala@.gmail.com) writes:
> we are trying to delete data from a huge 75 million records table
> it takes 4hr to prune data
> delete from Company where recordid in (select top 10000 recordid from
> recordid_Fed3 where flag = 0)
> we have a loop that prunes 10000 records at a time in a while loop
> let me know if there is a better way to acheive this

Rather than using SELECT TOP, try use a condition that matches the clustered
index and slice that up in intervals. Assume that the clustered index is
on recordid, and that this is an integer you would do:

SELECT @.recordid = MIN(recordid) FROM Company (WHERE flag = 0),
@.increment = 100000
WHILE EXISTS (SELECT * FROM Company WHERE recordid = @.recordid)
BEGIN
DELETE Company
WHERE recordid BETWEEN @.recordid AND @.recordid + @.increment - 1
AND flag = 0
SELECT @.recordid = @.recordid + @.increment
END

In this way you are only scanning the table once for rows to delete.

If you anticipate that you will delete more rows than you will retain,
you could create a new table, and insert the rows to keep. In this case
you need to make sure that you also bring with you constraints, indexes,
and triggers, and you will have to move referencing foreign keys. The
insert can be further speedied up by using SELECT INTO, but SELECT INTO
may not give you a faithful copy of the table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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