| Author |
Topic |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
 Posted - 2008-10-03 : 15:07:24
|
| I am doing a huge data import from a text file to a wide table. I used BUL INSERT for this, there were many data type problems. Someone on this site (guess it was Peso) suggested to import the data in a STAGING table with all varchar columns. BULK INSERT went well. Now I need to import to actual table from staging table. I am planning to use SSIS, data flow task. On the source I am using a query with CONVERT function. This query fails because of data type compatibility (for the same reason I had imported data into a all varchar table earlier!)Is there a way to segregate these bad records?------------------------I think, therefore I am - Rene Descartes |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-03 : 15:12:55
|
| Yes. You can use Data Conversion task to correct data type and also with Lookup Transformation task you can direct it to other destination if you have any. |
 |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-10-03 : 15:24:55
|
| Thank you Sodeep. I don't need [Data Conversion task], becuase data is already converted at the source using a CONVERT function in the SELECT statement. I need to capture the errors resulting because of CONVERT.------------------------I think, therefore I am - Rene Descartes |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-03 : 15:45:30
|
| Use configure error output in your OLEDB destination task and redirect row with red arrow pointing to other destination OLEDB destination . |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-10-03 : 15:54:58
|
| I got yor point. Why should I do it on the DESTINATION? I am expecting error on the SOURCE. I have done your suggestion in the SOURCE, how ever the bad rows are not getting transferred.------------------------I think, therefore I am - Rene Descartes |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-03 : 16:04:20
|
| Didn't understand your requirement clearly? How will you get error in Source table(staging) in SSIS? Did you create destination table with same datatype ? Also where you want to capture error? |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-10-03 : 16:09:35
|
| 0.My oledb source is not a table. It is a query.1. Staging and destination tables have same number of columns and same name. 2. However at staging all the columns have VARCHAR data type3. I am using a tsql query at the source. The query uses convert function to convert as per the destination columns. 4. I want to capture the error at source.------------------------I think, therefore I am - Rene Descartes |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-03 : 16:19:46
|
| The question is : How SSIS will know you have bad rows? You need to have some lookup table to determine? |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-10-03 : 16:25:51
|
| Convert function raises error - for e.g. when ever it tries to convert character to int. I want these rows to get captured.------------------------I think, therefore I am - Rene Descartes |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-03 : 16:37:11
|
| http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101225 |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-10-03 : 17:01:28
|
| Ok; One last question. 1. My source table has varchar for date info. 2. Target tables has proper datetime data type3. Where can i use the CONVERT function?------------------------I think, therefore I am - Rene Descartes |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-10-03 : 17:08:45
|
| no. The text file is from tera data. It has a different date format, that i snot supported in SQL Server.------------------------I think, therefore I am - Rene Descartes |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-10-03 : 17:17:57
|
| 1. The staging table is a wide table. 2. all the columns in staging table are VARCHAR3. when I use CONVERT it fails many time at different places. 4. How to capture these errors. 5. I don't mind NOT using SSIS. ------------------------I think, therefore I am - Rene Descartes |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-10-03 : 17:25:44
|
| 3. Lets say I have 100 datetime coulmns in the table. I am trying someting like SELECT CONVERT(datetime,col1,111).......100 cols from tb_name I get a generic error something like - can not convert to datetime. Now i don't know which of the 100 columns caused this problem. Hope it is clear now. ------------------------I think, therefore I am - Rene Descartes |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-10-03 : 17:35:01
|
| In fact I started like that. It is really monotonus and scary. The table has around 300 columns. It is DW table. 45 million records. Single select takes a lot of time. The elimination process is the last thing i want to use. I was expecting SSIS to give some detailed errors.------------------------I think, therefore I am - Rene Descartes |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Next Page
|
Need help in Data import