SSIS flat file import help!

[janetbarnett99]

Boy, do I need HELP! Have a simple csv file that I need to import. Worked fine in sql2000; I put it into dts to execute on a monthly basis. Makes connection, db connection, table creation fine, but stops at validation of flat file?

Basically, I want to go out and get a flat file, drop the existing table, and create the table, and import the information from the flat file. Not a complicated table of about 30,000 records.

Create table [db].[dbo].[tblPatient] (
[patientID] into not null, [chartID] varChar(15) null, [doctorID] int null, [birthdate] datetime null, [sex] varchar(1) null, [raceID] int null, [city] varchar(100) null, [state] varchar(2) null, [zip9] varchar(9) null, [patientTypeID] int null, [patName] varchar(100) null)

Below is the error report that tells me NOTHING!

Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Success)
- Setting Destination Connection (Success)
- Validating (Error)
Messages
* Error 0xc00470fe: Data Flow Task:
The product level is insufficient for component "Source - pmPatientInfo_csv" (1).
(SQL Server Import and Export Wizard)
* Error 0xc00470fe: Data Flow Task:
The product level is insufficient for component "Data Conversion 1" (71).
(SQL Server Import and Export Wizard)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Success)
- Copying to [fhc].[dbo].[tblpatient3] (Stopped)
- Post-execute (Stopped)
- Cleanup (Stopped)

 

[mrdenny]

This is because you are trying to run it on your workstation and your workstation doesn't have the SSIS server installed.

Install SP1 for SQL 2005 on your workstation and the problem should be fixed.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

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

http://www.mrdenny.com

 

[janetbarnett99]

Thanks - I installed sp1 on my local machine. Now the error is a truncation error. However, I'm using 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)

 

[janetbarnett99]

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

 

[mrdenny]

The SSIS wizzard is fairly basic. If the text file needs more advanced convertion, then the convertion may need to be done manually in the Business Intelligence designer.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

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

http://www.mrdenny.com