Arthur Fuller
fuller.artful at gmail.com
Tue May 1 22:32:31 CDT 2007
To put it as gently as possible, you haven't even begun to explore what SSIS can do. Several of your statements are so preposterous as to lie beneath rebuttal. I suggest that when you have a spare weekend, you investigate the SSIS documentation. There you will find abundant insight into how to handle CSV and fixed-width files, not to mention a whole lot more. I'm sorry that the wizards couldn't get you from here to there, but just because you couldn't figure out immediately how to do it does NOT mean it's the tool's problem. SSIS is a quantum leap beyond what DTS could do, and even it could handle your CSV and trailing spaces problems without difficulty. I respectfully suggest it's time for input (read some documentation) not output. This is not to say that SSIS is problem-free, but the trivial issues you raise are solvable in a few minutes of reading. Arthur On 5/1/07, JWColby <jwcolby at colbyconsulting.com> wrote: > > ROTFL. One problem I have is a "big boy" database that requires a toy to > feed it data. > > And yes, that is the fault of the tool. Luckily I am an expert at > programming the "toy" so I can get around the shortcomings of the ... > Hmm... > "big boy's database". > > I do notice that after all my posts there is dead silence about getting > SQL > Server to do what I need in an expeditious manner. Having combed the web > I > also find that there are hundreds of posts about this shortcoming of SQL > Server. > > "Oh, well... You can import to a temp table and then use xxx to strip the > spaces and then use YYY to get the data where it really should have been > put > in the first damned place by this "big boy's database". Yea, except that > I > have 60 of these files to do, and another 100 coming in a few weeks and > another 50 shortly after that. It is waaaay faster to program my "toy" to > feed it data. > > I will be the first to say that SQL Server is a powerful database engine, > that is why I use it. I am also however willing to admit that it sucks in > some very visible ways. > > Learning curve my hind ass. > > John W. Colby > Colby Consulting > www.ColbyConsulting.com > > -----Original Message----- > From: dba-sqlserver-bounces at databaseadvisors.com > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David > Lewis > Sent: Tuesday, May 01, 2007 1:29 PM > To: dba-sqlserver at databaseadvisors.com > Subject: [dba-SQLServer] Bulk insert > > > > John: > > There are a few websites concerned with sql server, with active forums. > I recommend you check them out. One is sqlcentral.com, the other is > sswug. > > You are right, access is a 'toy' when compared to sql server. One problem > you are having is that you don't have the luxury of a learning curve -- > but > that is not the fault of the tool. Hang in there. D > > > And that is exactly what I am doing and it is soooooo KLUDGY!!! > > I am running a program I wrote to read the data out of the original fixed > width file because SQL Server won't handle fixed width files and strip off > the spaces. How incredibly stupid is THAT? Is there ANYONE out there who > WANTS those spaces? So I am already using KLUDGE to get data into SQL > Server. Now I export it out to a perfectly valid CSV file only to > discover > that SQL Server BCP and Bulk Insert don't even look at (understand) quotes > around comma delimited fields. > > But ACCESS does. But Access is a TOY remember? What exactly does that > make > SQL Server that it needs a toy to feed it data? > > This has been an exercise in discovering just how brain dead the data > import > processes are (or can be anyway) for SQL Server. This is NOT rocket > science. I am able to write a utility to open / import / mangle / export > it > back out to another file in VBA. How tough can it be to do this import > inside of SQL Server natively? > > I have no idea how widespread this kind of file is but I can tell you that > that is all I see EVER in the industry I am dealing with. HUGE files, > fixed > width, space padded right. And I can tell you they have been a royal PITA > to get into SQL Server. > > At least now I have my own utility that can get I these input files into > the > format I need, even if it is in ACCESS/ VBA. My next step is to port this > to VB.Net so that I can do it a little more "natively". Once I get a > little > more familiar with VB.Net I want to look at storing the data right into a > recordset in ADO and then write that back to SQL Server. If that is too > slow (I suspect that it will be) then I can still do what I do now and > import / mangle / write to file and then run a stored procedure to do a > Bulk > Insert from the file I create. > > John W. Colby > Colby Consulting > www.ColbyConsulting.com > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >