Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS not importing zipcode data correctly into SQL from Excel Expand / Collapse
Author
Message
Posted Thursday, September 10, 2009 3:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 9:00 AM
Points: 44, Visits: 162
Using SSIS under MS SQL 2005 Server SP2. Using SSIS to create a package that takes data from an Excel 2000 spreadsheet and imports the data into a SQL table. Got everything to run okay except for the zip code field. In Excel the zipcode column is formatted as Special -> ZipCode. Data looks correct in Excel (Example: 06123). Run the SSIS package and look at data in SQL table. Data for zipcode shows as 6123 the first zero is missing.
Changed format of zipcode column in Excel to text. Data looks correct in Excel, run package, same results in SQL table - 6123.
Changed format of zipcode column in Excel to Number with 0 decimals. Data looks correct in Excel, run package, same results in SQL table - 6123.

How do I get SSIS to import the zipcode data correctly into the SQL table so the zipcode is the correct 06123?
Post #786047
Posted Friday, September 11, 2009 6:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 9:00 AM
Points: 44, Visits: 162
Added information: the datatype of the zip code field in the SQL table is nvarchar(15), null.

Just need to get the correct zip code with 0 as first digit imported into the SQL table. Zip Codes without the 0 as first digit are being pulled into the SQL table correctly.
Post #786304
Posted Friday, September 11, 2009 10:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:22 AM
Points: 375, Visits: 765
Look at the data type for the SSIS component (source output) to see the data type. If you just used the default it probably read the xls data type as int and therefore is dropping the leading zero. Change this components data type to varchar() like the SQL table data type and that should correct it.
Post #786535
Posted Friday, September 11, 2009 10:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 9:00 AM
Points: 44, Visits: 162
dmc (9/11/2009)
Look at the data type for the SSIS component (source output) to see the data type. If you just used the default it probably read the xls data type as int and therefore is dropping the leading zero. Change this components data type to varchar() like the SQL table data type and that should correct it.


I am new to SSIS. How and where do I change the data type for the SSIS component as stated above. Can you give me the procedure to do this (i.e. menus/navigation)?

Thanks.
Post #786545
Posted Friday, September 11, 2009 10:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:22 AM
Points: 375, Visits: 765
In SSIS you should have a connection manager to your XLS document. Double click on that connection manager to open it. Click adavanced on the left hand colum, and then you will see all the columns and their properties on the right pane. Find the column for zipcode and check the data type and change as neccessary.
Post #786553
Posted Friday, September 11, 2009 2:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 9:00 AM
Points: 44, Visits: 162
Still having issues.
1) Open Visual Studio/BIDS and open project. Open Data Flow tab and right click on Source-Sheet1$ object
2) Click on Show Advanced Editor
3) Select Input and Output Properties
4) Open Excel Source Output -> External Columns
5) Find Zip Code field and looked at Data Type Properties
Date Type = double-precision float [DT_R8]
6) Changed data type to Unicode String [DT_WSTR] with length of 15
7) Repeated same data type change for Excel Internal Columns for same Zip Code field.
8) Saved changes
See attached JPG file
9) Executed Package
10) Get Package Validation Error: Error at Data Flow Task [Source-Sheet1$[1]]: The output column "ZipCode" (61) on the error output has properties that do not match the properties of its corresponding data source column.

VS_NeedsNewMetaData

See 2nd attached JPG file with error message details.

11) Verified that Excel Internal and External Columns (Zip Code field) had same datatype = Unicode String [DT_WSTR] with length of 15.

12) Edited Output Columns -> Zip Code field and changed DataType to Unicode String [DT_WSTR] with length of 15. Get error message immediately that Property value is not valid. Click on Details button and get message of:

