October 26, 2009 at 4:50 am
Hi,
I am importing data from a number of different companies
i.e products
We have specified that these should be csv with Text delimeter of ".. but some of the companies have come back and said that they hold the " in the data to denote inches and are not willing to change .
Is there a way in SSIS that I could dynamically set the text delimeter from " to ^ or other based on a setting in a table ( we already change the path for the csv file based on a variable) but can not see how we can do using the GUI ...Script task?
Regards
Ray
October 26, 2009 at 6:48 am
How about this? Set up 2 connection managers for the flat flat file one for " the other for ^ delimiter.
Then enable package configurtaions from the SSIS menu -> package configurations.
Add a "SQL Server Configuration" pointing it to a configuration table telling it which connection to use.
I guess it will involve a few changes for you but I think it will acheive what you want.
Hope this is useful
S C Penguin
October 26, 2009 at 7:30 am
topman
cheers will give this a go
October 26, 2009 at 11:16 am
GrassHopper ..never used this before and it is brilliant ...
Do these settings only get applied at package start or can you modify the properties of certain values within the SSIS_Configuration table (i.e you have a process that reads the file and checks the contents and then updates the SSIS_Config value for that filter)..
Would you need to refresh a connection or would you have to have an SSIS package that read the file and then call another SSIS package after updating the table..
October 26, 2009 at 2:59 pm
Hmm - well that's a bit more difficult. I use Configuration files a lot but I use XML ones. Typically I would run the same package multiple times from a SQL job, each time pointing to a different configuration file to process similar data from another source.
I'd be very suprised if you can't do a similar thing with SQL configurations - perhaps running from a parent package & upadating the table between runs of the child package....
Sorry I can't be any more help - hope this makes sense!
October 27, 2009 at 4:59 am
IMHO what you want is a consistent peice of code that will handle both situations, i.e. the file requires preprocessing.
If the qualifier is ^ then leave it alone and process as normal, but if the qualifier is ", then process and rewrite the file as ^ qualified...then you only have to write one set of code and one set of transformations.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply