I have a dts package that imports a text file into SQL Sever.
If I right click "execute" on the package it works fine,
but when I schedule the package to run as a job the package fails
with the following error:
DTSRun OnError: Copy Data from dw000200ar to [APDData].[dbo].[TblProb] Step, Error = -2147467259 (80004005)
Error string: Error opening datafile: The specified user does not exist.
Any help is appreciated.
Hi,
I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below
I write a code in class to create a text file and write text in it.
1) I creat a class in Visual Basic.Net 2005, whose code is given below:
Imports System
Imports System.IO
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Public Class WLog
Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)
Dim w As StreamWriter = File.AppendText(LogName)
LogIt(newMessage, w)
w.Close()
End Sub
Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter)
wr.Write(ControlChars.CrLf & "Log Entry:")
wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
wr.WriteLine(" :")
wr.WriteLine(" :{0}", logMessage)
wr.WriteLine("---------------------------")
wr.Flush()
End Sub
Public Shared Sub LotToEventLog(ByVal errorMessage As String)
Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
log.Source = "My Application"
log.WriteEntry(errorMessage)
End Sub
End Class
2) Make & register its assembly, in SQL Server 2005.
3)Create Stored Procedure as given below:
CREATE PROCEDURE dbo.SP_LogTextFile
(
@LogName nvarchar(255), @NewMessage nvarchar(255)
)
AS EXTERNAL NAME
[asmLog].[WriteLog.WLog].[LogToTextFile]
4) When i execute this stored procedure as
Execute SP_LogTextFile 'C:Test.txt','Message1'
5) Then i got the following error
Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile':
System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied.
System.UnauthorizedAccessException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options)
at System.IO.StreamWriter.CreateFile(String path, Boolean append)
at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
at System.IO.StreamWriter..ctor(String path, Boolean append)
at System.IO.File.AppendText(String path)
at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)
Hi there,
I getting really frustrated here when trying to export a table with many columns into a text file. This does not happen when the table is relatively small.
I am using client tools on my machine connecting into SQL Server 2000 (sp3) that resides on a different box.
I have installed the client tools on my machine from scratch; no SQL server components were on it before. The version in the Add/Remove Program for SQL Server 2000 showed 8.00.194.
Then I have upgraded my machine (Not the server) client tools into sp3a (sp3a). The version in the Add/Remove Program for SQL Server 2000 shows 8.00.761.
Now, the problem is when I try to create a package that export data into a text file and the source is a table or a view with many columns, even only 18 columns. Trying to define the column structure in the destination file using "Define columns" button on the Destination tab, I get error message
"The instruction at "0x780014b1" referenced memory at "0x00000000". The memory could not be "read". and Enterprise Manager fails. This did not happened when I had version 194 before. only after installing sp3a. when I try to open a package that has been created in 194 version I can see the column definition of the destination, but again, it fails when I try to change it by "Populate from source" and "Execute".
Also, I can't change the column size on the "Define columns" window (for fixed length destination), but I could do it on 194 version.
So:
1. How come the newer version is worse than the previous one ? I know that Microsoft has solved some problems relating to this issues (some MDAC changes and more).
2.What can I do to solve this problem. Do I have to have other version ?
Any help will be appreciated.
[B]
The error I recieve is as follows:
"[Flat File Destination [13]] Error: Data conversion failed. The data conversion for column "SDATA" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". "
"[Flat File Destination [13]] Error: Cannot copy or convert flat file data for column "SDATA". "
"[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Flat File Destination" (13) failed with error code 0xC02020A0. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. "
"[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC02020A0. There may be error messages posted before this with more information on why the thread has exited. "
"[DataReader Source [207]] Error: The component "DataReader Source" (207) was unable to process the data. "
"[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "DataReader Source" (207) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. "
"[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited. "
I am selecting data from an OBDC then copying it to a text file an I always get this error, but when I change the destination to a excel file it works perfectly. But the whole point of the package is to copy to a text file.
Please could you help me, your replies will be greatly appreciated.
I want to export to a Text File (destination) from a SQL query using a DTS DataPump Task. My query has 28 columns, some how when I try to define the destination columns for my text file the Microsoft Managment Console Crashes completely.
I tough may be my query has two many columns only to find out that it has one to many. If I ramove a column form my query, any column. I get no error at all.
¿Is there a limit to a Text File destination connection?
hi all,
While importing into sql server 2000 from a text file i am getting an error message like not enough disk space available to perform this operation but there is enough space (around 18gb). Please advice why this is happenning as my work is stuck
thanks and regards
jk
I'm trying to import a tab separated text file into sql server 2005 using the import guide. But when running the job I get the error message
Error 0xc02020c5: Data Flow Task: Data conversion failed while converting column "Column 19" (67) to column "Column 19" (404). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)
The column 19 which reported a problem contains this information:
?searchroot=/_gen_/txt/&template=std.htm&use=prospect&intref=1_26067
However what is mysterious is that if I open the file in notepad or Excel and resave it again the job runs perfectly. This is not a way we could make it work later on since it's an automatic job that will run each night on a new text file.
The text file is sent from Norway to Sweden - and I use ANSI latin 1 when importing.
The column has datatype DT_STR with a width of 500.
I use Locale Swedish and when I save in Notepad it is saved in ANSI,
I use Windows XP Swedish version.
Hi all--Given a table called "buyers" with the following column definitions in a SQL Server 2005 database:
[BUYER] [nvarchar](40) NULL,
[DIVISION] [nvarchar](3) NULL,
[MOD_DATE] [datetime] NULL
This table is laden with Unicode data and the MOD_DATE contains no data--not even NULL values, and is giving me a headache as a result. I can export this data fine to a text file, but when I create an SSIS package to attempt import to another table defined exactly the same as above in another place, I get the following messages:
SSIS package "buyers_import.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Task, Source - buyers_txt [1]: The processing of file "D: emp3uyers.txt" has started.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Information: 0x402090DE at Data Flow Task, Source - buyers_txt [1]: The total number of data rows processed for file "D: emp3uyers.txt" is 232.
Error: 0xC0202009 at Data Flow Task, Destination - buyers_tst [22]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
Error: 0xC020901C at Data Flow Task, Destination - buyers_tst [22]: There was an error with input column "MOD_DATE" (45) on input "Destination Input" (35). The column status returned was: "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at Data Flow Task, Destination - buyers_tst [22]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (35)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Destination Input" (35)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - buyers_tst" (22) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Task, Source - buyers_txt [1]: The processing of file "D: emp3uyers.txt" has ended.
Information: 0x402090DF at Data Flow Task, Destination - buyers_tst [22]: The final commit for the data insertion has started.
Information: 0x402090E0 at Data Flow Task, Destination - buyers_tst [22]: The final commit for the data insertion has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Destination - buyers_tst" (22)" wrote 0 rows.
Task failed: Data Flow Task
SSIS package "buyers_import.dtsx" finished: Failure.
Among the customizations in this package is the flag "ValidateExternalMetadata" set to False. The data itself is surrounded by " and delimited by semicolons for each field, with the header row set as the name of each column. It looks like this:
"BUYER";"DIVISION";"MOD_DATE"
"108 Joon-Hyn Kim";"TAD";""
"109 Kang-Soo Do";"TAD";""
"FS07 John Smith";"TAD";""
...
Can anyone suggest a course of action on how to handle the error when the MOD_DATE field is completely empty?
Thanks in advance,
Jonathan
We have a flat file import proces which imports data from a series of unicode flat files.
The files have text qualifiers and are being imported to a table with the following format:
CREATE TABLE [dsa].[OBS](
[Kundenummer] [nvarchar](10) NULL,
[Navn] [nvarchar](60) NULL,
[Adresse] [nvarchar](50) NULL,
[PostnrBynavn] [nvarchar](50) NULL,
[Kursusdato] [datetime] NULL,
[Varighed] [decimal](18, 2) NULL,
[Kursustype] [nvarchar](100) NULL,
[Risikokoder] [nvarchar](50) NULL
) ON [PRIMARY]
In one of our files we have two rows that looks like this:
"19298529";"THIS IS ROW 1";"ADDRESS 9 -13";"4200 SLAGELSE";"02-05-2006";8.00;"Kombikursus Førstehjælp - Brand 8 lek.";"37"
"19448242";"THIS IS ROW 2";"ADDRESS 50";"4140 BORUP";"04-05-2006";4.00;""Fra vil selv - til kan selv". Om børn 1½ - 3 Ã¥r";"22"
Both rows are OK according to the format, but the second row actually contains the text qualifier in one of the qualified fields (""Fra vil selv - til kan selv". Om børn 1½ - 3 Ã¥r"). It's the title of a course with a comment.
The proces fails on this file, and wont even redirect the row, as it does on other erroneous rows in other files we import.
We believe this is a valid text, but apparently SSIS doesn't
Is this a bug or is this record not allowed?
Is there a work around, and why wont SSIS redirect the row?
We believe the reason is that the field before is not text quaified (which is of course specified in the connection manager).
Thanks in advance,
Lasse
I'm having problems designing a package to attempt to execute a fast load data transfer but failback to regular speed with error redirection in the event of an error.
The way I designed this was to add one data flow task to my package called "DFT FASTLOAD". The data flow copies a table SRC to another table DEST in the same SQL Server database. In the error handler for the data flow task I copied the original data flow task and changed the name to "DFT REGULARLOAD with Error redirection". In this data flow task I did not use fast load and addtionally redirected errors to a text file.
In the Data Flow Task "DFT FASTLOAD". I am copying from a varchar source field(with non-date strings) to a datetime destination field to force errors. However the Data Flow Task "DFT REGULARLOAD with Error redirection" never seems to start transferring data from source to destination. The data Flow Task "DFT REGULARLOAD with Error redirection" turns yellow (after the error occurs in "DFT FASTLOAD"), but no data is being transferred). It seems like it hangs.
Do I need to increase the MaximumError Count or something? The data flow task "DFT FASTLOAD" does not turn red when the error occurs it just remains yellow, so i assume I'm on the right track since it seems the error is caught.
I have added screenshots ... hopefully these screenshots will clarify my problem.
DESIGN:
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD1.jpg
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD2.jpg
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD3.jpg
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD4.jpg
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD5.jpg
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD6.jpg
RUNTIME:
http://i256.photobucket.com/albums/hh179/abzbank/RUN_FASTLOAD7.jpg
http://i256.photobucket.com/albums/hh179/abzbank/RUN_FASTLOAD8.jpg
http://i256.photobucket.com/albums/hh179/abzbank/RUN_FASTLOAD9.jpg
http://i256.photobucket.com/albums/hh179/abzbank/RUN_FASTLOAD10.jpg
http://i256.photobucket.com/albums/hh179/abzbank/RUN_FASTLOAD11.jpg
I can provide more details if needed... but really this is just a basic test.
Any assistance would be appreciated!
Hi,
I am looking for a way to combine two text files into one file. I am thinking of using a batch file (DOS command ) to do it. Any suggestion please?
I get this error:
The data types text and text are incompatible in the equal to operator.
when trying to execute this query
SELECT id FROM users WHERE username=@userName
Any Ideas?
Hey All,
Similar to a previous post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=244646&SiteID=1), I am trying to import data into a SQL Table.
I am trying to program a small application that will import product data obtained through suppliers via CD-ROM. One supplier in particular uses Fixed width colums, and data looks like this:
Example of Data
0124015Apple Crate 32.12
0124016Bananna Box 12.56
0124017Mango Carton 15.98
0124018Seedless Watermelon 42.98
My Table would then have:
ProductID as int
Name as text
Cost as money
How would I go about extracting the data with an XML Format file? I am stumbling over how to tell it where to start picking up data for a specific column.
Is there any way that I could trim the Name column (i.e.: "Mango Carton " --> "Mango Carton")?
I don't know if it makes any difference, but I've been calling SQL from my code by doing this:
Code in C# Form
SqlConnection SqlConnection = new SqlConnection(global::SQLClients.Properties.Settings.Default.ClientPhonebookConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO PhonebookTable(Name, PhoneNumber) VALUES('" + txtName.Text.ToString() + "', '" + txtPhoneNumber.Text.ToString() + "')";
cmd.Connection = SqlConnection;
SqlConnection.Open();
cmd.ExecuteNonQuery();
SqlConnection.Close();
RefreshData();
I am running Visual Studio C# Express 2005 and SQL Server Express 2005.
Thanks for your time,
Hayden.
What is the easiest way to accomplish this task with SSIS?
Basically I have a stored procedure that unions multiple queries between databases. I need to be able to export this to a text file on a daily basis and add a total records: row to the end of the text file.
Thanks in advance for any help.
Hello Experts,
I am createing one task (user control) in SSIS. I have property grid in my GUI and 2 buttons (OK & Cancle).
PropertyGrid has Properties like SourceConnection, OutputConnection etc....right now I am able to populate Connections in list box next to Source and Output Property.
Now my question to you guys is depending on Source Connection it should read that text file associated with connection manager. After validation it should pick header (first line of text file bases on record type) and write it into new file when task is executed. I have following code for your reference. Please let me know I am going in right direction or not..
What should go here ?
->Under Class A
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
//Some code to read file and write it into new file
return DTSExecResult.Success;
}
public const string Property_Task = "CustomErrorControl";
public const string Property_SourceConnection = "SourceConnection";
public void LoadFromXML(XmlElement node, IDTSInfoEvents infoEvents)
{
if (node.Name != Property_Task)
{
throw new Exception(String.Format("Invalid task element '{0}' in LoadFromXML.", node.Name));
}
else
{
try
{
_sourceConnectionId = node.Attributes.GetNamedItem(Property_SourceConnection).Value;
}
catch (Exception ex)
{
infoEvents.FireError(0, "LoadFromXML", ex.Message, "", 0);
}
}
}
public void SaveToXML(XmlDocument doc, IDTSInfoEvents infoEvents)
{
try
{
// // Create Task Element
XmlElement taskElement = doc.CreateElement("", Property_Task, "");
doc.AppendChild(taskElement);
// // Save source FileConnection
XmlAttribute sourcefileAttribute = doc.CreateAttribute(Property_SourceConnection);
sourcefileAttribute.Value = _sourceConnectionId;
taskElement.Attributes.Append(sourcefileAttribute);
}
catch (Exception ex)
{
infoEvents.FireError(0, "SaveXML", ex.Message, "", 0);
}
}
In UI Class there is OK Click event.
private void btnOK_Click(object sender, EventArgs e)
{
try
{
_taskHost.Properties[CustomErrorControl.Property_SourceConnection].SetValue(_taskHost, propertyGrid1.Text);
btnOK.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
#endregion
}
Hi,
I am pulling text files in gzip format from UNIX system. I want to unzip these files and then import data from these files into database using SSIS.
Hello friends....
I am looking for 2 things(using c#.net or vb.net and sql svr 2000)
1.convert data from sql server 2000 database (say customers table from northwinds database) to a text file(separated by commas or just plain space)
2.Insert the data from text file back to database.
Can someone pls give me the detailed code to achieve this....really need this on urgent basis.......Thank You.
When my production server processing some queries suddenly the SQL Server service crashed and following error was in the error log:
SQL Server Internal Error. Text manager cannot continue with current statement.
The server is running SQL Server 2000 with SP4.
I am really concerned because this is a production sever and there are over 300 users access concurrently.
Please help me to find a solution.
Thanks,
Roshan.
Hi -
I have an File System Task that copies a file from one directory ot another. When I hard code the target directory (c:dirfile.txt) it works fine. When I change it to a virtual directory (\serverdirfile.txt) I get a security error:
[File System Task] Error: An error occurred with the following error message: "Access to the path '\gracehbtest oS2TMM_Live_Title_000002.xml' is denied.".
Where do I change the security settings?
Thanks - Grace
Is it possible to export a sql table to a text file without using DTS? And also can we put a header on it
Thanks
Can we insert a header if we export the sql table thru DTS or BCP
Thanks
how can we insert the header.
Here's my problem :
I have to export a table form SQL to a text file But I want to have a line "XXXXXXX" as the top most line of the text file.
Can this be achieved thru DTS or BCP
I would like to send the data content in a table to a text file. Is there a stored procedure command to do that? Any help would be greatly appreciate. Thanks in advance.
View 1 Replies View Related
Hi folks
Any help on my request would greatly be appreciated. We are trying to maintain the growth of a particular table, by exporting and deleting data that is older then 90 days. Here are the 4 steps I need to do. I believe I know how to do 1 and 4;
1. Create a job that continously exports data that is older then 90 days to a text file
select *
from table A
WHERE (CREATED < DATEADD(DAY, - 90, GETDATE()))
(the column 'created' datatype is datetime and looks like this '3/5/2007 3:11:44 PM')
2. Have the job automatically name the exported file, the day it was exported (i.e. 07032007 (todays date))
3. Then zip that file (we're using 7-zip)
4. Then delete the data out of the table
delete
from table A
WHERE (CREATED < DATEADD(DAY, - 90, GETDATE()))
Im not a big scripter/coder, so I was wondering if there is anything I could do in SSIS. Im more familiar with DTS, so any kind of baby steps you could provide in SSIS, would go a long way.
Thank you so much.
Is it possible to send the output of a query to a text file in a stored procedure? When I run stored procedure in Query Analyzer I am able to do that and I am wondering if this is possible in a automated way?
View 2 Replies View RelatedAs we know, MySQL have function to output its data into Text File using "Select * into outfile 'C:/mytext.txt".
Does MSSQL has "Select into Outfile" function??? If yes, what is the function??
Thanks in advance :)
Anderson
I need to know how to import a text file into a stored procedure as one big varchar. I don’t want to import the data straight into my tables. I need to be able to work with it in the stored proc.
View 2 Replies View RelatedHappy Thursday all,
I am importing a text file to sql and most of my fields look like this:
"M","NEW ADDRESS",
and my other field looks like this:
"firstname Lastname"
but I need it like this:
"firstname", "Lastname"
Can anyone help me understand a better way of making this happen?
Thanks in advance
I have a table(say tblUserInfo) on SQL Server. What I like to do is a text file will be generated on the hard drive which SQL Server sits on when a new record is inserted into tblUserInfo. The content of the text file comes from the table. Is there any way we can go for doing that?
View 2 Replies View RelatedI have a table(tblUser) on SQL Server. I like to create a text file somewhere on the SQL Server after a new record is inserted into the table(tblUser). The content of the text file should be the new record. Is there any way to do that?
Thanks!
Regards,
Kevin Jin
Hello,
I have an access database (access 95 Version7)dumping a delimited text file onto my server. I am then using DTS in SQL 2000 to import the file into a table.
My issue is that each time the DTS runs, it imports the whole text file each time, this is causing duplicate records.
So I created a transformation script as follows :
Function Main()
If DTSSource("counter") <= DTSDestination("counter") Then
Main = DTSTransformStat_SkipRow
Else
DTSDestination("counter") = DTSSource("counter")
DTSDestination("serialdata") = DTSSource("serialdata")
DTSDestination("serialdata1") = DTSSource("serialdata1")
DTSDestination("date") = DTSSource("date")
DTSDestination("time") = DTSSource("time")
Main = DTSTransformStat_OK
End If
End Function
The theory behind the If statement, is if it sees that the counter field is less than or equal to what is there, it will skip the record and move forward. For some reason this is not working.
Does anyone have a workaround or another solution to this problem
HI Guys,
I am doing the following to read the data in a text file and inserting it into SQL.
1) Open db connection
2) Open Text File
3) loop through text file all along inserting each row into the db
4) close the text file
5) close the db connection
However, the text file has over 400 rows/lines of data that need to be inserted into the db. Each line in the text file is a row in the db. At anyrate, the above script times out. Is there a better, faster way to do this? I can't use Bulk Insert due to permissions previlages.
Thanks in Advance!
SQLServer 7.0
Hi,
I have a small project on that involves importing a series of csv files held within an ftp directory into our Datawarehouse. Every day a series of csv files will be added to the directory. These will be named something like:
Audit1.csv,Audit2.csv etc.
I would like to automate this process as this can involve up to 400 files at a time. The proecedure would need to identify a valid file, import it into the database, delete the file and then move onto the next one.
Does anyone know of a way to achieve this? I was thinking along the lines of using a cursor and bcp but I'm not sure how to identify these files to the database i.e. how do i make it step through the directory and process the files?
Any help would be greatly appreciated.
Thanks
Rob