Data flow flat file help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Data flow flat file help

Author

Topic

jbphoenix
Yak Posting Veteran

68 Posts

Posted - 2009-01-05 : 14:33:57

This is my first attempt at creating a package. I'm am importing a txt file into a table. On the Data flow tab I have my flat file source and then the sql server destination. Here is what the Progress tab shows. I'm not too sure why the SQL Server destination task turns Red.

SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Error: 0xC0202009 at Data Flow Task, SQL Server Destination [282]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".
Error: 0xC0202071 at Data Flow Task, SQL Server Destination [282]: Unable to prepare the SSIS bulk insert for data insertion.
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "SQL Server Destination" (282) failed the pre-execute phase and returned error code 0xC0202071.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "SQL Server Destination" (282)" wrote 0 rows.
Task failed: Data Flow Task
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) 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.

hey001us
Posting Yak Master

185 Posts

Posted - 2009-01-05 : 14:44:47

1) Did you created new flat file connection manager?
2) What is the path of the file that you pointed on the connection manager?
3) Is the path and file name are correct? and got access to the path?
4) How you are mapping your column name? (1st row or manual)

hey

jbphoenix
Yak Posting Veteran

68 Posts

Posted - 2009-01-05 : 14:54:42

1. I did create new flat file connection mgr and I can preview the data.
2. the file is located on a file server
3. The path and file name are correct since I can preview the data.
4. I have mapped the column names. I'm only using 1 column at this time for testing purposes. Once I get it work correctly I'll add the others.

hey001us
Posting Yak Master

185 Posts

Posted - 2009-01-05 : 15:02:30

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.

the SSIS is located in remote server or local?

try this this.
http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/32a4f178-154f-460d-97d3-a19f6302a109/

and this

http://www.sqlservercentral.com/Forums/Topic488046-147-1.aspx

hey

jbphoenix
Yak Posting Veteran

68 Posts

Posted - 2009-01-05 : 15:31:52

Thanks for you help.

That worked mostly - I did change it to an ole db destination. But now I'm getting a failure on the flat source file. It says the file is being used by another resouce. I am the only one who uses that file.

SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Task, Flat File Source [1427]: The processing of file "\\Fileserver\it\POS Files\2008\Copy of 2008 11 Updated Summary Sheets\Data\ADI_HES.txt" has started.
Warning: 0x80070020 at Data Flow Task, Flat File Source [1427]: The process cannot access the file because it is being used by another process.
Error: 0xC020200E at Data Flow Task, Flat File Source [1427]: Cannot open the datafile "\\Fileserver\it\POS Files\2008\Copy of 2008 11 Updated Summary Sheets\Data\ADI_HES.txt".
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Flat File Source" (1427) failed the pre-execute phase and returned error code 0xC020200E.
Information: 0x402090DD at Data Flow Task, Flat File Source [1427]: The processing of file "\\Fileserver\it\POS Files\2008\Copy of 2008 11 Updated Summary Sheets\Data\ADI_HES.txt" has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (1960)" wrote 0 rows.
Task failed: Data Flow Task
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) 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.
The program '[176] Package.dtsx: DTS' has exited with code 0 (0x0).

hey001us
Posting Yak Master

185 Posts

Posted - 2009-01-05 : 15:46:45

It mean that your file handler not closed properly, or some other process accessing the file as well.

hey

jbphoenix
Yak Posting Veteran

68 Posts

Posted - 2009-01-05 : 15:49:20

I just tried using FileMon to figure out what process is still accessing the file. But I'm not coming up with anything.

jbphoenix
Yak Posting Veteran

68 Posts

Posted - 2009-01-05 : 15:49:21

I just tried using FileMon to figure out what process is still accessing the file. But I'm not coming up with anything.

hey001us
Posting Yak Master

185 Posts

Posted - 2009-01-05 : 15:58:56

if it is, try to restart all your process?

hey

jbphoenix
Yak Posting Veteran

68 Posts

Posted - 2009-01-05 : 16:26:04

Hey - thanks for all your help. I'm not sure what was happening/going wrong. But I closed out of everything and started over and everything works perfectly.

Thanks again.

hey001us
Posting Yak Master

185 Posts

Posted - 2009-01-05 : 16:33:08

You're Welcome
Great :)
Sometime the application not closing the file handler properly hence I request you to restart the process.

hey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources