SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS package turns yellow and stops.


SSIS package turns yellow and stops.

Author
Message
Briceston
Briceston
SSChasing Mays
SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)

Group: General Forum Members
Points: 624 Visits: 506
Hello all,

I have a SSIS package that consists of a Foreach file Enumerator and a Foreach ADO.NET Schema Rowset Enumerator. Within Foreach ADO.NET Schema Rowset Enumerator, I have DFT that has an excel source, data conversion, derived column and OLEDB destination.

My problem is when I run the package it turns yellow, then a black window flashes very quickly and that's it. I can't read what the black window so says because it flashes so fast. I have no error messages.

The following message below is from the output:
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
SSIS package "Package.dtsx" finished: Canceled.

I have delay validation set to True for each step, so I'm not sure why the package won't run it's full course. If I execute the package from inside the DFT it works fine, so the problem is with the container.

My defined variables are as followed:

VarDirectory Package String C:\loadfiles\
VarExcelPath Package String C:\loadfiles\Testfile.xls
VarFileExtension Package String *.xls
VarSheet Package String Sheet1$A3:X

For the Foreach File Enumerator my setting are as followed:
Directory @[User::VarDirectory]
Filespec @[User::VarFileExtension]
Variable Mappings: @[User::VarFileExtension] Index set to 0

For the Foreach ADO.NET Schema Rowset Enumerator my settings are as followed:
Connection: Excel Schema
Schema: Tables
Variable Mappings: User::VarSheet Index set to 2

I tried researching the error but I have not found any resolve. Can you guys please provide some insight? Thanks
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64640 Visits: 13298
What if you execute the package outside of the editor using dtexec?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Briceston
Briceston
SSChasing Mays
SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)

Group: General Forum Members
Points: 624 Visits: 506
How do I execute outside the editor? Thanks.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64640 Visits: 13298
Do you always run packages by hand?

dtexec Utility (SSIS Tool)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Briceston
Briceston
SSChasing Mays
SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)

Group: General Forum Members
Points: 624 Visits: 506
Yes, my packages are ran by hand or automated via job through sql agent.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64640 Visits: 13298
Briceston (7/28/2014)
Yes, my packages are ran by hand or automated via job through sql agent.


OK, what if you run the package in a SQL job?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Briceston
Briceston
SSChasing Mays
SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)

Group: General Forum Members
Points: 624 Visits: 506
Running the package via a job returns the same outcome as before. My problem is with the ForEach Loop Container. The package starts the validation phase and then cancels.

SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
SSIS package "Package.dtsx" finished: Canceled.
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21471 Visits: 7660
Briceston (7/27/2014)

I have a SSIS package that consists of a Foreach file Enumerator and a Foreach ADO.NET Schema Rowset Enumerator. Within Foreach ADO.NET Schema Rowset Enumerator, I have DFT that has an excel source, data conversion, derived column and OLEDB destination.

I want to confirm that this is ALL it does, and there's no previous steps. Why is below...

My problem is when I run the package it turns yellow, then a black window flashes very quickly and that's it. I can't read what the black window so says because it flashes so fast. I have no error messages.

My best guess is that's a console opening up, then hiding itself. Nothing you described above should be doing that to my knowledge.


For the Foreach ADO.NET Schema Rowset Enumerator my settings are as followed:
Connection: Excel Schema
Schema: Tables
Variable Mappings: User::VarSheet Index set to 2


I've never used the Schema Rowset enumerator before, particularly against Excel, so I can't speak to this component yet. However, when you run this as a job, inside the step for the job you can turn on logging for the process; inside the step, go to Advanced, a few lines down is Output File. Stuff an output into a file somewhere and then come back with the results of it from a failure. This will help give us more details about what exactly has, is, and attempted to occur in your package.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Briceston
Briceston
SSChasing Mays
SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)

Group: General Forum Members
Points: 624 Visits: 506
I got the package to work after hours of investigating it. Turns out one of my variables did not have the complete file path. Also, my outer Foreachloop Container was on defined the package scope opposed to the Foreachloop container scope. Thanks for your insight though it's appreciated.
Briceston
Briceston
SSChasing Mays
SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)

Group: General Forum Members
Points: 624 Visits: 506
I'm encountering the below errors when I run my package. I have tried switching to 32 bit as per some resolves on the internet, but to no avail, and have delay validation set to true. The content of data is fine. Seemingly the package just stop enumerating once it reaches a certain amount of excel files. I have about 100+ excel files in the package folder. Any ideas?

Error Output:
Error: 0xC0202009 at Package, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
Error: 0xC020801C at Data Flow Task, Excel Source [3421]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component "Excel Source" (3421) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Error: 0xC0014023 at Loop through sheets: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (57) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search