Flat File Conn Mgr not converting

Last Post 28 Jun 2007 10:04 AM by sschmid4. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sschmid4
New Member
New Member

--
12 Jun 2007 12:40 PM
I have a .csv file that I am trying to create an import package for. I have created a drop table and create table statement in the control flow. In the data flow I then create a Flat File Source which uses a Flat File Connection Manager. The .csv file has no header row and cannot be read by the connection manager when I check the unicode Checkbox. So I have entered each data type in the flat file connection manager to match the create table statement in the control flow statement. However the OLD DB destination task is not recognizing the flat file data. When I preview data in the OLE DB task, I get a blank set of results. I have mapped the Column0 - Column15 columns in the .csv file to the named columns in the existing table. Yet the mapping does not seem to be catching on. When I try to run the package, I get the error that Column13 may be truncated since it's length is 22 characters and the destination table is 10 characters. However I checked the package and I cannot find anywhere where I have marked column 13 to be a 22 character length.

Do I need a 3rd data flow task in between the Flat File Source and the OLE DB Destination?
sschmid4
New Member
New Member

--
13 Jun 2007 06:38 AM
the max length is 10 characters in the .csv file. that is why i used that value in the flat file connection manager. To overcome the fact that I must designate the .csv file as non-unicode, I have since changed all the nvarchar data types in my destination table to varchar. All data types in the flat file manager are set to "string [DT_STR]".
sschmid4
New Member
New Member

--
27 Jun 2007 07:07 AM
I agree that Unicode uses 2 bytes. That is why I changed it to non-unicode. What I cannot figure out is why the mapping is not working. Is there something else I could explain to help diagnose this problem? I thought the adjustments I had made to the mappings would have solved the issues. Perhaps I need another connection manager for the flat file?

Beyond this issue, what site/book/resource do you recommend, beyond BOL tutorials, to help learn SSIS?
sschmid4
New Member
New Member

--
27 Jun 2007 12:33 PM
I will definitely check out the SSIS version of the site. Regarding my current issue, I have to use non-unicode characters for the .csv file that is created. When I attempt to use unicode in the Flat File connection manager, it does not read the rows of data from the .csv file at all. So I have to un-check the unicode checkbox to get the flat-file connection manager to recognize the rows of data. However it seems that the table I create with the SQL task create table statement automatically assigns unicode characters. Here is the create table statment I am using:

USE [EN_NEWS_DISTRIBUTION]
GO
/****** Object: Table [dbo].[EN_MEMBERS] Script Date: 06/12/2007 13:50:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EN_MEMBERS](
[ID] [int] NULL,
[TYPE] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FIRST] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MIDDLE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LAST] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SUFFIX] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DEGREE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BUSINESS1] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BUSINESS2] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADDRESS1] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADDRESS2] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADDRESS3] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CITY] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[STATE] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ZIP] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[COUNTRY] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EN_MEM_FLS] AS ((rtrim(isnull([FIRST],''))+rtrim(isnull([LAST],'')))+rtrim(isnull([STATE],''))),
[EN_MEM_FML] AS ((rtrim(isnull([FIRST],''))+rtrim(isnull([MIDDLE],'')))+rtrim(isnull([LAST],''))),
[EN_MEM_FILS] AS ((rtrim(isnull(left([FIRST],(1)),''))+rtrim(isnull([LAST],'')))+rtrim(isnull([STATE],''))),
[EN_MEM_FIML] AS ((rtrim(isnull(left([FIRST],(1)),''))+rtrim(isnull([MIDDLE],'')))+rtrim(isnull([LAST],'')))
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

What I am doing here is simply copying the Script As Create Table statement that is created in Management Studio and pasting it into the SQLStatement property of the SQL task I use to create the table. Is there some other setting I need to be using when creating the table to get the table in a non-unicode format? Or do I need to add an intermediary step after collecting the .csv file to transform it into a non-unicode format before trying to load it into the sql table?
sschmid4
New Member
New Member

--
28 Jun 2007 10:04 AM
OK, I have recieved the following error when I try to run the package.

Data conversion failed. The data conversion for column "ID_Flat" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

I had changed the flat file ID heading to ID_Flat to differentiate it from the destination column ID field.

It appears that, because I set the create table statement to have a width of 10, I cannot use any of the integer data types available in the connection manager settings since the largest one is 8.

Hopefully this will correct things.
You are not authorized to post a reply.

Acceptable Use Policy