July 26, 2006 at 11:59 am
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? (Told the SQL2005 to make best suggestion on import side.)
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)
July 27, 2006 at 5:52 am
Hi Janet,
This message is related to the edition of SQL Server 2005 installed.
Because of the separation of services and components - which provide some really nice flexibility to enterprise database architects - it's now also possible to create a scenario where you can create a package and not be able to execute it.
There's a big discussion about the scenario (and about SP1 addressing it - not sure if SP1 fixes this or not) at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112469&SiteID=1.
One concensus is installing the SQL Server 2005 Integration Services engine will correct the issue.
If you need more help, feel free to contact me at andy.leonard@gmail.com.
:{> Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
July 27, 2006 at 7:07 am
Andy,
Thanks so much for the reply. The engine is installed and running - with SP1 applied. If I resinstall, then reinstall SP1, what's the diff?
Somebody did suggest installing SP1 on my local machine to get better errors, which I did. 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)
July 27, 2006 at 11:36 am
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
July 27, 2006 at 11:46 am
Hi Janet,
There's a SQL function for replacing nulls. Try this...
Instead of "SELECT Field1 FROM SomeTable", try "SELECT IsNull(Field1, 0) FROM SomeTable"
:{> Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
July 27, 2006 at 12:13 pm
Well, hell, Andy. I'm just dumb as a rock. Looked at this too long in panic, I guess.
I guess if you have no control over your import file, though, you'd have to use an interim table to fix this, huh? Seems like you should be able to do it.
Thanks, hopefully the job will run tonight and I can breath a bit easier.
jb
July 27, 2006 at 1:14 pm
When you let the application make its best guess on the field sizes and types it only looks at the first few rows. So its not unusual to get data thats bigger than the system will allow. I've done this so many times i have a pattern that i'd like to suggest for you:
1) make a table full of varchars that are way larger than is reasonable and none of the fields are required.
2) truncate the table to make sure its empty and ready to import your data into
3) import the data into this table
4) run a bunch of queries that sanitize the data for you, like converting nulls to zeros, truncating strings that are too long, holding bad data
5) report the truncated and bad data to someone
6) copy the fixed up data into the one that you will have the rest of the system operate on.
this allows you to have the cleanest data for the rest of the system and lets you know if you've got bad data or something that needs to be reexamined.
July 27, 2006 at 1:18 pm
Thanks Bill! Actually, that's what I wound up doing and it's working great. Seems like there should be a way to convert null fields in the conversion, though, so you don't have to create an interim table?
Really appreciate the response. Maybe if I wasn't in such a panic, my little ole brain would've gotten it quicker.
Thanks!
July 27, 2006 at 1:28 pm
When you create your table you can specify a default value for the column
create table mystuff ( bob varchar (50) Default 'Stuff' )
so when there would otherwise be a Null in the column bob it will fill in a default value of 'Stuff'. Check out BOL under Create Table for the limits.
July 27, 2006 at 1:40 pm
Bill,
Ummm, already have that but I haven't found that to be very helpful with large, bulk inserts. It certainly works with entries made a record at a time with a web app or something, though.
Janet
July 27, 2006 at 1:44 pm
Hmmm, truthfully I don't use it much. Instead I rely on one of the queries in Step 4 above to handle those for me.
July 27, 2006 at 2:34 pm
A default value will be used in a non-null column if you run an insert that does not include that column. If you run an insert that is trying to insert the specific value NULL into a non-null column you get an error.
You could use a Derived Column transform in SSIS to incorporate the default values.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply