I have limited experience with SSIS. I have only ever designed the one project, whose purpose is to copy several tables from a SQL Server database hosted by my ISP to a MS Access database on my own PC. This project has worked correctly in the past, but not since I changed the length of a text column called "Payment Comments" from 120 to 200 on both databases. Despite modifying the relevant data conversion setting to 200 in SSIS, I get the following error: Warning: Truncation may occur due to retrieving data from database column "PaymentComments" with a length of 200 to data flow column "PaymentComments" with a length of 120. The only way I could get the transfer of the associated Payments table to work was to set "Ignore failure" for PaymentComments, which does not seem like an elegant solution. Where might I be going wrong? David

What you need to do is tell all parts of SSIS that touch that column that the metadata (size) has changed. So when you open the package, and open the data flow, you probably opened the Source editor. It would have told you that column metadata had changed, and you said "of course" and allowed it to fix things up. However, it didn't fix everything up - you know that because you changed your Data Conversion component a little further on to keep the 200 character length. What you need to do is double-click every component that follows the source (in order) in order to propagate the change. SSIS makes you do this because it doesn't think it's obvious that you'd want this to happen. In real life, you almost always do want these things to cascade through the whole process though. Keep editing components until you get to the destination. Then it ought not to complain. Talk to me now on

Hi Todd, Many thanks for your response. I don't remember any automated "fixing-up" option being offered by the Source Editor, but my memory is not very reliable. There are only 3 relevant Data Flow boxes, Source, Data Conversion and Destination. I have double-clicked them all in turn but I still have the warning triangle in the Source box. I can also see nothing in the Destination Editor for configuring character length. Any more suggestions? David

Another option is to "flush" the metadata. Enter the following query in the OLE DB Source (take a copy of your old query): SELECT a = 1 This will replace all the metadata in your pipeline. Then insert your old query, and open up every component in the dataflow. Always click 'OK'.MCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...

What Koen suggested - or right-click on the Source, select Advanced Editor, hit the "refresh" button, then OK. Does the same thing... Talk to me now on

Hi Koen, I don't understand what you mean by "Enter the following query in the OLE DB Source". Please clarify. However, if Eileen is correct in saying that "right-click on the Source, select Advanced Editor, hit the Refresh button, then OK. Does the same thing...", then it is not going to help. I tried Eileen's suggestion but it made no difference. David

Open the OLE DB Source editor. In the dropdown box, select SQL command. (I do hope you use SQL statements to retrieve your data). Enter the statement that I mentioned above and click OK. All metadata is now refreshed. Go back in the OLE DB Source editor and change it back to the original SQL statement. Metadata changes again. Open up every component's editor in the dataflow. If you get a pop-up complaining about unmatched columns, select the delete columns from mapping option and proceed.MCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...

Please forgive my ignorance, but where is the "OLE DB Source editor"? I can't find any reference to such a thing in the Help index. Perhaps I used it when I first created my one and only DTSX file, but that was about 2 years ago.

Please forgive my ignorance, but where is the "OLE DB Source editor"? I can't find any reference to such a thing in the Help index. Perhaps I used it when I first created my one and only DTSX file, but that was about 2 years ago. You do have a source right? A little square box at the beginning of your dataflow. Since you extract data from SQL Server, I assume you used an OLE DB connection, hence an OLE DB Source. If you double click the little squared box, you arrive in the OLE DB Source editor. For your information, here's the help file: http://msdn.microsoft.com/en-us/library/ms183570.aspxMCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...

Ah, that source editor! When I opened that dialogue box earlier I hadn't noticed its full title. I was also put off the scent by your references to SQL queries as I was pretty certain I had never written any queries in SSIS. Now that I am looking at the OLE DB Source Editor, I can see that the Data access mode drop-down box offers an 'SQL Command' option but my setting is simply 'Table or view' and below that there is another drop-down box where I have selected [dbo].[Payments]. Is there a problem with using SSIS in this manner? I have run 'Select a = 1', as you suggested (what does this do?) and this has removed the warning from my Source box. However, I now have red blobs with a white cross on my Data Conversion and Destination boxes. Apparently, I now have 'validation' errors, which I am now about to investigate ... David

The dropdown box is quite an inefficient way to select data from the source, as it takes all the columns while it is possible you don't need every column of the table. Behind the scenes, the way the dropdownbox retrieves the data from the database is also less performant than a normal SQL statement. On-topic: you've now refreshed the metadata of the dataflow, as SSIS now thinks there's only one column named "a". The components don't recognize this column (they have references to the old columns), so that's why you are getting errors. When you switch back to the original SQL statement (or dropdown box, if you must), the latest metadata is retrieved from the database. You need to propagate these changes through the dataflow by opening every component.MCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...

Oops. You did tell me in your previous post to switch back to the original setting in the Source Editor but I forgot! Switching back to my table selection (for this particular purpose, I do want to copy all the columns) and reconfirming all the column mapping has now fixed everything. Many thanks for your help. It is much appreciated. David

Hi Koen, I don't understand what you mean by "Enter the following query in the OLE DB Source". Please clarify. However, if Todd is correct in saying that "right-click on the Source, select Advanced Editor, hit the Refresh button, then OK. Does the same thing...", then it is not going to help. I tried Todd's suggestion but it made no difference. David