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