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:
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:
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.