Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SSIS Truncation

Author  Topic 

vnsatishch
Starting Member

3 Posts

Posted - 2011-12-01 : 11:17:06
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

52326 Posts

Posted - 2011-12-01 : 11:41:39
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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-01 : 11:47:51
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

3 Posts

Posted - 2011-12-02 : 04:29:29
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

52326 Posts

Posted - 2011-12-02 : 04:33:19
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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-02 : 04:51:59
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

52326 Posts

Posted - 2011-12-02 : 05:47:21
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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-02 : 06:08:34
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

52326 Posts

Posted - 2011-12-02 : 06:12:00
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

3 Posts

Posted - 2011-12-06 : 11:03:19
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
   

- Advertisement -