Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lols
Posting Yak Master
174 Posts |
 Posted - 2007-10-30 : 09:48:35
|
| Hello,I am doing a SSIS package to transfer rows from access db to sql server using foreach file. Before loading the data, I want to count the number of rows in a table of the access db. How do I do that?thank you in advance |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-30 : 09:59:55
|
| You could probably use OPENQUERY or OPENROWSET to run a query (e.g. "SELECT COUNT(*) FROM MyAccessTable") against the Access databaseKristen |
 |
|
|
lols
Posting Yak Master
174 Posts |
Posted - 2007-10-30 : 10:57:19
|
| I tried this :SELECT count(*) FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\northwind.mdb';'user';'pass', Customers)GOSince I am using the foreach file, i am using a variable to get the filename of the access db. How do i pass a variable to the OPENROWSET command instead of the hardcoded mdb path?thanks for the help |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-30 : 11:27:35
|
I think this will be more efficient (your way will have to pass all columns of all rows in the table between Access and SQL Server, and then make the Count )SELECT MyCount FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\northwind.mdb';'user';'pass', 'SELECT COUNT(*) AS MyCount FROM Customers')To make that "dynamic" you need something likeDECLARE @strSQL varchar(8000)SELECT @strSQL = 'SELECT MyCount FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''' + @MyTableName + ''';''user'';''pass'', ''SELECT COUNT(*) AS MyCount FROM Customers'')'PRINT @strSQL -- Debug use to check that the SQL is sensibleEXEC (@strSQL)Kristen |
|
|
|
lols
Posting Yak Master
174 Posts |
Posted - 2007-10-30 : 12:59:51
|
| you have been very helpful :) I will check it out |
|
|
|
|
|
|
Count rows in access db