Can anyone help? Need to upload a text file to a sql database but keep getting errors.
I'm creating a page that will allow users to to bulk import and update to a MsSql database. The users provide a text file every so often with new/update information. So i want to use a DTS package to transform the infomation, and create a table in the database, then check against existing/non existing records, if the record exist, update it, if not insert it. I'm using Visual Studio.Net, ASP.Net and coding in VB.Net.
Anyone know where i can find documentation/code regarding the above?
I will be greatful for any help.Hi There,
I looked into using DTS packages to upload data from a spreadsheet about a year and a half ago, but decided against it due to the various problems associated. I opted for the calling of a stored procedure with a bulk insert statement to load the information from a CSV file. Here is c# code for the calling of a DTS package that was used, should be easy enough to convert to vb.net.
BTW you are using DTS; (add a reference to Interop.DTS, i think it is Microsoft DTSPackage Object Library)
All the best, John
string serverName = System.Configuration.ConfigurationSettings.AppSettings.Get("ServerName");
string serverPassword = System.Configuration.ConfigurationSettings.AppSettings.Get("ServerPassword");
string userName = System.Configuration.ConfigurationSettings.AppSettings.Get("UserName");
bool bSuccessful = true;
int pErrorCode;
int lHelpContext;
string sHelpFile;
string sInterfaceError;
string sErrSource;
string sErrDescription;
int DTSStepExecResult_Failure = 1;
Package2Class dtsPackage = new Package2Class();
object varPersistStgOfHost = null;
dtsPackage.LoadFromSQLServer(serverName, userName, serverPassword, DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, null, null, null, DTSPackages.PackageName, ref varPersistStgOfHost);
dtsPackage.Execute();
// Check each step for failure
for (int stepCount=1; stepCount <= dtsPackage.Steps.Count; stepCount++)
{
if ((int)dtsPackage.Steps.Item(stepCount).ExecutionResult == DTSStepExecResult_Failure)
{
dtsPackage.Steps.Item(stepCount).GetExecutionErrorInfo(out pErrorCode, out sErrSource, out sErrDescription, out sHelpFile, out lHelpContext, out sInterfaceError);
bSuccessful = false;
}
}
dtsPackage.UnInitialize();|||hi John,
Thanks for the help, i do appreciate it! will try the code and see if i have any luck!
take care..
jen|||I do not see any part in the code that references the csv file.|||As the above code calls a DTS package that in turn loads the CSV file.
