SQL code to perform a the functions described in the body of this question

Could somebody help with coding in SQL to perform the following...

check folder - \\cluster\data\Direxxis\Erie\incoming\
if there is a matrix file – eg. Erie_Creative_Matrix_20140403.xlsx
remove the date portion eg. _20140403
copy matrix file without the date portion to \\cluster\data\Direxxis\Erie\incoming\
    - but before changing the file name copy the original eg. Erie_Creative_Matrix_20140403.xlsx to

run dtsx (with new file name eg. Erie_creative_Matrix.xslx)

Thanks in Advance
Who is Participating?
Sreedhar VengalaConnect With a Mentor Sr. Consultant - Business IntelligenceCommented:
This is can be acheived within your DTSX (SSIS) package.  You can have a pre-load process wherein you can use Script Component and write bit of C# code to read the designated Directory and check if there are any files which contains file name with work 'Matrics', if exists split on '_' and assemble back with appropriate name. And then copy to destination designated directory.

Further in next step you can load your data.
magarityConnect With a Mentor Commented:
Isn't this sort of thing more suited to a DTS package?
tesla764Author Commented:
Hi Sreedhar,
  Could you tell me how to use the script component and give a working example of the c# code to read the designated directory to check for files have have the name "Matrix". And then the rest of the code you mentioned, if exists split on '_' assemble back with appropriate name and copy to designated directory.

tesla764Author Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.