I'm testing a very simple SSIS package in which I have an XLSX source (connection is OLE DB Source) with a Multicast destination. I have a data viewer set in between the two transformations. There is 1 column in the Excel source file with a data type of General that has a maximum of 30 characters. Upon executing the package, I copied the data from the data viewer and noticed every value in the column in question has a length of 35 characters. So SSIS is adding trailing spaces to whatever value is in the column. For example: "abcdef" becomes "abcdef " Any ideas as to why those trailing spaces are being added? I checked the source file and the trailing spaces are not there. Please let me know if I can provide any additional information.

It is the data viewer that does that, as far as I remember, the data does not get affected.Arthur My Blog

Thanks for the reply Arthur. I tested that theory by inserting the records into a database table instead of just using a Multicast as my destination. The trailing spaces appear in the table, so the data is affected in my case.

In my attempt to resolve the issue, I came across something that confuses the issue more for me. I opened the original source file and simply highlighted the sheet name and the clicked outside (no actual change made although Excel asked me if I wanted to save the file when I closed it). I saved the file, executed the package again, and there were no trailing spaces added. I then deleted the file and ran the package against the original file again (unopened or saved manually). Again, the trailing spaces were added. So I'm wondering what effect opening the file and saving it had even though I didn't actually make any changes?

How did you define the data source metadata? I think I recall this mentioned once, I am under an impression Excel needs to be put into the so-called import mode to freeze its data, please revise the connection properties (advanced tab) to add this directive to its connection string ;HDR=YES;IMEX=1Arthur My Blog

I tried adding ;HDR=YES;IMEX=1 to the connection string, but it did not resolve the issue.

I think we sweat a small thing too much, how about we close this nuance with you adding a SSIS Expression in a Derived Column Transformation to trim off the trailing spaces? E.g. TRIM([MyColumn]) Arthur My Blog

I agree using the TRIM function is a fine workaround for most cases. However, in my particular scenario, there may be times when a trailing space is intentional, and therefore, the TRIM function doesn't meet my needs.

For debugging purpose , can you put one derived column after source and find the length of the string to a seperate column and check once manually.... why becoz this i am using ssis for 3 years , i had never face this kind of isssue.. and one more thing , check the metadata of oledbsource some times the default values like string[50].. like that will be assigned.l in this case you have go to advance editor and change the length appropriately..

Kingxxx1, I added a derived column after the source to get the length. For each record in the file, the value in the derived column is 35. The max length should be 30, so SSIS is always adding anywhere from 5-34 trailing spaces. As for the metadata of the column in oledbsource, it's showing a DataType of Unicode string [DT_WSTR] with a length of 255. So I don't think that sheds any light on the issue. I would share the source file but it contains some sensitive information. And as I noted above, if I open and save the file (making alterations or not), it seems to resolve the issue. So it seems to be something with the original state of the file that SSIS is not reading properly.

Kingxxx1, I added a derived column after the source to get the length. For each record in the file, the value in the derived column is 35. The max length should be 30, so SSIS is always adding anywhere from 5-34 trailing spaces. As for the metadata of the column in oledbsource, it's showing a DataType of Unicode string [DT_WSTR] with a length of 255. So I don't think that sheds any light on the issue. I would share the source file but it contains some sensitive information. And as I noted above, if I open and save the file (making alterations or not), it seems to resolve the issue. So it seems to be something with the original state of the file that SSIS is not reading properly.