June 30, 2011 at 8:28 am
Hi All,
A potential employer wants me to do a project in SSIS which I have never used before - until yesterday. This was the task I was given:
"Create an SSIS package that imports the three data files in E:\ETL\Source Data into the DataMart database on the local database server. Your package should have logging capabilities, as well as an error handling and notification mechanism. Also, please comment on the structure of the data in the resulting tables."
Would really appreciate anyone's help with this. Thank you so much! I have no idea what any of that means.
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Change Order, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Projects, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC00F9304 at SonaliPackage, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.
Error: 0xC020801C at Projects, Projects 1 [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Projects, SSIS.Pipeline: component "Projects 1" (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Projects, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Projects: There were errors during task validation.
SSIS package "Package.dtsx" finished: Failure.
June 30, 2011 at 10:16 am
Now I'm getting these errors:
SSIS package "SonaliPackage.dtsx" starting.
Error: 0xC001404B at SonaliPackage, Log provider "SSIS log provider for Text files": The SSIS logging provider has failed to open the log. Error code: 0x80070005.
Access is denied.
Information: 0x4004300A at Change Order, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Projects, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC00F9304 at SonaliPackage, Connection manager "Excel Connection Manager 1": SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.
Error: 0xC020801C at Projects, Projects [1081]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager 1" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Projects, SSIS.Pipeline: component "Projects" (1081) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Projects, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Projects: There were errors during task validation.
SSIS package "SonaliPackage.dtsx" finished: Failure.
June 30, 2011 at 1:57 pm
First of all, don't use the JET provider (which is used to read 2003-Excel files), it doesn't work in 64-bit.
The latest version of the ACE provider (used for .xslx) is available in 64-bit. If that isn't an option, you must run the package in 32-bit mode.
In BIDS, you do this by setting the project property RunAs64BitRunTime to False. In a SQL Agent job in SQL 2008 or later, you can check a checkbox so that the jobstep will run in 32-bit mode.
Regarding the log: you apparently don't have access to the log file. Contact the administrator and make sure the file isn't locked by another process.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 30, 2011 at 3:19 pm
Thanks for your help. I changed the setting and now get this output:
SSIS package "SonaliPackage.dtsx" starting.
Information: 0x4004300A at Change Order, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Projects, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Purchase Order, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4001100A at Sequence Container: Starting distributed transaction for this container.
Information: 0x4004300A at Purchase Order, SSIS.Pipeline: Validation phase is beginning.
Warning: 0x80049304 at Purchase Order, SSIS.Pipeline: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console.
Information: 0x40043006 at Purchase Order, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Purchase Order, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Purchase Order, PurchaseOrders [19]: The processing of file "E:\ETL\Source Data\PurchaseOrders.csv" has started.
Information: 0x4004300C at Purchase Order, SSIS.Pipeline: Execute phase is beginning.
Information: 0x402090DE at Purchase Order, PurchaseOrders [19]: The total number of data rows processed for file "E:\ETL\Source Data\PurchaseOrders.csv" is 11871.
Information: 0x402090DF at Purchase Order, OLE DB Destination 1 [1252]: The final commit for the data insertion in "component "OLE DB Destination 1" (1252)" has started.
Information: 0x402090E0 at Purchase Order, OLE DB Destination 1 [1252]: The final commit for the data insertion in "component "OLE DB Destination 1" (1252)" has ended.
Information: 0x402090DF at Purchase Order, OLE DB Destination [959]: The final commit for the data insertion in "component "OLE DB Destination" (959)" has started.
Information: 0x402090E0 at Purchase Order, OLE DB Destination [959]: The final commit for the data insertion in "component "OLE DB Destination" (959)" has ended.
Information: 0x40043008 at Purchase Order, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Purchase Order, PurchaseOrders [19]: The processing of file "E:\ETL\Source Data\PurchaseOrders.csv" has ended.
Information: 0x4004300B at Purchase Order, SSIS.Pipeline: "component "OLE DB Destination" (959)" wrote 11870 rows.
Information: 0x4004300B at Purchase Order, SSIS.Pipeline: "component "OLE DB Destination 1" (1252)" wrote 0 rows.
Information: 0x40043009 at Purchase Order, SSIS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300A at Projects, SSIS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Projects, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Projects, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Projects, SSIS.Pipeline: Execute phase is beginning.
Information: 0x402090DF at Projects, OLE DB Destination 1 [2131]: The final commit for the data insertion in "component "OLE DB Destination 1" (2131)" has started.
Information: 0x402090E0 at Projects, OLE DB Destination 1 [2131]: The final commit for the data insertion in "component "OLE DB Destination 1" (2131)" has ended.
Information: 0x402090DF at Projects, OLE DB Destination [1527]: The final commit for the data insertion in "component "OLE DB Destination" (1527)" has started.
Information: 0x402090E0 at Projects, OLE DB Destination [1527]: The final commit for the data insertion in "component "OLE DB Destination" (1527)" has ended.
Information: 0x40043008 at Projects, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x4004300B at Projects, SSIS.Pipeline: "component "OLE DB Destination" (1527)" wrote 65000 rows.
Information: 0x4004300B at Projects, SSIS.Pipeline: "component "OLE DB Destination 1" (2131)" wrote 0 rows.
Information: 0x40043009 at Projects, SSIS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300A at Change Order, SSIS.Pipeline: Validation phase is beginning.
Warning: 0x80047076 at Change Order, SSIS.Pipeline: The output column "LIWBSSegment2" (190) on output "Data Conversion Output" (174) and component "Data Conversion" (172) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at Change Order, SSIS.Pipeline: The output column "ProjectNumber" (196) on output "Data Conversion Output" (174) and component "Data Conversion" (172) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Information: 0x40043006 at Change Order, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Change Order, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Change Order, SSIS.Pipeline: Execute phase is beginning.
Information: 0x402090DF at Change Order, ChangeOrderErrors [314]: The final commit for the data insertion in "component "ChangeOrderErrors" (314)" has started.
Information: 0x402090E0 at Change Order, ChangeOrderErrors [314]: The final commit for the data insertion in "component "ChangeOrderErrors" (314)" has ended.
Information: 0x402090DF at Change Order, OLE DB Destination [201]: The final commit for the data insertion in "component "OLE DB Destination" (201)" has started.
Information: 0x402090E0 at Change Order, OLE DB Destination [201]: The final commit for the data insertion in "component "OLE DB Destination" (201)" has ended.
Information: 0x40043008 at Change Order, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x4004300B at Change Order, SSIS.Pipeline: "component "OLE DB Destination" (201)" wrote 3943 rows.
Information: 0x4004300B at Change Order, SSIS.Pipeline: "component "ChangeOrderErrors" (314)" wrote 0 rows.
Information: 0x40043009 at Change Order, SSIS.Pipeline: Cleanup phase is beginning.
Information: 0x4001100B at Sequence Container: Committing distributed transaction started by this container.
SSIS package "SonaliPackage.dtsx" finished: Success.
June 30, 2011 at 11:23 pm
The very last word of all that text says "success".
Is there any question you still have?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply