I have little problem designing a dts package. Scenario is this ,I receive 54 text files in different timings daily so that we have to import data from a directory every one hour where another application done in unix creates text files. So we have a package to be executed every one hour goes check in that directory if new text file came then import that file into sql server.The files are named like NAME01YYYY-MM-DD.txt.So in DTS package I have 54 braches(dynamicpropertiestask-->Text(source)-->TransformDataTask-->ConnectionProperties) to read & pump text files data into sqlserver DB. Now the requirement is I have to design one more branch for new text file (i.e., 55 text file).
In dynamic property task how to set global variables for Connections,Text File (Source),OLEDBProperties,Data Source,Properties,Value?
By seeing existing dynamic properties task I am unable to follow.
I set up a dts package with dynamicpropertiestask,globalvariables,execute sql task, transform data task.
DTS is running fine for the default saved text source file only. When I run dts and put in different text files unable to load.
I know this might enough information. please help me it is really urgent.