FeedFile Excel Connection Manager Concurrency Issue?

Hi,

RE. SSIS (v9.00.1399.00) FeedFile Excel Connection Manager Concurrency Issue

1. I have 15 completely separate SSIS Packages all of which use the FeedFile Excel Connection Manager to read data from completely separate Excel worksheets.

2. When I invoke, say, 5 of these Packages at the same time then one or more (at random) of them fail with the following error message:-

The AcquireConnection method call to the connection manager "FeedFile Excel Connection Manager" failed with error code 0xC0202009.

3. When I invoke the failed Packages again then they succeed.

So, do you know if there is:-

(a) a concurrency issue with the "FeedFile Excel Connection Manager" ?

(b) a read-only option (in the SSIS Package code) which needs to be specified to overcome file contention issues?

Additional Information about the FeedFile Excel Connection Manager:-

Connection String=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DoesNotNeedToExist.xls;Extended Properties="EXCEL 8.0;HDR=NO";

DelayValidation=True

ExcelFilePath= C:\DoesNotNeedToExist

RetainSameConnection=False

Note1. The actual Excel filename which is read is generated dynamically at run-time by a Script Task. This is why DelayValidation has been set to: True (Note. The file: "C:\DoesNotNeedToExist" does not actually physically exist.)

Note2. We have another SSIS Package which uses the FeedFile FlatFile Connection Manager. When we run this same Package concurrently (i.e. invoke it 5 times, at the same time, with different input files) then all 5 invocations succeed, which is what we would expect.

Thanks.

What is the "FeedFile Excel Connection Manager" as opposed to the regular Excel connection?

Is there no more detail? If running in BIDS, check the output winbdow, or if running unattanded, turn on logging.

I would expect there to be some additional error messages that go with 0xC0202009 error, which give the reason for this generic failure report.

|||

Sorry Darren, ignore the prefix FeedFile, it is the regular Excel connection.

I have not had a chance to look at this problem again but I will return to it later this week. I will let you know how I get on with trying to reproduce it in BIDevStudio etc.

From what I can gather though, there should be no limit to the number of separate SSIS Packages that can be run concurrently on a Server. Is this correct?

Fatal Error 7105 ... text, ntext, or image node does not exist

I'm getting the following in error in Enterprise Manager:
Fatal Error 7105 ... text, ntext, or image node does not exist
It seems that a row in a table has become corrupt or something because when
I try to query for that row or scroll down in the table to view the row (the
preceding rows display fine), that is when I get this error. Can a row that
causes this error be salvaged or deleted or something? It seems it is this
one row that is causing the problem. Is that right?
I found this KB article that suggests applying a hotfix.
http://support.microsoft.com/kb/890755
The hotfix seems to have come out between SP 3a & SP 4. I already have
Service Pack 4 installed, so I'm wondering if this proposed hotfix is already
part of SP 4 or if I should try to apply the hotfix?
I don't want to mess up my box, but I need this error resolved.
If anyone can offer furter advice, I would appreciated (and then I can get a
broken app back up and running).
ThanksI have a correction to my post.
I'm getting the following error in Enterprise Manager:
[Microsoft][ODBC SQL Server Driver][SQL Server]Page (1:6890), slot 35 for
text, ntext, or image node does not exist.
In my web app, the error is:
Fatal Error 7105 ... text, ntext, or image node does not exist

Fastest way to alter table structure

I'm writing a database table designer (works like the Enterprise Manager one, but with restricted datatypes and some checks), and I have a bit of a question:

To alter the structure of a table, I'm currently using the same method as Enterprise Manager (create a temporary table with the new schema, copy the data across, drop the original table, rename the temporary table).

When altering the structure of a table with a large number of rows, but not changing the order of columns or adding columns in the middle of the table:
Would it be faster to execute multiple ALTER TABLE statements? If so, at what point would it become slower? (Say, changing lots of column data types, etc).Thanks

Probably always faster using ALTER TABLE.

I really doubt that ALTER TABLE statements would be noticably slower.

|||Thanks.

I was worried that if there were a lot of column data type changes there could be a lot of conversions, and a lot of overhead from going through the entire table multiple times.
|||I guess that could happen -depends upon the quantity and type of changes.

Failure to start up SQL Server

SQL fails to start up - no error message is given- in SQL Server Service
Manager or in Enterprise Manager
The problem first occurred when starting up my computer and SQL failed to
start automatically (Auto start service when OS starts check box is ticked)Leonard wrote:
> SQL fails to start up - no error message is given- in SQL Server Service
> Manager or in Enterprise Manager
> The problem first occurred when starting up my computer and SQL failed to
> start automatically (Auto start service when OS starts check box is ticked)
and the error message is:'?|||Have alook in the event log of WIndows to see where the problem is.
Jens Suessmeyer,
"Leonard" <Leonard@.discussions.microsoft.com> schrieb im Newsbeitrag
news:8664BBA4-F8CC-417C-B732-B4E9DEE008C9@.microsoft.com...
> SQL fails to start up - no error message is given- in SQL Server Service
> Manager or in Enterprise Manager
> The problem first occurred when starting up my computer and SQL failed to
> start automatically (Auto start service when OS starts check box is
> ticked)|||Check SQL errorlogs as well.
Adrian
"Leonard" <Leonard@.discussions.microsoft.com> wrote in message
news:8664BBA4-F8CC-417C-B732-B4E9DEE008C9@.microsoft.com...
> SQL fails to start up - no error message is given- in SQL Server Service
> Manager or in Enterprise Manager
> The problem first occurred when starting up my computer and SQL failed to
> start automatically (Auto start service when OS starts check box is
> ticked)|||I too have this problem. There is no error message. After installing sp4,
the system reboots and starts the Service Manager but it doesn't
automatically load my server\instance name. I have to do that manually.
Event viewer logs do not show any errors.
Have you found a solution yet?

Failure to Login

I have installed a brand new msde2000, in order to get SQL
Service Manager. The SQL Service Mgr is installed, and it
shows up in my tray with the little green arrow. I
installed the msde following the instructions in the MS
Knowledge base article 319930, How to Connect to MSDE. I
installed it with the SECURITYMODE=SQL parameter. I used
the osql command to give it a new password. So far so
good. Now, following 319930, I tried a connectivity
test. I get the following error message: Test Connection
failed because of an error in initializing provider.
Login failed for user 'user'.
I am running Windows98SE on a home network.
Any thoughts/help, please!!!!
Hi,
By default, the MSDE was install with OS Authentication . But windows 98
will not support OS authentication.
To use MSDE on Win 98, use have to use SQL Server authentification . To use
the SQL authetification on MSDE, see readme.txt
with the MSDE.
How to change the model after installation: (Need to modify registry)
Default instance:
HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\Lo ginMode
Named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance
Name\MSSQLServer\LoginMode
Change the value to 0 or 2 for mixed-mode or 1 for integrated. (Integrated
is the default setup for the SQL Server 2000 Data Engine.)
Note The 0 and 2 values have the same functionality. They both set the
security to mixed-mode.
To set this key to mixed-mode (SQL Authentication and OS Authentication) ,
follow these steps:
1.. Stop SQL Server and all related services, such as SQLAgent, from the
Services control panel.
2.. Click Start, click Run, type regedt32, and then click OK.
3.. Find the HKEY_LOCAL_MACHINE window on the local computer.
4.. Navigate to the following registry key for the default instance:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\ MSSqlServer
5.. Navigate to the following registry key for a named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance
Name\MSSQLServer\LoginMode
6.. On the right-hand pane, find the value LoginMode and double-click it.
7.. In the DWORD Editor window, set the value to 0 or 2. Also, make sure
that the Hex radio button is selected.
8.. Click OK.
Restart SQL Server and SQL Server Agent services for this change to take
effect.
After these try conecting using the below command from command prompt.
OSQL -Usa -Sserver_name
Thanks
Hari
MCDBA
"fgbrooks" <anonymous@.discussions.microsoft.com> wrote in message
news:7c3801c43173$af8c8b00$a601280a@.phx.gbl...
> I have installed a brand new msde2000, in order to get SQL
> Service Manager. The SQL Service Mgr is installed, and it
> shows up in my tray with the little green arrow. I
> installed the msde following the instructions in the MS
> Knowledge base article 319930, How to Connect to MSDE. I
> installed it with the SECURITYMODE=SQL parameter. I used
> the osql command to give it a new password. So far so
> good. Now, following 319930, I tried a connectivity
> test. I get the following error message: Test Connection
> failed because of an error in initializing provider.
> Login failed for user 'user'.
> I am running Windows98SE on a home network.
> Any thoughts/help, please!!!!
|||Thanks for the reply. I had already checked the Registry
following the instructions of the readme and 319930, and
had previously set it at 2. At a command prompt, if I
type 'osql -U sa', I am prompted for a password (which I
had previously set up), enter it, and it appears that I am
logged in. However, when I try to test this using
a 'test.udl' file as described in 319930, it fails, even
though I enter the same password.
My server name is FRED'S COMPUTER (with a space between
the S and the C). When I try to enter your suggested
command, it reads as follows: osql -U sa -S FRED'S
COMPUTER. This command fails as improper syntax.
Evidently, it doesn't like my server name. Any other
thoughts?
>--Original Message--
>Hi,
>By default, the MSDE was install with OS Authentication .
But windows 98
>will not support OS authentication.
>To use MSDE on Win 98, use have to use SQL Server
authentification . To use
>the SQL authetification on MSDE, see readme.txt
>with the MSDE.
>How to change the model after installation: (Need to
modify registry)
>Default instance:
>HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\L oginMode
>Named instance:
>HKLM\Software\Microsoft\Microsoft SQL Server\Instance
>Name\MSSQLServer\LoginMode
>Change the value to 0 or 2 for mixed-mode or 1 for
integrated. (Integrated
>is the default setup for the SQL Server 2000 Data Engine.)
>Note The 0 and 2 values have the same functionality. They
both set the
>security to mixed-mode.
>To set this key to mixed-mode (SQL Authentication and OS
Authentication) ,
>follow these steps:
> 1.. Stop SQL Server and all related services, such as
SQLAgent, from the
>Services control panel.
> 2.. Click Start, click Run, type regedt32, and then
click OK.
> 3.. Find the HKEY_LOCAL_MACHINE window on the local
computer.
> 4.. Navigate to the following registry key for the
default instance:
>
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\ MSSqlServ
er
> 5.. Navigate to the following registry key for a named
instance:
> HKLM\Software\Microsoft\Microsoft SQL Server\Instance
>Name\MSSQLServer\LoginMode
> 6.. On the right-hand pane, find the value LoginMode
and double-click it.
> 7.. In the DWORD Editor window, set the value to 0 or
2. Also, make sure
>that the Hex radio button is selected.
> 8.. Click OK.
>Restart SQL Server and SQL Server Agent services for this
change to take
>effect.
>After these try conecting using the below command from
command prompt.
>OSQL -Usa -Sserver_name
>Thanks
>Hari
>MCDBA
>
>"fgbrooks" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:7c3801c43173$af8c8b00$a601280a@.phx.gbl...
SQL[vbcol=seagreen]
it[vbcol=seagreen]
I[vbcol=seagreen]
used[vbcol=seagreen]
Connection
>
>.
>

Failture to start Report Builder from Report manager

