Help w/ SSIS Data Flow Task

[sqlsadie108]

Hi, I'm also posting this on SSIS boards, but I need all the help I can get.

I am trying to execute a Data Flow Task. There is only one task in the data flow task so far, and that is a Flat File Source task. I also set up a Connection Manager for the file, myfile.txt.

When I try to run the Data Flow Task, I get these errors:

The first problem seems to be with the connection, on the first line. What connection element is missing here? The others seem to be truncation errors. I don't know why that is. I changed the column sizes to match the sizes of database columns. They ARE surrounded by double-quotes, but I have {"} specified as a Text Qualifier in my Connection Manager.

Please, need help!

Error: 0xC001000E at : The connection "{F6513CB9-29E2-4D88-A86B-0EF9DFABE9D6}" is not found. This error is thrown by Connections collection when the specific connection element is not found.
SSIS package "MyPackage.dtsx" starting.
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 [2168]: The processing of file "D:\myfile.txt" has started.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Error: 0xC02020A1 at Data Flow Task, Flat File Source [2168]: Data conversion failed. The data conversion for column "Column 0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Task, Flat File Source [2168]: The "output column "Column 0" (2185)" failed because truncation occurred, and the truncation row disposition on "output column "Column 0" (2185)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0202092 at Data Flow Task, Flat File Source [2168]: An error occurred while processing file "D:\myfile.txt" on data row 1.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Flat File Source" (2168) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Task, Flat File Source [2168]: The processing of file "D:\myfile.txt" has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Task failed: Data Flow Task

 

[mrdenny]

Are you loading into unicode data types or non-unicode data types?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sqlgracie]

Hi Mrdenny,

I don't know... where would I check?

So far, I only have Flat File Data Source defined. Can I look in the properties of that?

Also, why would the connection fail?

help...........

 

[mrdenny]

I was referring to the data types of the table you are loading into.

SSIS assumes that text files are uni-code.

You may need to route the data feed through a data type conversition task.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sqlsadie108]

I haven't even gotten that far yet...

I am simply trying to run a Data Flow Task with only one subcomponent. The subcomponent is just a Flat File Source adapter. I have set up the Connection Manager connection for the Flat File Source.

And I keep getting this error. Maybe I cannot just run the Flat File Source, eh? I don't know what the heck connection it's referring to... maybe the Flat File Source wants to connect to something??

Error: 0xC001000E at : The connection "{B9E11DD7-21A1-4FD7-986F-6BDC5B81F51D}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

 

[mrdenny]

Did you create a connection, then setup the data flow, then delete the connection and create a new one with the same name?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sqlsadie108]

Did you create a connection, then setup the data flow, then delete the connection and create a new one with the same name?

Yes, I did - is this a problem??

 

[mrdenny]

Yes it is. You need to edit the data pump task and tell it to use the new connection. Everything in SSIS uses GUIDs to identify the objects not the names. You may need to create a dummy connection object tell the data pump to use that, then switch back to the correct one.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sqlsadie108]

So you're saying everytime I change the connection, I need to create a connection with a new name?

Frankly, at this point, I am so muddled as to what I've done and not done, I really can't backtrack to pinpoint the exact problem, and how or when it occurred.

However, even using a NEW connection with a brand new name still gives me this error when using a SSIS pkg that was migrated from a DTS pkg.

That is, when I change the connection manager for the migrated package to a BRAND new connection manager, with a brand new name, it's still throwing the error, even though it still performs the task correctly.

 

[mrdenny]

Sounds like the migration wizzard is having a problem. What SQL 2005 service pack have you installed on your workstation?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sqlsadie108]

SP 1

 

[mrdenny]

Try installing SP2. SP2 does fix a ton of bugs.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sqlsadie108]

i did not realize there was an sp2 for sql server 2005 out

thanks for the tip!