August 14, 2006 at 10:22 am
Hello everyone,
I have a daily .csv file that is created from our production computer. I want to be able to automatically take this file and dump it into SQL server. I have been able to take a single file and do this but since I have a newly created file everyday, I want to automated the process. For example, Monday's file is called Data-S-08142006.csv and Tuesdays file is called Data-S-08152006.csv. Does anyone know how to create a DTS package that will pick up on the new file daily or check to see if there are new files within the folder that DTS could transfer? Any thoughts would be helpful. thanks
Josh
August 14, 2006 at 10:33 am
Fairly simple in SQL 2005. Using BIDS create an integration services project then build the appropriate data flow. Even when there is only one file you could use a for each loop container.
In SQL 2000 I think you could use a global variable and active X script to get the file name but its been a while since I did anything with SQL 2000
August 14, 2006 at 10:51 am
Im using 2000. Never used Active X before, is there a website with some sample coding for something like this? My other thought was to somehow to merge the csv files into one file and then upload it through DTS but I dont think i can to that automatically.
August 14, 2006 at 2:08 pm
August 15, 2006 at 5:11 am
you could do it within the dts package using t-sql and doing a bulk load
you can get a dir listing and load the appropriate file right in
August 15, 2006 at 7:36 am
You can create a .bat file to copy the date stamped file name to a generic file name:
for /f "tokens=2,3,4 delims=/ " %%i in ('date /t') do (
set my_day=%%j
set my_month=%%i
set my_year=%%k
)
set filedate=%my_day%_%my_month%_%my_year%
copy %filedate%.csv myData.csv
And then use the SQL 2000 DTS Package "Execute Process Task" to run the bat file. Then use the DTS package to load the .csv file to the destination table. Make sure your .bat script has the full path to the files to be re-named.
August 15, 2006 at 9:28 am
You can use Dynamic Property Task to adjust a file name
August 15, 2006 at 9:55 am
You can do this in DTS. We do this every day as part of an interface between ourselves and a customer.
In a DTS job use a Execute Process Task that executes a .bat file that copies any Data-S-*.csv files to a file called say ImportData.csv.
Then use a Transform Data task to load the standard filename ImportData.csv to your database table.
Then use an Execute Process task to change all of the .csv files to say .csvp (for processed). This enables easy recovery of original files in case of problems. (i.e. just rename the files back to .csv)
Hope this of assistance
Pete
August 15, 2006 at 10:30 am
I developed a DTS package for a client that is very similar to what you are looking to do. It uses T-SQL to obtain the name of the filename/location you are looking for (like 'C:\files\Data-S-08142006.csv'), then uses vbscript to change the filename for the bulk insert task. Once the insertion is complete it moves the file to an archive folder (using xp_cmdshell). Just another morsel of "food for thought".
August 15, 2006 at 10:37 pm
Hi
I am using DTS with C# to transfer txt information into SQL 2000. We can change the path and also the file name during runtime. Here is the code.
I have given a name for the text connection as "TandR". I can change the path and also the name of the text file during run time (note that the columns would be the same)
DTS.Package2Class package =
new DTS.Package2Class();
string filename = System.Windows.Forms.Application.StartupPath + @"\DTS\" + strfilename +".dts";
string password = null;
string packageID = null;
string versionID = null;
string name = strfilename;
object pVarPersistStfOfHost = null;
package.LoadFromStorageFile(
filename,
password,
packageID,
versionID,
name,
ref
pVarPersistStfOfHost);
package.FailOnError =
true;
for (int k = 1; k<= package.Connections.Count;k++)
{
if (package.Connections.Item(k).Name == "Northwind")
{
package.Connections.Item(k).DataSource = strDTSServerName;
package.Connections.Item(k).UserID = strDTSUserName;
package.Connections.Item(k).Password = strDTSPwd;
package.Connections.Item(k).Catalog = strDTSDBName;
}
if (package.Connections.Item(k).Name == "TandR")
{
package.Connections.Item(k).DataSource = strTextDataSource;
}
}
package.Execute();
Console.WriteLine("executed successfully");
package.UnInitialize();
package =
null;
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply