SSIS package turns yellow and stops.

Question

SSIS package turns yellow and stops.

Briceston

SSCrazy

Points: 2899
More actions
July 27, 2014 at 9:27 am

#310315

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

What if you execute the package outside of the editor using dtexec?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Briceston SSCrazy Points: 2899 More actions · Answer 2

Briceston

SSCrazy

Points: 2899

July 28, 2014 at 7:23 am

#1732831

How do I execute outside the editor? Thanks.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Do you always run packages by hand?

dtexec Utility (SSIS Tool)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Briceston SSCrazy Points: 2899 More actions · Answer 4

Yes, my packages are ran by hand or automated via job through sql agent.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

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?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Briceston SSCrazy Points: 2899 More actions · Answer 6

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 SSC Guru Points: 100851 More actions · Answer 7

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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Briceston SSCrazy Points: 2899 More actions · Answer 8

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 SSCrazy Points: 2899 More actions · Answer 9

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.

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.