Installed an upgrade from 2000 sp4 to 2005 sp1. Trying to import simple flat
file of 30,000 records but getting error that told me nothing. Somebody
suggested installing SP1 on my local machine to get better error reporting,
which I did. Now the error is a truncation error. However, in the wizard
(it's a very simple import, like my brain), using the advanced setting and
asking sql to suggest types, and getting the following error. But what
doesn't make sense to me is that I've changed the output on that field to
varChar(100). Isn't the purpose of a wizard to accommodate someone like me
who isn't a full-fledged dba?

I've figured out a way to do a command line run of the old dtsrun.exe
utility in the interim. (By the way, the dtsrun executes and imports the data
fine into 2005.) But, I'd like to figure out how one does implicit
conversions with a wizard? And, I need to get the basics of the SSIS down.

Suggestions of online tutorials would be appreciated....

Executing (Error)

Messages

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
conversion for column "Pat L Name" returned status value 4 and status text
"Text was truncated or one or more characters had no match in the target code
page.".
(SQL Server Import and Export Wizard)


Error 0xc020902a: Data Flow Task: The "output column "Pat L Name" (50)"
failed because truncation occurred, and the truncation row disposition on
"output column "Pat L Name" (50)" specifies failure on truncation. A
truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)


Error 0xc0202092: Data Flow Task: An error occurred while processing file
"X:\misys\sqlImports\pmPatientInfo.csv" on data row 106.
(SQL Server Import and Export Wizard)


Error 0xc0047038: Data Flow Task: The PrimeOutput method on component
"Source - pmPatientInfo_csv" (1) 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.
(SQL Server Import and Export Wizard)


Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with
error code 0xC0047038.
(SQL Server Import and Export Wizard)


Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown
signal and is terminating. The user requested a shutdown, or an error in
another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)


Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error
code 0xC0047039.
(SQL Server Import and Export Wizard)

If this is helpful to anyone, here goes: Tried to go through the virtual lab
for ssis on the MS site, but, unfortunately, got to page 7 of 15 and there
are no objects in the toolbox on the virtual lab, so I had to quit.

Moved two of the dts to bulk imports for flat file imports and that's fine
for them. On the two remaining that go through AccessXP, I got one of the
migrated dts packages to work. I had to go in and manually play with the
datatype conversions. Trial and error, and finally got it to work.

The other, though, blows up when there is a null value in a field that is
being imported into a non-null bit field. I've got the conversion object
open, but can't figure out how to tell it to use a zero in place of a null.
Any ideas on that one? Unfortunately, I need to figure this one out because
it's the one that needs to run at midnight every night.

I'd also appreciate any hints on good tutorials out there for this. (Other
than the MS virtual lab.)

Janet

Personally I do not use the wizards. I prefer to build my packages
manually. for what you want it sounds like this would be easy to do also.
You say you have asked SSIS to suggest data types. The issue then could be
that because SSIS will sample n rows then it has not encountered a piece of
data large enough for a row further down the file and hence not specced the
column correctly.

This is why data is being truncated.

Part of this article may help

Looping over files with the Foreach Loop
(http://www.sqlis.com/default.aspx?55)


Also I wouldn't say SSIS is a DBA tool. I would say it is more geared
towards developers

--


Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com



"Janet" <Janet (AT) discussions (DOT) microsoft.com> wrote

Thanks, Alan. Yeah, I probably will eventually NOT use the wizard, but I've
found them useful when you're learning - when they work right. I had tried
increasing the record reviewed file, but that didn't help. It just wasn't
picking the correct datatype.

I used an interim table on the item I was having trouble with and then
executed a stored procedure to make the transfer into the table I needed the
data in. I could then use the isNull(fieldname,0) to take care of my earlier
problem.

Janet