September 10, 2009 at 3:25 pm
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?
September 11, 2009 at 6:43 am
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.
September 11, 2009 at 10:11 am
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.
September 11, 2009 at 10:24 am
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.
September 11, 2009 at 10:33 am
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.
September 11, 2009 at 2:38 pm
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.
September 14, 2009 at 10:54 am
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)
September 14, 2009 at 3:11 pm
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.
September 15, 2009 at 7:24 am
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.
September 16, 2009 at 5:10 am
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.
September 16, 2009 at 6:52 am
I will try the last suggestion of editing the flat file connector and modifying the delimiter and text qualifier settings.
In the meantime, I have a couple of other questions:
1) How do I include the replace/trim sql scripts in the SSIS package? In other words I want to have the SSIS package run the scripts to remove the quotes as part of the package process. Where and how is this done in SSIS?
2) Can a SSIS package be executed from within a SQL script? I would like to use a command button in my application that would run a SQL script or utility that would locate, call and execute the SSIS package. Can this be done and if so, how?
Thanks.
June 5, 2013 at 8:00 am
Manually edit your excel connection string and add "IMEX=1", it tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply