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 RepliesHi all.I have an keyword table by 2000000 rows that is an int(4) key and avarchar(50)Keyword(Unique index) in Sql Server 2000.After about 80 seconds of starting full population, speed of addingnew items t in Enterprise manager/Fulltext ctalog, decreases to (1/4)and more less.please help me!Thanks
View 1 Replies View RelatedHi ya,
I'm generating a flat file from SSIS package and i'm having some problems.
My package contains this Data Flow which is connecting to a Database and importing the records. I did design a script component since the file I generate has some padding.
Here is the code for the component so that you really understand what i'm trying to achieve over here.
Dim toPadTo As Int32
Dim myDate As String
Dim MultVal As Int64
Dim myMonth As Int16, myMonth2 As String
myMonth = CShort(Row.TransDate.Month)
If myMonth <= 9 Then
myMonth2 = myMonth.ToString.PadLeft(2, CChar("0"))
Else
myMonth2 = myMonth.ToString
End If
myDate = CStr(Row.TransDate.Date.Day) + myMonth2 + CStr(Row.TransDate.Year)
MultVal = CInt(Row.TransAmount * 100)
Dim myChkLength As Int16 = CShort(MultVal.ToString.Length)
Dim myCombine As String = CStr(MultVal) & CStr(myDate)
Dim myCombine2 As String = CStr(MultVal) & CStr(myDate)
Select Case myChkLength
Case 4
toPadTo = 30 - (myCombine.ToString.Length)
Case 5
toPadTo = 30 - (myCombine.ToString.Length - 1)
Case 6
toPadTo = 30 - (myCombine.ToString.Length - 2)
Case 7
toPadTo = 30 - (myCombine.ToString.Length - 3)
End Select
' + myDate.ToString.Length - 1))
Row.myConvert = myCombine2.PadLeft(toPadTo, CChar("0"))
Dim MyNewRow As String = myCombine2.PadLeft(toPadTo, CChar("0"))
Dim ChkSign As Int16
ChkSign = CShort(Math.Sign(CDec(MyNewRow)))
If ChkSign = 1 Then
Row.myConvert = MyNewRow.PadLeft(2, CChar("+"))
ElseIf ChkSign = -1 Then
Row.myConvert = MyNewRow.PadLeft(2, CChar("-"))
Else
Row.myConvert = MyNewRow.PadLeft(2, CChar("+"))
End If
End Sub
Obviously i don't think this is the best approach hence i'm asking? not to mention the sort of problem i'm getting with the + and - insertion part of the code.
To give you an example of how the value would be:
Actual value: 159.23
After transformation it should be like this : +00000000000015923
Depending on the amount x no zeros should be inserted.
Any other way to achieve it? Apart from this I also need to generate a sequence no with some string at the end of the ragged file. How would i go for it ?
Cheers
I'm trying to generate a DTS Package with VB.Net using the Microsoft DTSPackage Object Library and
and the Microsoft DTSDataPump Scripting Object Library
I have to load csv files into SQL tables.
I could generate both a SQL connection and a FlatFile connection and the transformationtask.
When I look at the transformationtask and click on the transformation tab I get this error
"Incomple file format information"
The problem is I don't find where I could set the FlatFile connection properties like "Text Qualifier" and
"row delimiter"
I tried this but it still shows CRLF as row delimiter when I look at the generated DTS Package
Dim oConnection As DTS.Connection2
Dim package As DTS.Package2
Dim filename As String
filename = "myfilename.csv"
oConnection = package.Connections.New("DTSFlatFile")
oConnection.Name = filename
oConnection.ConnectionProperties.Item("Row Delimiter").Value = vbTab
Hi, I'm a first time user at reporting services and in dire need of some help, I'm having a real hard time finding the information I need.
I have made two reports in Business Intelligence Development Studio that access a sql database and I can deploy them and see them at http://localhost/reports etc.
What I wan't to do is be able to "trigger" the reports to be built from a schedule (e.g. every 8 hour on weekdays, every 12 hours on weekends but should be able to be changed) and after built to be saved to a html file.
I also need to set the sql query for the dataset before generating the report (select * from table where DateTimeType between This and That).
Anyway, I'm completely lost on how to achive this and would greatly appreciate and tips or pointers to where the information can be found.
Kindest Regards
Hi All,
Can any tell me how to generate the Script file in SQL 2005.
If i execute that script in another server all should be created automatically including the Database with the same name.
How to do this..
Ganesh
Hi All,
I need to know how to create a AScii 7 bit flat file using Integration services. I do have basic charecters in the flat files - only other charecters required are a pipe (|), which is used as delimeter and additionally it will have line feed (LF) which is used as row delimeter.
Please let me know if this is possible.
-vinu
Hi All,
I am using MS SQL 2005 and using Integration Services I have created FTP task to create a txt file with the required information on to a FTP location. But I need the encoding of the file to be set to AScii 7 bit mode rather than unicode or Ansi-Latin I - which are 16 bit. I tried creating the file first in unicode first & then converting it Ascii, but this made me to loose some data from the generated file. Looks like this doesnt work out and my attempts generating AScii 7 bit flat file is failing. I need solution to URGENTLY otherwise I will have think of some alternative other than Integration services. Egarly waiting for any responses!!
-vinu
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?
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)
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.
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(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
In a DTS package I have a text file import object, a data pump, and a SQL object. The text file import object has been set up to splice a 500 character wide file into 20 columns. The data pump task does a copy column for all the columns into the appropriate table. What I need to do is have a way of changing the file name I specify in the text import object. I have 12 months worth of data in seperate files (DBF0199.TXT, DBF0299.TXT, DBF0399.TXT, etc..)
which all use the same format. Is there a way to change the text import objects file name inside the package using an active script task or something?
Any help is greatly appreciated.
Thanks,
Todd
I am using bcp to write from a query or table into a text file from a stored procedure. No problem. However, what do I do if I want to write to a text file from another stored procedure which returns a record set? Any help gratefully received. thanks.
View 1 Replies View RelatedI am trying to SELECT various fields from a table in SQL Server to INSERT INTO a Text file defined using a Schema.ini. I know that this can be done using BCP (not sure if I could specify which fields as not all are required), DTS (which I have done) and with a Linked Server (where I keep getting a bookmark error). The process used in Access is [Sample#csv] IN 'C:' 'TEXT;' but I can't seem to find the format to use in SQL using either a MSDASQL or ODBC connection string. Any ideas would be greatly appreciated, thanks.
View 1 Replies View RelatedI'm trying to import a fixed field text file into SQL Server using DTS but everytime I go past 3640 characters, I am not able to add, delete or move column breaks after that. Is anyone else experiencing this problem and know of a work around. Any help would be appreciated. Thanks!
Using SP2 on SQL Server 2000.
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]