SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SSIS Truncation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vnsatishch
Starting Member

India
3 Posts

Posted - 12/01/2011 :  11:17:06  Show Profile  Reply with Quote
hi,
i have problem with exporting records using SSIS, when i export record from one table to anthor table(two different servers) using SSIS so many records are missing.
the source table and destination table columns and data tyes are same.
1)call_Date datetime
2)originalcalledpartynumber varchar 50
3)callingpartynumber varchar 50
4)duration varchar 6
5)DateTimeOrigination varchar 50
but i am getting some warning messages in the SSIS when i run it manually like this
[OLE DB Destination [385]] Warning: Truncation may occur due to inserting data from data flow column "originalcalledpartynumber" with a length of 8000 to database column "originalcalledpartynumber" with a length of 50.
[OLE DB Destination [385]] Warning: Truncation may occur due to inserting data from data flow column "callingpartynumber" with a length of 8000 to database column "callingpartynumber" with a length of 50.

i am facing this problem from so many days,
please give me some solution,thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

India
52320 Posts

Posted - 12/01/2011 :  11:41:39  Show Profile  Reply with Quote
is it direct transfer or do you have some intermediate tasks like lookup? also is it pulling directly from source table or are you using a query as source?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/01/2011 :  11:47:51  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Have a look at the properties on the columns - source and destination (sounds like the destination).
It sounds like ssis couldn't get the values when the package was created and has defaulted to 50 - or maybe the table was changed after the package was created.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

vnsatishch
Starting Member

India
3 Posts

Posted - 12/02/2011 :  04:29:29  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

is it direct transfer or do you have some intermediate tasks like lookup? also is it pulling directly from source table or are you using a query as source?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






i am using one query as a source.its a direct SSIS package,no look ups between them.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52320 Posts

Posted - 12/02/2011 :  04:33:19  Show Profile  Reply with Quote
whats the query? does it have joins with other tables? check if you're getting whole records even after all the joins

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/02/2011 :  04:51:59  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
See my previous post. It is ssis that is doing the truncation - nothing to do wit the query (although that may also be an issue).
inserting data from data flow column "originalcalledpartynumber" with a length of 8000 to database column "originalcalledpartynumber" with a length of 50.

That comes from the package believing that originalcalledpartynumber in the destination is 50 characters. If you look at the properties of the destination you should find it there. Then the choice is to change the value manually or recreate the destination. It sounds like the package is simple so I would go for that first. Could be that you created the destination from the source and it has varchar(max) columns which have been converted to varchar(50) - then you altered the destination table but the package is still there with varchar(50) metadta. Right click on the destination and sho advanced editor then look at the column properties.


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52320 Posts

Posted - 12/02/2011 :  05:47:21  Show Profile  Reply with Quote
sorry I didnt understand how that will cause ssis to miss some rows altogether from source (unless I'm missing something). I agree that truncation can occur in above case but whatever op posted was a general warning thrown by ssis. That wont have an issue as long as no data beyond 50 comes and when it comes it will cause the package to fail which also hasnt happened as per OPs post above. The only case I guess when it can happen due to truncation is when you've set on error property as Ignore failure which is not the default property either!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/02/2011 :  06:08:34  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Sorry - I got caught up with the warnings - forgot about the underlying issue.

Still think it is a mismatch between the source and destination though. May the destination was created by the package which has varchar(max) columns which got changed to varchar(5) in the package.
Now when the package runs it truncates the data but also gets confused - maybe due to overflows or invalid characters in the data and misses rows - I've seen that happen from files a lot (and the number of rows imported is often incorrect without an error) but not from a query.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52320 Posts

Posted - 12/02/2011 :  06:12:00  Show Profile  Reply with Quote
quote:
Originally posted by nigelrivett

Sorry - I got caught up with the warnings - forgot about the underlying issue.

Still think it is a mismatch between the source and destination though. May the destination was created by the package which has varchar(max) columns which got changed to varchar(5) in the package.
Now when the package runs it truncates the data but also gets confused - maybe due to overflows or invalid characters in the data and misses rows - I've seen that happen from files a lot (and the number of rows imported is often incorrect without an error) but not from a query.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.


Ok..thats fine but in that case would we have something in log/progress bar to indicate that it has missed so many rows/failed validation or something?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

vnsatishch
Starting Member

India
3 Posts

Posted - 12/06/2011 :  11:03:19  Show Profile  Reply with Quote
hi guys,

thanks for your replies.

here i changed source as one table ,now the source and destination tables have same column names and same data types.And after changed the table sructure everytime i am changing the package as well.
Eventhough so many records are missing.
I did n't set the error property as ignore failure,its having Default one as 'Fail Component'.I did not change anything wrto this.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000