SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS not importing zipcode data correctly into SQL from Excel


SSIS not importing zipcode data correctly into SQL from Excel

Author
Message
rons-605185
rons-605185
Old Hand
Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)

Group: General Forum Members
Points: 370 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?
rons-605185
rons-605185
Old Hand
Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)

Group: General Forum Members
Points: 370 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.
dmc-608719
dmc-608719
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1189 Visits: 787
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.
rons-605185
rons-605185
Old Hand
Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)

Group: General Forum Members
Points: 370 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.
dmc-608719
dmc-608719
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1189 Visits: 787
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.
rons-605185
rons-605185
Old Hand
Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)

Group: General Forum Members
Points: 370 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.
Attachments
BIDS Data Change.jpg (43 views, 220.00 KB)
BIDS Package Error.jpg (31 views, 35.00 KB)
dmc-608719
dmc-608719
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1189 Visits: 787
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)
rons-605185
rons-605185
Old Hand
Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)

Group: General Forum Members
Points: 370 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.
Attachments
SSIS Packages in SSMS.jpg (31 views, 70.00 KB)
dmc-608719
dmc-608719
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1189 Visits: 787
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.
P Jones
P Jones
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2876 Visits: 1524
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search