Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS package turns yellow and stops. Expand / Collapse
Author
Message
Posted Sunday, July 27, 2014 9:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 8:56 AM
Points: 25, Visits: 170
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


Post #1596626
Posted Monday, July 28, 2014 12:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:28 AM
Points: 13,026, Visits: 10,803
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1596694
Posted Monday, July 28, 2014 7:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 8:56 AM
Points: 25, Visits: 170
How do I execute outside the editor? Thanks.
Post #1596850
Posted Monday, July 28, 2014 7:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:28 AM
Points: 13,026, Visits: 10,803
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1596868
Posted Monday, July 28, 2014 9:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 8:56 AM
Points: 25, Visits: 170
Yes, my packages are ran by hand or automated via job through sql agent.
Post #1596914
Posted Monday, July 28, 2014 10:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:28 AM
Points: 13,026, Visits: 10,803
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1596944
Posted Monday, July 28, 2014 11:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 8:56 AM
Points: 25, Visits: 170
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.


Post #1596996
Posted Monday, July 28, 2014 4:59 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:46 PM
Points: 5,384, Visits: 7,458
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
Post #1597115
Posted Tuesday, July 29, 2014 7:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 8:56 AM
Points: 25, Visits: 170
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.
Post #1597297
Posted Thursday, July 31, 2014 7:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 8:56 AM
Points: 25, Visits: 170
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.
Post #1598261
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse