Receiving errors when using foreach loop and excel connection manager...

Purpose: Need to import excel source data into SQL Server 2005 tables. Excel source data comes in nulitple excel files with the same structure but different data. I would appreciate someone taking a look at the following information and notifying me of what I am doing incorrectly.

I Inserted a foreach loop container, a data flow task located inside the foreach loop contaiiner, an excel and SQL Server 2005 connections.

After trying multiple times I went the following URL and followed step by step direction on how to connect excel workbooks dynamically: http://msdn2.microsoft.com/en-us/library/ms345182.aspx . I also used http://www.sqlstrings.com/ as a reference when creating the connection string.

Creating a Foreach Loop Container:

1. Opened foreach loop container 2.Set the Enumerator to 'Foreach File Enumerator" and configured the enumerator by setting the directory location and file base name to E:\Clients\Dep Comm\BEA\BEA_Test_Source and *PersonnelExpense*.xls respectively. 3. Clicked Variable Mapping; created two variables called, "ExcelFile", and "ExtProperties" and closed out of the foreach loop container.

I. Created Excel Connection:

1. Created excel connection called, “Dynamic Excel Connection Manager,” that initially pointed to one of the excel workbooks.
2. Went to the connection properties by right clicking the connection manager.
3. Expanded Expressions and clicked the ellipsis button to bring up property expressions
4. Chose Connection String in the Property.
5. Clicked the Expression Ellipsis button.
6. Put the following inside the Expression multi line text box:

A. "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +@.[User::ExcelFile] + ";Extended Properties=\"" + @.[User::ExtProperties] + "\""

7. Clicked the Evaluate Expression button to get the following:
   1. Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties=""
8. Clicked Ok button
9. Inserted a Data flow task inside the foreach loop container.

II. Configured Tasks that is associated with Dynamic Excel Connection Manager or Package:

1. Set the Foreach loop container Delay Validation to true.
2. Set the Data Flow Task Container Delay Validation to true.
3. Set the Dynamic Excel Connection Manager Delay Validation to true.
4. Set the SQL Server Connection Manager Delay Validation to true.
5. Set the Package Delay Validation to true.
6. Package Locale ID set to English

Ran the package after connecting the excel source data flow to the OLEDB destination and have inserted part of the error in this post. Please see below.

Error: 0xC0202009 at Package, Connection manager "Dynamic Excel Connection Manager": An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.Source: "Microsoft JET Database Engine"Hresult: 0x80004005Description: "Could not find installable ISAM.".

I modified the connection string after receiving the error by removing the extended properties. The following is the modified connection string: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +@.[User::ExcelFile]

I repeated step I.6 above and received the following expression: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=

I ran the package and received the following error in part: OLE DB record is available.Source: "Microsoft JET Database Engine"Hresult: 0x80004005Description: "Unrecognized database format 'E:\Clients\Dep Comm\BEA\BEA_Test_Source\PersonnelExpense_OCCs_051007.xls'."

I did not find anything helpful when I searched for the above errors and would very much appreciate anyone’s assistance on this issue as this issue needs to be taken care of ASAP.

Does anyone have any ideas as to why I received this error and what can I do to resolve this issue?

Your assistance in this matter is truly appreicated!

Thanks!!

Lee

Are there headers on the Excel spreadsheets?

The error is looking for a database format where the headers are matched as column headers.

If there are no headers, it doesnt' know how to match it.

Just my twist on it,

Adamus

|||

Hi Adamus,

I appreciate your feedback. To answer your question, the first row does contain column headers.The data flow task has an excel source file which uses the Dynamic Excel Connection manager that I mentioned in my original post. The excel source file connects to a derived column data task that creates derived columns and changes the data type to reflect that of the destination. The derived columns is then connected to the SQL Server destinationation table that matches the excel header columns to the destination table columns.

What should I do with the connection string that I mentioned in my first post? I have provided it here for you or anyone else to review.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +@.[User::ExcelFile] + ";Extended Properties=\"" + @.[User::ExtProperties] + "\""

Can you or anyone else tell me what I am doing wrong here?

Thanks!!

Lee

|||

Can you actually import at least one single file? forget about the foreach loop and the expression in the connection manager.

I just did a search on 'Could not find installable ISAM ' and found this:

http://support.microsoft.com/kb/209805

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=477902&SiteID=1

So, before going deeper, try to connect to a single execel file.

BTW, I just found a test package I created long time ago, and I see some differences with your approach:

I have only one variable in my for each loop container to hold the file name; and since I configured the loop container to retrive 'Fully Qualified" file name; the variable contains the path and the file name. In the excel connection manager I applied an expression to the ExcelFilePath property (not to the Connection String). The expression is just: @.{User::VarFileName] ; which is the variable I use in the loop container. I don't mess with the extended properties.sql

Rebuildm failed with Error 266

I am trying to run rebuildm to change the collation of SQL. I stop SQL and
even have deleted sqlmangr.exe out of Task Manager "Processes". I have
everything closed on the machine. SQL is definitely down - No services show
in "Services". However, when I run rebuildm - after I enter where the data
files are and the new collation and press Rebuild it returns an error. First
One "Cannot copy master: It is being used by another person or program.
Close any programs that might be using the file and try again" When I press
OK I get "Rebuild Master failed with error 266: The copy function cannot be
used. The error occurred due to a file copy failure"
How can there be a connection? - SQL is stopped! No one can connect - the
server isn't even connected to the network. I've got everything closed!
Can you please help me - This is driving me crazy -- Thanx!
Terry
Regina Qu'appelle Health Region
Saskatchewan
CANADA
Try copying the files to disk first and remove the read only
attribute. That's generally what causes the error you are
seeing.
-Sue
On Fri, 30 Sep 2005 15:15:03 -0700, "Terry"
<terry@.microsoft.newsgroups.com> wrote:

>I am trying to run rebuildm to change the collation of SQL. I stop SQL and
>even have deleted sqlmangr.exe out of Task Manager "Processes". I have
>everything closed on the machine. SQL is definitely down - No services show
>in "Services". However, when I run rebuildm - after I enter where the data
>files are and the new collation and press Rebuild it returns an error. First
>One "Cannot copy master: It is being used by another person or program.
>Close any programs that might be using the file and try again" When I press
>OK I get "Rebuild Master failed with error 266: The copy function cannot be
>used. The error occurred due to a file copy failure"
>How can there be a connection? - SQL is stopped! No one can connect - the
>server isn't even connected to the network. I've got everything closed!
>Can you please help me - This is driving me crazy -- Thanx!
|||thanks! - Tried it but...
My db files and rebuildm are on disk
The actual directory tree will not let me remove the RedOnly on the Data dir
Windows Server 2003 doesn't want me to remove it - Apply it but it just
reverts back to Read Only -- tried all the way up the dir structure.
I went ahead anyway and created another branch for the db files and copied
them to it.
I ran rebuildm - it went further but came back with a different but similar
error
"Rebuild Master failed with error -1: the error occurred due to a file copy
failure"
I didn't actually attach the new set of dbs to SQL though ?
Thanx!
Terry
Regina Health Region
Saskatchewan
CANADA
"Sue Hoegemeier" wrote:

> Try copying the files to disk first and remove the read only
> attribute. That's generally what causes the error you are
> seeing.
> -Sue
> On Fri, 30 Sep 2005 15:15:03 -0700, "Terry"
> <terry@.microsoft.newsgroups.com> wrote:
>
>
|||I'm not clear on what you moved where but I doubt it will
work if you split things apart.
Copy the entire x86\Data directory. Select all of the files
in the data directory and remove the read-only attribute.
Then run rebuildm. Use this other data directory as the
source directory for the data files.
-Sue
On Mon, 3 Oct 2005 10:36:04 -0700, "Terry"
<terry@.microsoft.newsgroups.com> wrote:

>thanks! - Tried it but...
>My db files and rebuildm are on disk
>The actual directory tree will not let me remove the RedOnly on the Data dir
>Windows Server 2003 doesn't want me to remove it - Apply it but it just
>reverts back to Read Only -- tried all the way up the dir structure.
>I went ahead anyway and created another branch for the db files and copied
>them to it.
>I ran rebuildm - it went further but came back with a different but similar
>error
>"Rebuild Master failed with error -1: the error occurred due to a file copy
>failure"
>I didn't actually attach the new set of dbs to SQL though ?
>Thanx!
|||We always install SQL onto another drive - usually D:
So a typical install puts files/dirs on both C: and D:
.../Data exists on D: that's what I copied to another directory
and used as a source for Rebuildm
data files are always in d:\Program Files\Microsoft SQL Server\MSSQL\Data
I took those files and copied them to D:\MSSQL\Data which is a new dir
I must be confused about something basic !?!?!
Is my problem due to installing SQL on D: ?
SQL 2000 - SP4 ? Windows Server 2003 ?
Rebuildm is on C:\...
Thanks!
Terry
Regina Health Region
Saskatchewan
CANADA
"Sue Hoegemeier" wrote:

> I'm not clear on what you moved where but I doubt it will
> work if you split things apart.
> Copy the entire x86\Data directory. Select all of the files
> in the data directory and remove the read-only attribute.
> Then run rebuildm. Use this other data directory as the
> source directory for the data files.
> -Sue
> On Mon, 3 Oct 2005 10:36:04 -0700, "Terry"
> <terry@.microsoft.newsgroups.com> wrote:
>
>
|||Yes...using rebuildm has nothing at all to do with whatever
data files you currently have. Ignore whatever you have and
where ever they are located for the rebuildm utility. Just
look at this as having nothing to do with whatever you
already installed.
Copy the entire x86\Data directory from your SQL Server
installation CD to a share. You need all of the original
files from the installation CD.
Select all of the files in the data directory that you
copied from the CD. Right click, select properties and
remove the read-only attribute.
Then run rebuildm. Use this data directory share where you
have just copied the files from the CD as the source
directory for the data files.
It sounds like you are trying to use some of your database
files and that's now how it works. The rebuildm utility
rebuilds the master database and system databases. It the
original files for these database from the CD. Rebuildm gets
you back up and running but you still need to restore you
master, model and msdb databases.
-Sue
On Tue, 4 Oct 2005 08:16:01 -0700, "Terry"
<terry@.microsoft.newsgroups.com> wrote:

>We always install SQL onto another drive - usually D:
>So a typical install puts files/dirs on both C: and D:
>.../Data exists on D: that's what I copied to another directory
>and used as a source for Rebuildm
>data files are always in d:\Program Files\Microsoft SQL Server\MSSQL\Data
>I took those files and copied them to D:\MSSQL\Data which is a new dir
>I must be confused about something basic !?!?!
>Is my problem due to installing SQL on D: ?
>SQL 2000 - SP4 ? Windows Server 2003 ?
>Rebuildm is on C:\...
>Thanks!
|||Thanks Sue!!! -- that's it !!! -- I wasn't understanding the intent of the
utility. I thought it wanted the location of the existing system files.
Terry
Regina Health Region
Saskatchewan
CANADA
"Sue Hoegemeier" wrote:

> Yes...using rebuildm has nothing at all to do with whatever
> data files you currently have. Ignore whatever you have and
> where ever they are located for the rebuildm utility. Just
> look at this as having nothing to do with whatever you
> already installed.
> Copy the entire x86\Data directory from your SQL Server
> installation CD to a share. You need all of the original
> files from the installation CD.
> Select all of the files in the data directory that you
> copied from the CD. Right click, select properties and
> remove the read-only attribute.
> Then run rebuildm. Use this data directory share where you
> have just copied the files from the CD as the source
> directory for the data files.
> It sounds like you are trying to use some of your database
> files and that's now how it works. The rebuildm utility
> rebuilds the master database and system databases. It the
> original files for these database from the CD. Rebuildm gets
> you back up and running but you still need to restore you
> master, model and msdb databases.
> -Sue
> On Tue, 4 Oct 2005 08:16:01 -0700, "Terry"
> <terry@.microsoft.newsgroups.com> wrote:
>
>
|||Glad to hear you got it. Your not the only one who I've seen
have problems with the same issue FWIW...
-Sue
On Wed, 5 Oct 2005 08:16:01 -0700, "Terry"
<terry@.microsoft.newsgroups.com> wrote:

>Thanks Sue!!! -- that's it !!! -- I wasn't understanding the intent of the
>utility. I thought it wanted the location of the existing system files.

rebuild the replication

Easiest way is to script out replication on the publisher
using enterprise manager, modify the server names (change
Server X to Server Y and vice-versa) and keep this script
for failover. Once server X goes down, you apply the
script on Server Y.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Is there a way that I can rebuild the database in such a way that it becomes
original publisher and the subscriber can then push all changes been made so
far to it to publisher?
Here is what I want to do. Tell me if I 'm thinking wrong.
Server X is publisher and server Y is subscriber with merge replication set.
Now when X is up and Y is standby then all transactions are done at X and
been replicated to Y.
Now let's say X goes down by any reason (Fault or Disaster).
So I make Y as the main server for all user connections and transactions. I
still don't change the replication and keep it as subscriber.
Now how should I rebuild X so that I can make it publisher the way it was
and when I establish the connection, it merges all changes been made at
subscriber and take over again as publisher?
Is this something doable ?
Should we do this or not?
Please advice.
Sam
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1faf01c51bed$77819fb0$a601280a@.phx.gbl...
> Easiest way is to script out replication on the publisher
> using enterprise manager, modify the server names (change
> Server X to Server Y and vice-versa) and keep this script
> for failover. Once server X goes down, you apply the
> script on Server Y.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

re-attach database to another server

I have detached a database, say AB, from one SQL server 2000. Then attach it
in another server 2000 by using enterprise manager. But it is reattached as
read-only. So I can not make any change. There is no such a peoblem for sql
server 2005.
How to solve this problem?
Thank you for any help
David
Solved it by reading the previous posts.
Thanks
This is a good web site.
David
"david" wrote:

> I have detached a database, say AB, from one SQL server 2000. Then attach it
> in another server 2000 by using enterprise manager. But it is reattached as
> read-only. So I can not make any change. There is no such a peoblem for sql
> server 2005.
> How to solve this problem?
> Thank you for any help
> David

re-attach database to another server

I have detached a database, say AB, from one SQL server 2000. Then attach it
in another server 2000 by using enterprise manager. But it is reattached as
read-only. So I can not make any change. There is no such a peoblem for sql
server 2005.
How to solve this problem?
Thank you for any help
DavidSolved it by reading the previous posts.
Thanks
This is a good web site.
David
"david" wrote:

> I have detached a database, say AB, from one SQL server 2000. Then attach
it
> in another server 2000 by using enterprise manager. But it is reattached a
s
> read-only. So I can not make any change. There is no such a peoblem for sq
l
> server 2005.
> How to solve this problem?
> Thank you for any help
> David

re-attach database to another server

I have detached a database, say AB, from one SQL server 2000. Then attach it
in another server 2000 by using enterprise manager. But it is reattached as
read-only. So I can not make any change. There is no such a peoblem for sql
server 2005.
How to solve this problem?
Thank you for any help
DavidSolved it by reading the previous posts.
Thanks
This is a good web site.
David
"david" wrote:
> I have detached a database, say AB, from one SQL server 2000. Then attach it
> in another server 2000 by using enterprise manager. But it is reattached as
> read-only. So I can not make any change. There is no such a peoblem for sql
> server 2005.
> How to solve this problem?
> Thank you for any help
> David

Really need help w/ connection manager problem in SSIS

Hello,

I am attempting to set up a Connection Manager for a Flat File Source.

Data in my flat file is like this, for example:

"038188306","03/02/2007"
"038C88328","9A9990846","INFY-SW ","L","Z98ZVL97R","2006-06-02"

Row 1 has two columns, Row 2 has six columns (as delimited by the double quotes).

Format: Delimited

Text Qualifer: {""}

Header Row Delimiter: {CR}{LF}

But when I click on the Columns button, it only shows 2 columns. For example, it shows the first row correctly, but Row 2 shows as:

Column 1

"038C88328"

Column 2

"9A9990846","INFY-SW ","L","Z98ZVL97R","2006-06-02"

which is NOT what I want.

I really need help here.

THANKS

Nevermind, figured it out :-)

Need to skip first row

|||

Hey K108,

I just wanted to know how did yo figured ti out.

Thanks,

Vikt

|||The answer is, all the rows in the text file must have the exact same number of columns. In my text file, the first row is garbage data with only 2 columns. So I just needed to select the option to skip 1 row.

Really need help w/ connection manager problem in SSIS

Hello,

I am attempting to set up a Connection Manager for a Flat File Source.

Data in my flat file is like this, for example:

"038188306","03/02/2007"
"038C88328","9A9990846","INFY-SW ","L","Z98ZVL97R","2006-06-02"

Row 1 has two columns, Row 2 has six columns (as delimited by the double quotes).

Format: Delimited

Text Qualifer: {""}

Header Row Delimiter: {CR}{LF}

But when I click on the Columns button, it only shows 2 columns. For example, it shows the first row correctly, but Row 2 shows as:

Column 1

"038C88328"

Column 2

"9A9990846","INFY-SW ","L","Z98ZVL97R","2006-06-02"

which is NOT what I want.

I really need help here.

THANKS

Nevermind, figured it out :-)

Need to skip first row

|||

Hey K108,

I just wanted to know how did yo figured ti out.

Thanks,

Vikt

|||The answer is, all the rows in the text file must have the exact same number of columns. In my text file, the first row is garbage data with only 2 columns. So I just needed to select the option to skip 1 row.