nigerman
asked on
replacing truncated data, how?
Greetings all,
We just migrated data from Oracle database into SQL Server 2008.
However, since we were having problem using SQL Server dts for this migration, due to date, timestamp and other Oracle-SQL Server compatibility issues, we decided to use Oracle's SQL Developer tool to extract data from Oracle to .csv file and then to SQL Server.
Problem is that user just found out 3 weeks after conversion that some data was truncated.
There are a few comment fields with large character lenght that lost some data from Oracle to .csv file.
I am trying to fix this problem by just importing only those comment fields.
Is there a way, perhaps easier way to just add truncated data without having to delete entire table and reload data?
The user doesn't want to go this route because they have added so many new records since we went live.
Any ideas how to fix this?
Suddenly, my blood pressure has gone up due to this sudden discovery.
Thanks a lot in advance
We just migrated data from Oracle database into SQL Server 2008.
However, since we were having problem using SQL Server dts for this migration, due to date, timestamp and other Oracle-SQL Server compatibility issues, we decided to use Oracle's SQL Developer tool to extract data from Oracle to .csv file and then to SQL Server.
Problem is that user just found out 3 weeks after conversion that some data was truncated.
There are a few comment fields with large character lenght that lost some data from Oracle to .csv file.
I am trying to fix this problem by just importing only those comment fields.
Is there a way, perhaps easier way to just add truncated data without having to delete entire table and reload data?
The user doesn't want to go this route because they have added so many new records since we went live.
Any ideas how to fix this?
Suddenly, my blood pressure has gone up due to this sudden discovery.
Thanks a lot in advance
ASKER
Thanks very much for your prompt response.
This is where the issue is:
in this dataflow add oracle as datasource and sql server as data destination. and you must only select data from oracle table that is not in sql server table.
All data that is in Oracle is also in SQL Server. Only problem here is that the one in SQL Server is truncated. As a result, you see something like (this is just an example), My name is James Turi and I am...
The rest is truncated. Again that was an example.
Usually, the truncation starts after 255 character length is used up.
So, my desire here is to delete records in only the comment fields and replace them with complete data from the Oracle db but I am not sure the best approach.
This is where the issue is:
in this dataflow add oracle as datasource and sql server as data destination. and you must only select data from oracle table that is not in sql server table.
All data that is in Oracle is also in SQL Server. Only problem here is that the one in SQL Server is truncated. As a result, you see something like (this is just an example), My name is James Turi and I am...
The rest is truncated. Again that was an example.
Usually, the truncation starts after 255 character length is used up.
So, my desire here is to delete records in only the comment fields and replace them with complete data from the Oracle db but I am not sure the best approach.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes, some sense. I will attempt your suggestion as I am about to be kicked out of office for the rest of the day.
Maybe in an hour, I will try and let you know.
Thanks for your assistance
Maybe in an hour, I will try and let you know.
Thanks for your assistance
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad to help,
let me know if you have problem on this issue
let me know if you have problem on this issue
ASKER
acperkins,
This is a nice piece but it addresses sql server 2000 or earlier.
I suppose it doesn't matter whether it is 2000 or 2008 and I am using sql server 2008 and still having the same issue.
Second, the article references a package.
I don't have a package. I am just trying to do a one time import with dts.
reza_rad,
I will bet you have some pretty good experience with SSIS package.
I spent half of last night trying to use a package to dump the data just like you had suggested.
However, I got stuck on the data transfer section.
Recall, that you have 2 options, one to copy tables from source to destination.
The other to use query to transfer. Based on the article on how to use SSIS package to import data, I was instructed to use the Query option to transfer data.
When I got the box to formulate my query, I used the following:
SELECT * from [fstatus$]. The fstatus is the name of the excel file I was trying to import data from and I kept getting the following error message:
Sql statement is not a query.
I can use a text file if that is possible. I am just frustrated right now.
This is a nice piece but it addresses sql server 2000 or earlier.
I suppose it doesn't matter whether it is 2000 or 2008 and I am using sql server 2008 and still having the same issue.
Second, the article references a package.
I don't have a package. I am just trying to do a one time import with dts.
reza_rad,
I will bet you have some pretty good experience with SSIS package.
I spent half of last night trying to use a package to dump the data just like you had suggested.
However, I got stuck on the data transfer section.
Recall, that you have 2 options, one to copy tables from source to destination.
The other to use query to transfer. Based on the article on how to use SSIS package to import data, I was instructed to use the Query option to transfer data.
When I got the box to formulate my query, I used the following:
SELECT * from [fstatus$]. The fstatus is the name of the excel file I was trying to import data from and I kept getting the following error message:
Sql statement is not a query.
I can use a text file if that is possible. I am just frustrated right now.
don't select use query to transfer.
try this way ( this is simpler):
select copy data from one or more table and then in next window select the sheet you want to import data from
you can then click on edit mapping and set mapping column to what column you want in sql server table as below image
let me know if you have problem
1.JPG
try this way ( this is simpler):
select copy data from one or more table and then in next window select the sheet you want to import data from
you can then click on edit mapping and set mapping column to what column you want in sql server table as below image
let me know if you have problem
1.JPG
>>This is a nice piece but it addresses sql server 2000 or earlier.<<
No, it addresses DTS, regardless of whether you are using 2000, 2005 or 2008.
>>I don't have a package. I am just trying to do a one time import with dts.<<
That does not make any difference. It is still a DTS package.
But let me put it this way, either use:
1. DTS and implement the changes recommended in the link or
2. SSIS.
Pick your poison and we can help you from there. I was merely addressing your original question as to why it was failing and providing a solution.
No, it addresses DTS, regardless of whether you are using 2000, 2005 or 2008.
>>I don't have a package. I am just trying to do a one time import with dts.<<
That does not make any difference. It is still a DTS package.
But let me put it this way, either use:
1. DTS and implement the changes recommended in the link or
2. SSIS.
Pick your poison and we can help you from there. I was merely addressing your original question as to why it was failing and providing a solution.
ASKER
Thanks a lot reza_rad.
I don't think the mapping of columns is the issue. The issue is that the string length is smaller than the data from the excel file.
I know you suggested yesterday that I set the size as I want on the DB and I have done that but somehow, when I go into the mapping screen, I don't see the size I set on the db.
acperkins, thanks also for your continued assistance.
See sql 2008 no longer supports DTS and as you can see from comments above with reza_rad, i am struggling to get a handle on this SSIS thing.
I don't think the mapping of columns is the issue. The issue is that the string length is smaller than the data from the excel file.
I know you suggested yesterday that I set the size as I want on the DB and I have done that but somehow, when I go into the mapping screen, I don't see the size I set on the db.
acperkins, thanks also for your continued assistance.
See sql 2008 no longer supports DTS and as you can see from comments above with reza_rad, i am struggling to get a handle on this SSIS thing.
ASKER
Ok, some progress report:
using the SSIS package and your instructions reza_rad, I was able to transfer data.
However, I ran into one warning. Please see screenshot and let me know if you know what that means and how to resolve it. I can see the changes now in length increase. That doesn't appear to be an issue anymore.
warning.JPG
using the SSIS package and your instructions reza_rad, I was able to transfer data.
However, I ran into one warning. Please see screenshot and let me know if you know what that means and how to resolve it. I can see the changes now in length increase. That doesn't appear to be an issue anymore.
warning.JPG
>>See sql 2008 no longer supports DTS and as you can see from comments above with reza_rad, <<
Actually it does. But that is fine SSIS is a better option.
Actually it does. But that is fine SSIS is a better option.
ASKER
Thanks again but do you have any idea why I am having the issue that I attached and how to resolve it?
don't worry about that warning,
as it said clearly:
you have two columns that is not used in transfer data, you can remove them from transfer process if you don't need them at all.
if this doesn't make sense to you , provide a screen shot of your mapping columns window here
as it said clearly:
you have two columns that is not used in transfer data, you can remove them from transfer process if you don't need them at all.
if this doesn't make sense to you , provide a screen shot of your mapping columns window here
>>I know you suggested yesterday that I set the size as I want on the DB and I have done that but somehow, when I go into the mapping screen, I don't see the size I set on the db
I suggest to you this:
after generating your ssis package with import/export wizard , save this package ( you can do this in last steps)
then open it in BIDS
in BIDS you can go through data flow task
select data source with advanced editor
and then select appropriate length for your columns in transfer operation
I suggest to you this:
after generating your ssis package with import/export wizard , save this package ( you can do this in last steps)
then open it in BIDS
in BIDS you can go through data flow task
select data source with advanced editor
and then select appropriate length for your columns in transfer operation
ASKER
Ok, thanks a lot for your help and patience.
I am going through this process. It is a bit slow for me but I am making some progress and will update you again soon.
Again, many thanks.
I am going through this process. It is a bit slow for me but I am making some progress and will update you again soon.
Again, many thanks.
ASKER
I found this article that addressed the character issue I have been having:
http://support.microsoft.com/kb/q281517/
As soon as I followed it and changed the value for TypeGuessRows to 0, I am now able to import data from Excel into SQL Server successfully.
I basically imported the data into a temp table called statusTemp.
My hope now is to be able to replace the 4 comment fieldnames that have their data truncated with data from these newly imported 4 comment fieldnames.
Is there a way to do this?
I tried insert into status (field1,field2,field3,fiel
Of course i am getting an error that some fieldnames cannot have null values.
Will update work? If yes, any ideas???
Thanks a lot
http://support.microsoft.com/kb/q281517/
As soon as I followed it and changed the value for TypeGuessRows to 0, I am now able to import data from Excel into SQL Server successfully.
I basically imported the data into a temp table called statusTemp.
My hope now is to be able to replace the 4 comment fieldnames that have their data truncated with data from these newly imported 4 comment fieldnames.
Is there a way to do this?
I tried insert into status (field1,field2,field3,fiel
Of course i am getting an error that some fieldnames cannot have null values.
Will update work? If yes, any ideas???
Thanks a lot
ASKER
I have figured this out.
Thanks
Thanks
ASKER
They provided good assistance but they didn't help me.
However, I am truly grateful for their assistance.
B is really not a bad grade and doesn't mean that I didn't appreciate their assistance. I did and I do.
Thanks a lot
However, I am truly grateful for their assistance.
B is really not a bad grade and doesn't mean that I didn't appreciate their assistance. I did and I do.
Thanks a lot
for this you can create a ssis package
and add a dataflow task in it
in this dataflow add oracle as datasource and sql server as data destination. and you must only select data from oracle table that is not in sql server table.
you must have installed SQL server Integration service for working with SSIS packages