Basically remove any line that start with a "#" or any blank lines..
I am assuming you can do this only using a script component and not directly through ssis..but i am not too familiar with scripting...so some code would be helpful
i have a text file that is like:date = OCT0606asdfsdafasdfasdgsdghasdfsdfasdgSTART-OF-DATAasdfasdfgasdfgdfgsfgsadfsdfgsaasdfgsdfgEND-OF-DATAasdfgalsdkdfklmlkmasdfgasdfgi need to clear everything from this file except the data between theSTART-OF-DATA and END-OF-DATA using a batcj file... elternitavly i amopen to suggestions of how to import using bulk insert in sql withoutchanging the file at all. data is pipe seperated but obvioulsy hasplenty of junk data in it. i have 2 similar files at about 30mb and60mb in size. thnks everyone
I am trying to bcp import a text file into a SQL Server 2000 database.The text file is coming out of a java application where orderinformation is written to the text file. Each record is on it's ownrow, so the last item in each record has a new line character at theend of it to create the next row. This works well in creating the filehowever bcp does not like to import this text file with the extra blankline at the end. If I change the new line character to the beginning ofthe records then there is a blank line at the top of the text file,which bcp also does not like. Does anyone have any suggestions for meto get around this issue?Thanks,
SQL SERVER 2000: My problem is that I have to process a special text file every day which contains 0 ASCII values to separate fields. The DTS import program drops everything after the ascii 0 value in the row, but of course I need the entire row with all fields. So how can I prevent the text file import task from dropping everything after the 0 ascii value?
Hi ALL, I need to delete 1st and last lines of text from a text file in an automated process,and save the file with the same name or a different name at the same location. How is that possible?Any help is greatly appreciated. Thanks!! Sai.
I know very simple SQL queries but I need help with this one. I have multiple lines in a SQL database that I need to run. Basically, I need to run this (the bracketed text and the XXX are place holders):
DELETE FROM [tableName] WHERE [columnName] = 'XXXXX'
But I need to run it around 90 times where XXXXX is a unique variable each time. I could create 90 lines similar to this one but that would take way too much time to run. Any suggestions for a noob?
Thanks,
- MT
-=<>=-=<>=-=<>=-=<>=-=<>=- Matt Torbin President Center City Philadelphia Macintosh Users Group http://www.ccpmug.org/
I have a DTS that reads in a bunch of transactions daily to a tran history table. I read them in from a text file each day. The problem is, that about half of the lines in the text file contain semicolons because they are comments. What I do now is, import the whole thing, and then do a Delete on my tran hist table for all lines with semicolons. As my tranhist table grows, this Delete will start to take a long time. How do I filter it so it doesn't even import the lines with semicolons to start with, that will run faster and save me time later.
I have a dtsx package that works fine with one exception. When I open the dtsx package in BI, it gives me the following message:
Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file?
When I respond yes, the package opens and I can edit or execute with no problem. Still, I want to understand what could cause this message to occur and, more importantly, how I can get rid of the message. When I try to simply execute the package I still get the same error and it seems this will be a problem for trying to run the package from SQL Server agent.
It seems likely to me that this message refers to the dtsx file (in xml format) itself. Does that make any sense?
Hi everybody, I'm sending text based e-mails using SMS and I need some lines to be bolded. I don't want to switch to HTML based just to make some lines bold. But, I cannot figure out how I can make the lines bold in SMS. I didn't see any options in SMS to bold a line of text. Is there any any function available for varchar datatype that will bold the text or something like that? or will I have to go to HTML based e-mail? any help is greatly appriciated. devmetz
I've created a dataset with 27 measures and 20 query parameters. When attempting to load the report containing this dataset I'm shown the message;
'Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file.'
If I do open the file it does indeed respond very slowly or even hangs.
I can manually format the XML code but amending the code in any way (i.e. using the layout designer to move a chart) removes my formatting and re-introduces the problem.
Are these an unreasonable amount of measures / parameters?
Environment; VS2005 v8.0.507 MSSQL 2005 9.00.1399.06 Build 3790 SP2 Windows Server 2003 SP2
I am parsing a directory of flat files and looping through it with a foreach loop. Some of the files have lines that contain characters that I would like to remove. In fact, it would be good if I could remove the entire line. Is there a way to do this with a Script Task or some other way.
I have a very simple question, how do i read the first couple of lines only from a flat file source. Let me illustrate with an example:
**Source file***
Date of refresh 04/05/06 **
abc, 123
bac, 156
I need a way to read the first line 'Date of refresh 04/05/06 **' and get the date '04/05/06' ; and store the date in a variable.
Right now, I have a flat file source and a script component, which receives a connection from the flat file source, The script component is reading the lines, but thrice instead of once. It would be great if I can program the script component to terminate after reading the first line only. Any help would be appreciated very much.
I can't believe it's been a few days and I can't figure this out. We have a flat file (purchaseOrder.txt) that has header and detail lines. It gets dropped in a folder. I need to pick it up and insert it into normalized tables and/or transform it into another file structure or .NET class.
10001,2005/01/01,some more data SOME PRODUCT 1, 10 SOME PRODUCT 2, 5
Can somebody place give me some guidance on how to do this in SSIS?
Ok so I have some dynamic sql to delete a file that is created via sql earlier on. It is to provision a copy of a database to an instance on link server. Everything works great and the files used to delete. Now, with no code changes it is throwing a syntax error. I do a print of what the dynamic sql is creating before executing and then I copy / paste what was generated into command prompt and guess what!! The file deletes.
Here is the result on screen:
@DeleteBackupFileStatement: DEL adas16.clients.advance.localwip$AvionteAP_Template_893.bak /Q Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ''. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ''.
Here is the code that creates the statement:
SET @DeleteBackupFileStatement = NULL BEGIN SET @DeleteBackupFileStatement = 'DEL ' + LTRIM(RTRIM(@BackupFile)) + ' /Q' END PRINT '@DeleteBackupFileStatement: ' + cast(@DeleteBackupFileStatement as varchar(400)) BEGIN EXEC adasdb.master.sys.Sp_executesql @DeleteBackupFileStatement END END
The value of @BackupFile is simply the path of the file with the file name, everything in the prepared statement with the exception of DEL and the switch at the end.
I have used the following useful article regarding exporting a multi-record file: http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
I have created the 9 datasources, ordering each on a field commmon to all.
I have created the required derived columns headers and have merged all the record types into a file.
The resulting file looks fine, except for the odd blank line between record types. Any ideas regarding cause and what to do to fix?
I am trying to use several tables that have one 10-character text field in common. Most of the records have a numeric expression, but some tables have leading 0's, and some don't. I can't cast the field to numbers because there are some records that have letters also. What function can I use to get rid of all the 0s at the left of each record? (Sort of a LTRIM function that gets rid of 0s instead of spaces).
I have a task where I need to process roughly 60000 excel spreadsheets and bring them into a SQL Server 2014 database. All excel files have the same format and same number of identical columns. I have set up an SSIS package that is using Foreach Loop Container to look into a folder and process these files one at a time and load them to a table. The mappings are straight-forward, no problems there.
I am attaching a sample spreadsheet with two tabs - current structure and desired structure.
Basically what I need to do is to repeat the first 7 columns based on the number of lines in a transaction.
How can you delete a log file. Say I have backed up a transaction log at some drive say G:. Now I want to delete this log file and want to define it in a job. What is the syntax - delete G:Test_log.bak ???? What is a proper syntax?? Thanks!
can some one please help me to delete a File with extension .mdf, as its continously giving error that file can't be deleted its being used by any other program or user.
I am trying to remove the transaction log file since I have created another one on a seperate drive by it wont let me do it . I get this error "cant delete the primary data or log file" Now microsoft site clearly state steps for deleting the transaction log file , by first emptying it and then deleting it BUT IT DOESNT WORK. I have even tried detaching the database and then removing the transaction log file and then attaching the database again BUT it attaches the transaction file back again GRRRRRR
then on top of this MS SQL studio does not allow to put PRIMARY or anyother value to the Filegroup for tansaction log files so how on earth can a transaction file be "primary" as the error is stating ??????
can anyone tell me how to get rid of a transaction log file that was created with the database ?
Server: Msg 1101, Level 17, State 10, Line 1 Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.
So went to EM & added another file (Additional file in some other drive ) to the Tempdb , now I want to delete this file ???
What are the steps ... I cant re-start the SQL Server
I can not delete this file from C/Windows/Temp directory, all other files form that foder I can delete. The error message I get is that something else is using this file. The thing is I cant find what is using it, even though I uninstaled SQL Server 2005 completely. I have tried lots of things, nothing helps. At the end I entered windows in SAFE MODE, went to Computer Management and disabled almost every service I can, that might be using it, and still cant get rid of this SQL.LOG which is 26GB now. The only thing I could do is to compress the file and now it is 6.5GB.
In SQL 2012 I'm trying to delete a log file (both physically and logically). There are two log files and one of them is unnecessary. When I click 'remove' in SSMS it will delete the log file, but then when I go back under database properties it's still showing up even though the file has been physically removed from the OS. I'm wondering what steps I can take to get rid of the file permanently?
When I delete substantial amounts of data using the SQL DELETE command, the database size apparently remains the same (702 mb) and the log file goes from about 17 mb to over 1 gig. I was expecting for the overall size to decrease drastically, but got just the opposite.
Is this typical? Can I do something to slim it down? As I am just trying to decrease the overall size to make it easier to work with when creating my application in VB, I am not worried about restoring the db (I have secure copies).
I use discountasp.net. I've added some new tables that I'd like to place on my website. My problem is that if I reattach my mdf file to my server, my member roster gets deleted. So my question is how can I update the mdf file without losing member information?
I have maually deleted the Database.LDF file by stopping the SQL Server. When i start the SQL Server, merge replication which was configured for the database does not work. How do i fix this problem.
I'm new to SQL Server 2005 (up until now I've been an Oracle DBA exclusively) and have been tasked with backing up all (6) of our SQL Server 2005 databases on a regular basis. To accomplish this, I've created individual SQL Server jobs for each database I wish to backup. Each of these jobs executes a T-SQL script like this:
BACKUP DATABASE [MYDATA] TO DISK = N'\networksharemydata.bak' WITH RETAINDAYS = 4, NOFORMAT, NOINIT, NAME = N'MyData-FullBackup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
My goal in specifying 'RETAINDAYS = 4' was so that there would always be 4 days worth of "backupsets" (forgive my Oracle-ese) kept in mydata.bak. But when I look at the amount of backupsets kept in mydata.bak I noticed that all of them were being kept and none were being deleted.
I researched this problem a little bit and thought I had discovered a solution by specifying 'INIT' instead of 'NOINIT' and 'NOSKIP' instead of 'SKIP', but this deletes the entire contents of mydata.bak only-if all of the backupsets contained in it are past their expiration date. I was hoping it would just delete the expired backupsets and keep the non-expired ones, but this isn't the case.
Does anyone know the simplest way - with T-SQL commands - to accmplish the task of backing up a database to 1 individual .bak file and also only retain x amount of "backupsets" within it? Any help would be greatly appreciated, and the more detailed the better. Thanks.
I'm new to SQL Server 2005 (up until now I've been an Oracle DBA exclusively) and have been tasked with backing up all (6) of our SQL Server 2005 databases on a regular basis. To accomplish this, I've created individual SQL Server jobs for each database I wish to backup. Each of these jobs executes a T-SQL script like this:
BACKUP DATABASE [MYDATA] TO DISK = N'\networksharemydata.bak' WITH RETAINDAYS = 4, NOFORMAT, NOINIT, NAME = N'MyData-FullBackup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
My goal in specifying 'RETAINDAYS = 4' was so that there would always be 4 days worth of "backupsets" (forgive my Oracle-ese) kept in mydata.bak. But when I look at the amount of backupsets kept in mydata.bak I noticed that all of them were being kept and none were being deleted.
I researched this problem a little bit and thought I had discovered a solution by specifying 'INIT' instead of 'NOINIT' and 'NOSKIP' instead of 'SKIP', but this deletes the entire contents of mydata.bak only-if all of the backupsets contained in it are past their expiration date. I was hoping it would just delete the expired backupsets and keep the non-expired ones, but this isn't the case.
Does anyone know the simplest way - with T-SQL commands - to accmplish the task of backing up a database to 1 individual .bak file and also only retain x amount of "backupsets" within it? Any help would be greatly appreciated, and the more detailed the better. Thanks.
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)
