JWColby
jwcolby at colbyconsulting.com
Wed May 2 06:55:50 CDT 2007
Arthur knock it off. I haven't even begun to explore what SQL Server in general can do. I asked a whole STREAM of questions about using SSIS to do this and you and everyone else were strangely silent. This list is exactly about getting HELP not "your statements are preposterous". If you can't answer how to do it, that is fine (notice that you STILL aren't offering an answer) but if YOU DON'T KNOW (being the kung fu master of SQL Server) then exactly how am I supposed to figure it out. Preposterous my statements may be (though you aren't showing me proof of that) but insulting your statements are, and not helpful to boot. I came up with a solution to my problem that involved a TOY. YOU OTOH do NOT offer a solution but hint that someday when I too am a kung fu master I will be able to solve my problems masterfully. Which I do not doubt. I respectfully suggest that you put your money where your mouth is. 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 Arthur Fuller Sent: Tuesday, May 01, 2007 11:33 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] Bulk insert 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 > > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com