Mr.Sahand (8/25/2014)
Thanks Phil for the answer.The rules are not too complicated to be done through T-SQL or Access query or VB scripting.
Although they are many in number. They are mostly related to the cleansing of data.
For the Access date time format as you would probably know there are some mismatches between data/time formats in SQL and Access. That is part of our problem.
I can not do that in Access side on the live data.
If I want to do that in SQL side, I guess I first have to store all those date time data into varchar fields
in sql tables, then modify them using those rules in an sp and then change the data type of all those varchar fields
to date time. That is a workaround comes to my mind. Would it be possible to this(your second suggestion) in a better way?
Also, could you please refer me to an article about SSIS global functions?
Thanks
I haven't worked much with getting data out of Access using SSIS. However, what I had in mind was the creation of clean-up functions in Access which can be used in Access views.
I was then hoping that these views could be used as data sources in SSIS. But it may be that the Access driver allows access only to the data itself - I just can't remember how it all fits together.
If we discount the above possibility, you can do it in SQL Server as you suggest - though I would not do it exactly as you describe.
Instead, I would create one or more staging tables to hold the imported data (with your dodgy dates as varchar(n) as you suggest).
Then, I would use T-SQL to get that data into your target table - possibly calling UDFs to take care of your 'global' code (taking care with performance - UDFs can be slow if implemented poorly).
Using 'global functions' in SSIS. Have a look here and you'll start to get the idea.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.