June 26, 2010 at 6:21 pm
Hi, I have searched around prior to asking.
I've written an SSIS package that takes addresses from a flat file and puts them into a table.
It has been working fine but I've run into a problem. A couple of Canadian addresses have been added that have letters in the zip codes. I can not change the type of field I'm using in the table right now which is set to numeric. The package fails when it comes across these two zip codes. For now I would like the package to just leave the zip code null for these two Canadian addresses.
Any suggestions on the best way to do this?
Another question I have is what task should I use to pick a path in the package based on the value in a file? The value will be 1 or 2. If it is 1 then go one way and if it is 2, go the other.
Thanks for the help. I learn so much on these forums.
Howard
June 28, 2010 at 1:29 pm
PHXHoward (6/26/2010)
Hi, I have searched around prior to asking.I've written an SSIS package that takes addresses from a flat file and puts them into a table.
It has been working fine but I've run into a problem. A couple of Canadian addresses have been added that have letters in the zip codes. I can not change the type of field I'm using in the table right now which is set to numeric. The package fails when it comes across these two zip codes. For now I would like the package to just leave the zip code null for these two Canadian addresses.
Any suggestions on the best way to do this?
Another question I have is what task should I use to pick a path in the package based on the value in a file? The value will be 1 or 2. If it is 1 then go one way and if it is 2, go the other.
On the first part, I think it's going to kind of depend on what the source looks like. If you don't have any dashes or anything non-numeric in the field for the records you want, you can just add a WHERE ISNUMERIC(yourField) = 1 to limit it. (The zip codes with letters will fail the isnumeric test.) Another way you can specifically avoid the records with letters is to use WHERE yourField not like '%[A-Z]%'
On the second part, it has been a while since I've used SSIS and I never did a ton with it, but I think I understand what you're trying to do. I know in 2005 that you could select the path the execution took based on success/failure AND a condition. (By default it's just set to success/failure.) I would set the condition as @someVariable = X, so you just need to set the value of a variable in scope based on that value in your file and then add the condition to your flow arrow.
June 28, 2010 at 4:05 pm
Thank you for responding bt. This is just what I was looking for. When can I put it in SSIS to take effect? I have a data flow task with a flat file source and a OLEDB destination. Does this condition go into a transformation task?
On the first part, I think it's going to kind of depend on what the source looks like. If you don't have any dashes or anything non-numeric in the field for the records you want, you can just add a
WHERE ISNUMERIC(yourField) = 1to limit it. (The zip codes with letters will fail the isnumeric test.) Another way you can specifically avoid the records with letters is to useWHERE yourField not like '%[A-Z]%'
June 28, 2010 at 4:14 pm
I tried to make a new derived column for the zip codes using this:
(ISNUMERIC([ZIP]) = 1 ? [ZIP]: "")
but it did not work.
June 28, 2010 at 4:27 pm
bt, this part worked perfectly. Thanks for helping me to get this set up!
On the second part, it has been a while since I've used SSIS and I never did a ton with it, but I think I understand what you're trying to do. I know in 2005 that you could select the path the execution took based on success/failure AND a condition. (By default it's just set to success/failure.) I would set the condition as @someVariable = X, so you just need to set the value of a variable in scope based on that value in your file and then add the condition to your flow arrow.
June 28, 2010 at 5:24 pm
PHXHoward (6/28/2010)
I tried to make a new derived column for the zip codes using this:
(ISNUMERIC([ZIP]) = 1 ? [ZIP]: "")
but it did not work.
I don't know the SSIS specific syntax for doing it there. If you're pulling from a SQL Server, you should just be able to change your source query to pull where that column is numeric. Otherwise (non-SQL source) your options would seem to be:
1. Figure out the syntax for SSIS if there is even a function to do this check there;
2. Figure out the correct syntax to eliminate the non-numeric records from your source; or
3. Use an intermediate table in SQL Server to temporarily hold your data and then move the data into your desired destination from there. (Doing so would allow you to use the ISNUMERIC function in the final transformation.)
I would think option #3 would be a last resort.
June 28, 2010 at 5:32 pm
My source is a flat file that has one row that does not conform to the numeric requirement of the database field. I'll keep working on a solution. Thanks for responding bt.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply