Data not uploading
It is probably due to the length limitation somewhere else, what else is there in your current test package?Arthur My Blog
January 15th, 2011 6:50am

File sent.
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 6:57am

The test package is completely bare bones: one Data Flow Task, containing a Flat File Source and a Flat File Destination; and the 2 Connection managers.
January 15th, 2011 6:59am

Hello Irene, Did you check your source file , because i used the same sample after formating with tab and was able to get everything as you posted in your first post. Please check the source file manually for first 4,5 rows , make sure header is also formated well. Yes I used these two numbers are different columns. Thanks
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 7:00am

I just tried that, but it doesn't help. Data uploads into all the columns except that one. Is there a tool (other than Data Viewer) I could use to see what is happening as the package executes? I looked in the Input and Output Properties tab of the Advanced Editor for OLE DB Destination (a suggestion I found on the web), but found that the datatypes are the same for both External Columns and Input Columns.
January 15th, 2011 7:03am

File sent. I checked it , and everything is correct with the sample file you sent. I think you did something wrong. could you try to drop destination, and use recordset destination with an object type variable, add a new data viewer and let us know if you got any progress or not.http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 7:08am

could you try to load it into another flat file destination without any change in character or dt-str or dt-wstr ? please let us know the resulthttp://www.rad.pasfu.com
January 15th, 2011 7:11am

I deleted the package and started over (for the third time). I must be making the same mistake each time, because the problem just doesn't go away. Of the 8 columns with DataType = Unicode String [DT_WSTR], why would just one of them have a problem? Is there something other than DataType and OutputColumnWidth that I should be looking at?
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 7:21am

I replaced the OLE DB destination with a recordset destination (with an object type variable) and built a new data viewer. Everything uploaded except that one column.
January 15th, 2011 7:30am

Delete the old File connection, Create a new one and see if this will solve the issue. Sometime You need to Refresh the filed, there is Refresh button when you create File Connection. Thanks
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 7:41am

I could really use a fresh pair of eyes on this. I built a package that uploads data from a tab-delimited text file to a 9-column table. This is the test file: 205304066 11111111 JONES ,ONE U GSR 2011-01-01 00:00:00 Min I ZZ 205304090 22222222 JONES ,TWO U GSR 2011-01-01 00:00:00 Min I ZZ 205304116 33333333 JONES ,THREE U SUR 2011-01-01 00:00:00 Maj I ZZ 205304124 44444444 JONES ,FOUR M MED 2011-01-01 00:00:00 Maj I ZZ When I run the package, everything uploads except column 2. The Flat File Connection Manager Editor shows all the data (including that in column 2) in the Columns and Preview windows. The Advanced window shows ColumnDelimiter = Tab, OutputColumnWidth = 8, TextQualified = False and DataType = Unicode string [DT_WSTR]. Except for the column width, all the other string values have the same definitions. I put a Data Viewer in the Data Flow between Flat File Source and OLE DB Destination - it shows column 2 as empty. I have never seen this happen before. Can anyone offer suggestions on what to try next? Thanks, Irene
January 15th, 2011 7:49am

I have an idea: how about you try to create the same package on another machine? Let's see if it will work there.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 7:52am

I loaded from Flat File Source to Flat File Destination, leaving all the column settings unchanged. Again, that one column did not load.
January 15th, 2011 8:02am

How the destination column #2 is defined in the database table?Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 8:06am

could you see the column 2 data in PREVIEW section of flat file source?http://www.rad.pasfu.com
January 15th, 2011 8:08am

nchar(8), null
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 8:08am

Yes, it was there.
January 15th, 2011 8:10am

Hello Irene, I am curious what locale your machine is at? Now, just for the sake of a simple test: could you text-qualify the 2nd column with the " (double quotes) and modify your Flat Data Source to reflect that change please? Then try loading the data again.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 8:11am

Please change the definition of the column #2 in the Flat File Source to DT_STR (the OLEDB is not Unicode agnostic). Retry the upload.Arthur My Blog
January 15th, 2011 8:21am

Hello, the test data you have provided is not tab delimited, Please make sure your data is tab delimited. I used the same data after correcting tab and it worked. Here is corrected data and it works perfectly 205304066 11111111 JONES ,ONE U GSR 2011-01-01 00:00:00 Min I ZZ 205304090 22222222 JONES ,TWO U GSR 2011-01-01 00:00:00 Min I ZZ 205304116 33333333 JONES ,THREE U SUR 2011-01-01 00:00:00 Maj I ZZ 205304124 44444444 JONES ,FOUR M MED 2011-01-01 00:00:00 Maj I ZZ Note: data Changes here during pasting , so please make your data tab delimited manually Thanks
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 8:22am

I tried a different workstation, but got the same result. I also built a new package and, this time, left all the string values as data type string [DT_STR]. An error appeared immediately, stating that the values in columns 1, 2 and 3 could not be converted (unicode/non-unicode). All 3 columns are nchar or nvarchar in the database table, but other columns are also nchar or nvarchar and they did not produce an error. Stepping back from the immediate problem, can anyone explain how SSIS determines the datatypes in a TEXT file? Is a tab-delimited text file treated any differently? Text files are WYSIWYG - right? No hidden control characters? Thanks for all the help so far...
January 15th, 2011 8:23am

I deleted and rebuilt both connection managers, but the problem persists.
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 8:28am

I tried something different. In the Flat File CM, I changed the data types of all the text columns to String [DT_STR]. Then I inserted a Data Conversion between the source and destination in the Data Flow, and converted the data types there. This time, there is red X on the OLE DB destination icon, telling me that my column "cannot convert between unicode and non-unicode string data types." What does this mean?
January 15th, 2011 8:33am

I think this trick may help you: use the Data Export Wizard to upload your file, save as a SSIS Package to examine how it did that.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 8:33am

remote whole flat file connection manager/source , and destination and create them again and map again, tell us if you have problem yet.http://www.rad.pasfu.com
January 15th, 2011 8:37am

Try to drop the data conversion step and try to upload then. The error is because probably you convert the values to Unicode and the OLEDB is not Unicode tolerable.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 8:39am

Hi Arthur, I am in the USA (California). Test 1: I added the double quotes around the column 2 flat file entry and modified the Flat Data Source to reflect that. The transfer failed due to truncation on that column. I changed the "On Truncation" setting to "Ignore" and ran it again. That column is still missing in the destination flat file. Test 2: In the connection manager for the source file, I changed the OutputColumnWidth from 8 to 10 (for the double quotes?) and changed the "On Trucation" setting back to "Fail". It made no difference.
January 15th, 2011 8:43am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics