But my problem is that the firmat may not be generated in Windows and i can't user "
" as row delimeter.
It could be generated in Unix and want to use LF as the row delimeter.
Questions :
1) How do i specify this kind of new line (LF ) in formatfile?
2) Is there anyway that i can specify both the delimeters and let it use which ever is appropriate ?
How to create a text file in UNIX format using Flat File connection manager. By default when we create a connection manager for flat files it is taking "CRLF" as the delimeter.
I beleive the format of the file will be decided based on the control line feed character at the end of each row. There are different control line feeds for different operating systems.
CR - Mac OS (Carraige return) LF - UNIX (Line Feed) CRLF - Windows. (Carriage return Line Feed)
First, let me start by saying I know the answer to this, but due to politics any answer I give will be viewed with some disdain and disbelief.
A Unix/Network [rtdpmin one our sister agencies is trying to solve a problem that really does not need to be solved, but ....
The sister agency uses Information Builder's WebFocus on Unix. When they try to read my data warehouse in SQL Server 2000 it times out on them (on their end and they claim they cannot fix it) (This is using the supplied WebFocus ODBC/JDBC driver for SQL Server). This has lead to accusations of us not letting them read the data.
After much gnashing of teeth over "if you do data and data analysis for a living, why do I have to show you how to set-up an ODBC connection" we have shown them how to connect to the data, read it, and transfer it using MS-ACCESS, EXCEL, and more importantly SPSS. This is not good enough because they cannot figure out how to put it into WebFocus.
Their network person has come-up with the brilliant idea of reading the .mdf directly by installing Windows for Unix on the Unix side and then just pointing to the .mdf file and "reading it ".
Unless I have missed something somwhere (I will admit possible), I know you need a driver to read MS-SQL and that driver in every example I have seen could care less about the physical location of the .mdf file. It wants to know what database, what server (ip or name) and what security/login to use.
Could someone give me a more "technical answer" or even Microsoft's party line so I do not have this person mucking around with my production server trying to acomplish the impossible.
I'm trying to extract data from a Flat File which is as fixed length as they come. The file has a header, which simply contains the number of records in the file, followed by the records, with no header delimeter (No CR/LF, nothing).
For example a file would look like the following:
00000003Name1Address1Name2Address2Name3Address3
So this has 3 records (indicated by the first 8 characters), each consisting of a Name and Address.
I can't see a way to extract the data using a flat file connection, unless we add a delimeter for the header (not possible at this stage). Am I wrong?
Any suggestions on possible solution would be much appreciated - I'm thinking Ill have to write a script to parse the file manually.
I am dealing with a lot of XML file data that I load in a SQL 2005 database using Integration Services. Let me give you a high level idea of I how I do it.
The way I do it is that a) I create a blank Integration Services Project, then b) In the "Control Flow" tab I put the "Data Flow Task" by simply dragging "Data Flow Task" on to the "Control Flow" page. c) Then in the "Data Flow" tab I drag the "XML Source" as my data flow source, and drag "OLEB Destination" as my data flow destination; then I create a connection between the "XML Source" and "OLEDB Destination" and point the "XML Source" to my XML file, and I point the "OLEDB Destination" to the Database where I want to load the XML file. I configure the mappings etc if needed and run the package. This works fine for me. But if the XML file is large it takes hours to load the data. I am dealing with huge size XML files and I want an alternative in Inegration Services probably "Bulk Insert Task" but dont know how to use it. Can someone please explain how I can use bulk insert as a fast alternative to load the data. Please list the steps.
I have the misfortune of converting a DTS package to SSIS that loads a flat file that has a text fields that can contain embedded text delimiters ("), column delimiters (,) and even new lines (CR+LF i.e.,hex 0D 0A) in it. A sample line from the file is posted here, remember this is just one line though it shows as three lines, since the third field has embedded new line in it:
4,"Sam","EVP; MARKETING PRODUCT MANAGER ""Level I"", Internet Sales / HELP 8005551212",100
If you open in excel it handles it perfectly showing four fields, as below, and this is what I want ( I cannot get it aligned right in the posting, just save the above line in *.csv and open to see what it should be):
4 Sam "EVP; MARKETING PRODUCT MANAGER ""Level I"", 100 Internet Sales / HELP 8005551212"
Now, SSIS errors on the embedded text delimiters and breaks into two or three lines based on which option I chose. I have tried few options based on postings in the forum: a). Using undouble and undoubleout: Does not work when there are embedded column delimiters (,) in the text field b). Modified undouble script posted by lvovg at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1718225&SiteID=1. Handles the embedded column delimiters (,) perfectly, but the embedded new lines (CR+LF i.e.,hex 0D 0A) are breaking it. Since, I am using the ragged right format to read from the file then use transform script on the line by lvovg, the line is already broken by the ragged right format at the embedded new lines, hence does not work.
Right now I am stuck. Can someone please help (anyone from MS) ? I am already baffled at the amount of coding required to convert a very basic ( and working ) flat file load DTS package to SSIS. I am willing to persist bit longer to convert this to SSIS, before I give up and stick with DTS and wait for a fix / workaround.
I need to create a File DSN on AIX Unix server which can connect to MS SQL server 2005 on a windows machine.Can anyone please give me step by step procedure how to do it.Its an urgent requirement. Please help me.
I want to make a CSS Style solution for my reports. I have few ideas related to this, changing the report properties on the fly, but I am not sure for which I should go for.
- We know that we can put expression on reports almost everywhere, and can also call custom code for setting up those expressions. i.e.
Public Shared Function GetValue(ByVal Key As String) As String
Dim myDataReader As SqlDataReader Dim mySqlConnection As SqlConnection Dim mySqlCommand As SqlCommand
mySqlConnection = New SqlConnection("server=localhost;Trusted_Connection=yes;database=tempMIQB") mySqlCommand = New SqlCommand("SELECT * FROM [Properties] Where PropertyName='" & Key & "'", mySqlConnection) mySqlConnection.Open() myDataReader = mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
If myDataReader.Read() Then GetValue = myDataReader.Item("PropertyValue").ToString Else GetValue = "None" End If
End Function
and in textbox or any where I can say =Code.GetValue("BGColor") or from .NET dll.
For going further on with this idea I can create an .NET dll and get the list from database or XML file. (Please give suggestions for performance/scalability issue.)
- second idea is to write a custom application (script) which can go through all the reports and change the color and fonts and every thing.
- third is to use parameters and use Array.IndexOf method to search for the value, but in this case i need to add dataset to all reports and, I think we cannot access report properties i.e. Parameters from custom code, just to make a generic function to access parameter value.
Hi, I have a script written in ASP to load data file (.csv) to ms sql. In the script, I have a portion of script looks like taht : ..... Do While NOT oInFile.AtEndOfStream oOutFile.WriteLine Replace(oInFile.Readline, chr(13), vbcrlf)Loop ..... After that, I will use a BULK INSERT to input data to ms sql. I am wondering how do I convert each row (data) to vbcrlf in Stored Procedure? Coz' I did not compose the convertion part, and I got no error when running BULK INSERT, but no rows are inserted :( HELP!!!! I guess it's because the file is not being converted into a correct format??
I am importing all the files from a particular folder to a table on my database KB. It is working perfectly if i use it on the same system where the DB exists and not working from the network. Also this scritp is working perfectly on sqlserver7 and not working on sqlserver2000.
SQL2000 returns error near comma
set @query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'" WITH ( FIELDTERMINATOR = ","(returns error here) ,ROWTERMINATOR = "")'
USE TESTDB
--Table Creation Starts here
Create table Account([ID] int IDENTITY PRIMARY KEY, Name Varchar(100), AccountNo varchar(100), Balance money)
Create table logtable (id int identity(1,1), Query varchar(1000), Importeddate datetime default getdate())
--Table Creation ends here
---Stored Procedure Starts here
Create procedure usp_ImportMultipleFiles @filepath varchar(500), @pattern varchar(100), @TableName varchar(128) as set quoted_identifier off declare @query varchar(1000) declare @max1 int declare @count1 int Declare @filename varchar(100) set @count1 =0 create table #x (name varchar(200)) set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"' insert #x exec (@query) delete from #x where name is NULL select identity(int,1,1) as ID, name into #y from #x drop table #x set @max1 = (select max(ID) from #y) --print @max1 --print @count1 While @count1 <= @max1 begin set @count1=@count1+1 set @filename = (select name from #y where [id] = @count1) set @query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'" WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "")' --print @query exec (@query) insert into logtable (query) select @query end
I am trying to import comma delimitted text files which contain data for any particular country.
I have created SSIS package to import data into sql server table. table structure is exactly same as text file except one additional column which is to identify the CountryID. Text files we recieved does not contain CountryID column.
I am using BULK INSERT Task in SSIS to import the data as text files are quite big (up to 1 GB). during processing I do determine the country id for that text file as file name contain countrycode which can be use for lookup and get the countryid.
cyn_bills_20071208_032242.txt
CYN is the country code and country table has got country id for it.
I am using Format file with BULK INSERT task as table has additional column which does not present in source file. currently after inserting all rows into table i have to run an UPDATE statement to update the value of CountryID column which is very expensive some time taking more then 30 min for (30 million rows)
Can anyone tell me how to insert an expression when using BULK INSERT command / Task in SSIS. there must be a way to insert litterals using Bulk Insert.
I am stuck at one place, where I have to convert CSV format file data into SAP IDOC format file. In SSIS we don't have any such SAP adapter (though we have .NET Data Provider for mySAP suite [SSIS SAP Adapter] but this is still not fully supported by Microsoft, plus it doesn't have feature to convert data into IDOC format) that can do this. Can someone here please provide me some pointers on any third party adapters available in market to do this job or if anyone has already developed some custom approach to achieve this task?
Your quick response on this is highly appreciated.
I can skip the first line with Transform Data Task, it look like can not skip the first line in BulkInsert. But bulkinsert is faster, anybody can help?
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();
SqlConnection.Open(); cmd.ExecuteNonQuery(); SqlConnection.Close(); RefreshData(); I am running Visual Studio C# Express 2005 and SQL Server Express 2005.
Hi,I am trying to use BULK INSERT with format file. All of our data hasfew bytes of header in the data file which I would like to skip beforedoing BULK INSERT.Is it possible to write format file to skip these few bytes ofheader before doing BULK INSERT? For example, I have a 1 GB data filewith 1000 byte header. Except for first 1000 bytes, rest of the data isgood for BULK INSERT.Thanks in advance. Sorry if it is really a dumb question as I am newto BULK INSERT and practicing still.Bob
Hi everyone!I'm be stuck by a simple issue about newline in C# and has relative with SQL. I wrote a SQL sentence like that: IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME = 'TG1' AND TYPE = 'TR')" + " DROP TRIGGER TG1" + " GO " + " CREATE TRIGGER TG1 ON FILES FOR DELETE AS DELETE table1 FROM" + " table1 INNER JOIN DELETED ON DELETED.ID = table1.ID" + " GO " + " DELETE FROM table2 WHERE ID = 'ID00001'It's executing ok in SQL but when execute programming then " GO " have to break newline ( or System.Environment.NewLine), However seem have not mean for SQL Execute becoz will be appear in SQL sentence. I'm not have experience about GO and TRIGGER so have any idea for this problem! Thanks so much.
I have a SQL 2005 stored procedure to generate an email when passed parameters such as receipient, subject etc One of the paramteres passed to it is @body which is the body text of the message. I want to be able to add a couple of blank lines and then some footer information. This is working right now except I can't find the right way to add newlines into the string within the store procedure, so my footer information just tags right on after the bodytext. I have tried but that literally adds the two characters and n Can anyone advise how to generate newlien sequences in T-SQL. Regards Clive
Hello, I have data in a database table that has newline data in it. It has a newline character in front of the text of the data, and for some reason, I can't figure out how I can replace it. I've tried entering in a return character between '' and I've tried trimming the text... How do I represent a newline character to remove it from the text? Thanks.
what is the newline character in sqlserver. My problem is ,I have to display text (say "Hi abcd hhh") where is the newline. But when i print this in sqlserver storedproc,Its displaying as it is instead of newlines.
I am using Derived Column Transformation Editor. I have 3 string values which I would like to combine them into one string and have a newline character inbetween the 3 strings. I cannot see a Char() function similar to TSQL to use for this purpose. I thought about creating a Variable but even to that I don't know how I can assign a newline character.
