OK, I got the SQL steps all running as you suggested and boy doesn't that make life easier. I wonder if I could take it to the next level?
Is there a way that I can do the Excel part as well? This might be out of the arena of this forum but I shall ask it anyway as you seem to know your stuff. Here is the environment that I am working with
1. Every Friday I get sent a file that contains a load of data. The file name is not the same, although I can get it to be the same if that eases this process.
2. This text file is then opened in Excel and the following changes are made;
-- Column I - Number 0 decimal places
-- Column JKL - Number 2 Decimal
-- Column M - Date - dd/mm/yyyy
-- Column N - Number 2 Decimal
-- Column O - Number 0 Decimal
-- Column PQRST - Custom - dd/mm/yyyy
3. Column T1 has the value of 'Data Date' placed in it and then the date of the data being worked on added to every following row (approx 6,700 rows)
4. This is then saved as a file called latestdata.csv in a specific non changing directory on the SQL server.
5. Once saved the process of automation that you suggested takes over and completes the final steps to bring it into a SQL server table.
I guess possibly, bearing in mind that this is only data manipulation, that this conversion and saving of data could be done as part of the transformation that occurs in the automated steps. My question would then be I guess, how do I specify the format as part of the DTS and how do I get it to enter the date for the data that goes in column/row T2 onwards. Is there some way that the user could be prompted to enter the date that would be autofilled into the remaining rows?
I think you see where I am going with this. If I can do this for this package then I can apply the same procedures with modifications to other massive amounts of data imports I do on a weekly/monthly basis.
Thanks in Advance