Dear all
I want to start report builder from report manager. I configured IIS with
integrated security. Everything seems well configured - users with access
rights can access Reporing Services, users without accessrights cannot.
But with reportbuilder I have a Authentication problem. A user from a client
machine cannot access report builder, while strangely accessing reportbuilder
from the server is possible. I assume the is a right problem, like the error
message below declares.
The weired thing is, that the user has the accessrights to the filesystem.
IIS can authenticate the user (event viewer) and I can see it also in the IIS
log. But no way to access the report builder.
Thanks for your help!
Regards,
Marc
MarcPLATFORM VERSION INFO
Windows : 5.1.2600.131072 (Win32NT)
Common Language Runtime : 2.0.50727.42
System.Deployment.dll : 2.0.50727.42 (RTM.050727-4200)
mscorwks.dll : 2.0.50727.42 (RTM.050727-4200)
dfdll.dll : 2.0.50727.42 (RTM.050727-4200)
dfshim.dll : 2.0.50727.42 (RTM.050727-4200)
SOURCES
Deployment url :
http://10.1.10.64/ReportServer/ReportBuilder/ReportBuilderLocalIntranet.application
ERROR SUMMARY
Below is a summary of the errors, details of these errors are listed later
in the log.
* Activation of
http://10.1.10.64/ReportServer/ReportBuilder/ReportBuilderLocalIntranet.application resulted in exception. Following failure messages were detected:
+ Downloading
http://10.1.10.64/ReportServer/ReportBuilder/ReportBuilderLocalIntranet.application did not succeed.
+ The remote server returned an error: (401) Unauthorized.
COMPONENT STORE TRANSACTION FAILURE SUMMARY
No transaction error was detected.
WARNINGS
There were no warnings during this operation.
OPERATION PROGRESS STATUS
* [17.07.2006 14:46:13] : Activation of
http://10.1.10.64/ReportServer/ReportBuilder/ReportBuilderLocalIntranet.application has started.
ERROR DETAILS
Following errors were detected during this operation.
* [17.07.2006 14:46:25]
System.Deployment.Application.DeploymentDownloadException (Unknown subtype)
- Downloading
http://10.1.10.64/ReportServer/ReportBuilder/ReportBuilderLocalIntranet.application did not succeed.
- Source: System.Deployment
- Stack trace:
at
System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
at System.Deployment.Application.SystemNetDownloader.DownloadAllFiles()
at
System.Deployment.Application.FileDownloader.Download(SubscriptionState
subState)
at
System.Deployment.Application.DownloadManager.DownloadManifestAsRawFile(Uri&
sourceUri, String targetPath, IDownloadNotification notification,
DownloadOptions options, ServerInformation& serverInformation)
at
System.Deployment.Application.DownloadManager.DownloadDeploymentManifestDirectBypass(SubscriptionStore
subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState,
IDownloadNotification notification, DownloadOptions options,
ServerInformation& serverInformation)
at
System.Deployment.Application.DownloadManager.DownloadDeploymentManifestBypass(SubscriptionStore
subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState,
IDownloadNotification notification, DownloadOptions options)
at
System.Deployment.Application.ApplicationActivator.PerformDeploymentActivation(Uri activationUri, Boolean isShortcut)
at
System.Deployment.Application.ApplicationActivator.ActivateDeploymentWorker(Object state)
-- Inner Exception --
System.Net.WebException
- The remote server returned an error: (401) Unauthorized.
- Source: System
- Stack trace:
at System.Net.HttpWebRequest.GetResponse()
at
System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
COMPONENT STORE TRANSACTION DETAILS
No transaction information is available.|||Hello Roger,
Thank you for post in MSDN newsgroup.
If I understand the issue correctly, a user from client cannot open report
builder while he could access report builder from local console of the RS
server. If I'm off-base, please let me know.
From the error log, it seems to be a permission issue. I suggest that you
try the following steps:
1. Get the RSWebApplication.config file located in the following folder in
most cases:
C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting
Services\ReportManager
2. Backup the RSWebApplication.config to another folder.
3. Modify the RSWebApplication.config as follows:
From:
<ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>
To:
<ReportBuilderTrustLevel>PartialTrust</ReportBuilderTrustLevel>
You may want to restart IIS by running "iisreset" to test the sitaution.
Please let me know if this helps. If you have further question, please feel
free to let's know. I look forward to your reply.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Peter
thanks for your patience and your help with my issues. Unfortunately your
advice did not fix the problem. See the error message below :-/.
Do you have any other idea what could be wrong configured?
Regards,
Marc
PLATFORM VERSION INFO
Windows : 5.1.2600.131072 (Win32NT)
Common Language Runtime : 2.0.50727.42
System.Deployment.dll : 2.0.50727.42 (RTM.050727-4200)
mscorwks.dll : 2.0.50727.42 (RTM.050727-4200)
dfdll.dll : 2.0.50727.42 (RTM.050727-4200)
dfshim.dll : 2.0.50727.42 (RTM.050727-4200)
SOURCES
Deployment url :
http://10.1.10.64/ReportServer/ReportBuilder/ReportBuilderLocalIntranet.application
ERROR SUMMARY
Below is a summary of the errors, details of these errors are listed later
in the log.
* Activation of
http://10.1.10.64/ReportServer/ReportBuilder/ReportBuilderLocalIntranet.application resulted in exception. Following failure messages were detected:
+ Downloading
http://10.1.10.64/ReportServer/ReportBuilder/ReportBuilderLocalIntranet.application did not succeed.
+ The remote server returned an error: (401) Unauthorized.
COMPONENT STORE TRANSACTION FAILURE SUMMARY
No transaction error was detected.
WARNINGS
There were no warnings during this operation.
OPERATION PROGRESS STATUS
* [18.07.2006 07:53:08] : Activation of
http://10.1.10.64/ReportServer/ReportBuilder/ReportBuilderLocalIntranet.application has started.
ERROR DETAILS
Following errors were detected during this operation.
* [18.07.2006 07:53:09]
System.Deployment.Application.DeploymentDownloadException (Unknown subtype)
- Downloading
http://10.1.10.64/ReportServer/ReportBuilder/ReportBuilderLocalIntranet.application did not succeed.
- Source: System.Deployment
- Stack trace:
at
System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
at System.Deployment.Application.SystemNetDownloader.DownloadAllFiles()
at
System.Deployment.Application.FileDownloader.Download(SubscriptionState
subState)
at
System.Deployment.Application.DownloadManager.DownloadManifestAsRawFile(Uri&
sourceUri, String targetPath, IDownloadNotification notification,
DownloadOptions options, ServerInformation& serverInformation)
at
System.Deployment.Application.DownloadManager.DownloadDeploymentManifestDirectBypass(SubscriptionStore
subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState,
IDownloadNotification notification, DownloadOptions options,
ServerInformation& serverInformation)
at
System.Deployment.Application.DownloadManager.DownloadDeploymentManifestBypass(SubscriptionStore
subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState,
IDownloadNotification notification, DownloadOptions options)
at
System.Deployment.Application.ApplicationActivator.PerformDeploymentActivation(Uri activationUri, Boolean isShortcut)
at
System.Deployment.Application.ApplicationActivator.ActivateDeploymentWorker(Object state)
-- Inner Exception --
System.Net.WebException
- The remote server returned an error: (401) Unauthorized.
- Source: System
- Stack trace:
at System.Net.HttpWebRequest.GetResponse()
at
System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
COMPONENT STORE TRANSACTION DETAILS
No transaction information is available.|||Hello Marc,
Thank you for your update. Based on my further research, it seems the issue
is related to the file security. I suggest that you try the following steps:
Open IIS and navigate to Web Sites | Default Web Site | ReportServer |
ReportBuilder
Right click ReportBuilder.application
/ReportBuilderLocalIntranet.application
Click Properties
Click File Security
Click Edit under Authentication and Access Control
Select Integrated Windows Authentication
Uncheck Anonymouse authentication
Repeat the above steps for ReportBuilder.exe and ReportBuilder.exe.manifest
ReportBuilderLocalIntranet.exe.manifest
Please also do above step on ReportServer and ReportBuilder Virtual
directory.
Also, I'd like to know if you have tested on different client and if you
logged on as a domain user with admin rights on report server from a client
machine.
If you have any update, please feel free to let me know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Peter
Thanks for your reply. I first checked that everything is set just to
integrated windows authentication. But this did not resolve the problem. Then
I tested what happens when the files:
ReportBuilder.application
ReportBuilder.exe
ReportBulder.exe.manifest
ReportBuilderLocalIntranet.application
ReportBuilderLocalIntranet.exe.manifest
have Anonymous authentication (checked). And I granted the anonymous user
access to the files listed above.
And that gave me access to report builder!
What is strange I think, that Integrated Windows Authentication deosn't work
here. But perhaps I have a misconfiguration somewhere.
Best Regards,
Marc|||Hello Marc,
Thank you for your update. I understand that you'v resolved the issue by
granting anaonymous access to all of the files I mentioned. Though it is
not easy to find the root cause of the issue, I guess this might be related
to some issues on SRS VDs and options of web.config.
Your experience shall benefit the community. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================