February 26, 2014 at 10:24 am
Hi All,
I have been all over the web trying to figure this out, but no luck. Please assist if you can!
The simple story:
The package is dynamically exporting several documents to a new folder each run.
The following file types are being created each time (several of each):
TXT
XLSX (multiple tabs / worksheets)
XLS
I have not gotten to programming the XLS file, because this issue surfaced while programming the XLSX.
the control flow is summarized:
1.(variables set by expressions)
2.New folder created on network share.
3.XLSX file is created, and 5 sheets are created. (headers and sheet name, no data), each sheet has different headers / columns.
4.Dataflow(s)
4A) The Dataflows are split into 3 flows. the first one creates the first 2 txt files, and populates 3 of the 5 tabs of the xlsx.
4B) populate 4th tab of the xlsx
4C) populate the 5th tab of the xlsx
The issue is that i am getting an error inconsistently when this runs all the way through, on step 4C.
[Excel Destination 1 2 1 1 1 [1]] Error:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
The AcquireConnection method call to the connection manager "Premium2008XLSX_B" failed with
error code 0xC0202009.
There may be error messages posted before this with more information on why the AcquireConnection method call failed.
[SSIS.Pipeline] Error: component "Excel Destination 1 2 1 1 1" (1) failed validation and returned
error code 0xC020801C.
But the kicker is that if I disabled 4A, then 4B and 4C work. So I know that each of the pieces of this package work on their own, but it's like when there are too many xlsx connections to the same file, a lock can happen and error out the next step.
Amazingly it ran successfully all the way through, for the first time, as I am writing this. This may be because it is lunch hour and there is less traffic on the shared server, not sure. But I am sure that i have done nothing different, and the problem will keep occurring.
My thought was that the package needs to completely close the xlsx connection in memory, before trying to populate another sheet, so I tried putting a 15 second delay between the flows, but that did not alleviate the issue.
My machine is 32bit, but i did make sure to run it as 32 bit.
any suggestions would be welcomed, thanks in advance.
EDIT :
1 Hour Later I try to run the package again, and the first flow (4A) fails. the same error message pops, and the node that is highlighted is one of the xlsx destinations.
4 Minutes later, rerun yielded a failure on 4B.
February 27, 2014 at 6:18 am
bump?
I know there are a lot of bright minds on here, somebody surely has come across this before?
I am going to try to make / populate the xlsx file locally and then File System Task it over to the share.
February 27, 2014 at 8:58 am
Just a shot in the dark here but try setting "Retain Same Connection" to True in your Connection Managers
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 27, 2014 at 12:42 pm
Thanks,
I tried researching that, and it seems that is only for data source connections that support multiple connections. Excel definitely does not, and the property is likely ignored.
First test for local creation worked. i will try it rerunning it several more times, and if it works consistently, i will call it the fix.
February 27, 2014 at 1:29 pm
well I think it's safe to say that the creation of the network share was causing the issue. It works consistently when created locally.
But what i cannot understand is why I need to use 2 connections to the same file?
Connection 1 is used to create the XLSX and tabs.
connection 2 is used to populate.
If i try to use the same connection, the package will seize up / fail or produce a corrupt file. I haven't noticed any other documentation on that?
February 27, 2014 at 2:03 pm
You got me, seems rather odd the issue you are experiencing with the network share....are you using a fully qualified domain name? Or mapping to am admin share?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 3, 2014 at 8:07 am
As far as I know, this is the full path.
I have it in this format
\\server\folder$\folder\folder\...\file.xlsx
the second folder in appears to be a normal folder, with $ at the end of the name.
i have this structure mapped as a drive on my machine like
s:\...\file.xlsx
which i could use in BIDS, but wouldnt be practical, or work as a job.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply