SSIS on Excel 2007 Data Import & Moss2007 :"Excel Source Output" . The column status returned was: "Text was truncated or one or more characters had no match in the target code page."

SSIS on Excel 2007 Data Import & Moss2007

One of the crucial stages in moving managers from the “Simple all I want I get from Excel” to a more centralized platform like Sharepoint is moving their data !

Well lately not only have I been a :

• “HTML”
• “SharePoint Designer 2007”
• “Adobe Photoshop CS2” & “Paint.Net” Graphics
• “General Specific Site Collection Moss2007 Admin”
• “InfoPath 2007 Browser Compatible Developer” (including InfoPath C# Code)
• “General Web Service & WCF Programmer”
• “Camtasia Studio Video Composer”
• “General Web Part Programmer”
• “Regular T-SQL Stored Procedure Developer”
• “Reporting Services 2005 & 2008 Guru”
• AND (!) “SqlServer2005/2008 Integration Services Package Developer”
• … Not forgetting the every day Project Management meetings, e-mails and all around needed Project Characterization Documents

Like every project manager the first thought is KISS – “Keep It Simple Stupid”. Well, if you’re like me and you know that this is a one time , or close enough, occurrence ( the import of data ) you can do what I did :

1. Open Access2007
2. Import the data from Excel
   
   
   
3. Save the file as Access2003
   
   
   
4. And from there on the SSIS Wizard ( Sql2008 in my case ) will do the work
   
   
   in the process ( in the steps of the wizard ) , you can always check the option to save a local copy of the Package ( without an encryption key ) – your choice :)

for those who still are looking for another solution , a more permanent one that is, here’s a good link

SSIS Excel Data Source: Error with output column “Comments” (5691) on output “Excel Source Output” (5596). The column status returned was: “Text was truncated or one or more characters had no match in the target code page.”

And yes

1. I know SSIS is more my sister’s ( Ella Maschiach's BI Blog ) area , than mine ;)
2. I needed that for an InfoPath2007 form, which always can connect to a Moss List ;)