Error at Data Flow Task [Source - Sheet1$ [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "ZipCode" (61)".

Error at Data Flow Task [Source - Sheet1$ [1]]: Failed to set property "DataType" on "output column "ZipCode" (61)".


So you can change the datatype for Internal and External Columns but can't change the Output columns. The error/validation messages seem to state that all 3 types of columns need to be changed.

What am I doing wrong?

I just want to get the zip code field to be a non-numeric datatype.





  Post Attachments 
BIDS Data Change.jpg (15 views, 220.74 KB)
BIDS Package Error.jpg (11 views, 35.47 KB)
Post #786712
Posted Monday, September 14, 2009 10:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:22 AM
Points: 375, Visits: 765
I am getting the same error. I thought I had done this before, but it may have been a text/csv data source versus xls. You may be able to use an alternate method to achieve the results you need. Look at using a derived column transformation to pad 0's back on to that colum using stuff or some other type of function since you know the data type of the column. Not the prettiest, but if no one else knows how to resolve the issue it may be your best course of action.

For example something like right('00000' + zipcode field, 5)
Post #787616
Posted Monday, September 14, 2009 3:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 9:00 AM
Points: 44, Visits: 162
Tried several different methods and found out that part of the problem was using the Microsoft Excel (driver) as the data source. I switched to using the Flat File data source and was able to import the data (relatively) correctly into the SQL table. The zip codes definitely are now correct.

Now have 2 issues:

1) Using the flat file data source to pull the data from the tab delimited txt file (source file). There is a column called CompanyName which has some entries like: Smith Mfg. Co., Inc. which pulled into the SQL table as: "Smith Mfg. Co., Inc." with the quotes. Now need a way to go through the CompanyName field in the SQL table and eliminate the quotes. Used the Replace function as follows:

UPDATE dbo.IMP_tblImport
SET CompanyName = REPLACE(CompanyName,'"',' ')
WHERE CompanyName LIKE '%"%'

This eliminated the quotes but now but a space in the beginning of the company name where the quote was (I am assuming the a blank is also now at the end of the companyname where the ending quote was also).

Now how do I get rid of the beginning (and ending) spaces. I thought I could use the LTrim with the Replace but can't seem to get the coding correct. Tried the following:

UPDATE dbo.IMP_tblImport
SET CompanyName = REPLACE(CompanyName, ' ',(ltrim(CompanyName)))
WHERE CompanyName LIKE ' %'

Got the following error:

Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.
The statement has been terminated.

So that didn't work. Any suggestions on coding one SQL statement that would eliminate the begining/ending quotes and not leave any spaces/blanks?

Start: "Smith Mfg. Co., Inc." End result: Smith Mfg. Co., Inc.

2) Second issue is that I am using Visual Studio/BIDS to create my SSIS package. I thought that when I renamed and saved my SSIS package that the package would appear in the SQL Server Management Studio as Stored Packages -> MSDB (when logged into SSMS as Integration Services).

This latest package I saved does not appear in the Object Explorer. So how do I run the package from SSMS without having to go into Visual Studio/BIDS?

See attached JPG file.

Thanks.



  Post Attachments 
SSIS Packages in SSMS.jpg (7 views, 70.66 KB)
Post #787774
Posted Tuesday, September 15, 2009 7:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:22 AM
Points: 375, Visits: 765
Glad the text driver worked, as I said I knew that worked but I thought I had also done it with the excel driver.

You should be able to trim by simply doing something like

Update TableX
Set FieldY = LTRIM(FieldY)


AS far as seing packages in the Stored Packages area in SSMS you need to deploy them there first to be visible. One of the easiest ways it to connect to integration services from SSMS, expand stored packages, and thee file system or msdb (depending on where you want it) and right click and select import package. At that point follow the wizard, as there is too much to explain each step and option.
Post #788126
Posted Wednesday, September 16, 2009 5:10 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, August 21, 2014 4:21 AM
Points: 561, Visits: 1,168
Excel is a pain as it chooses the data type based on the data in the first ten or so rows so US zip codes look like numbers unless you edit the excel to include a quote in front.

If using flat file import, you can edit your flat file connection manager and set both delimiter (comma usually) and then text qualifier as " so it strips any quoted text.
Post #788834
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse