James Barash
James at fcidms.com
Fri Mar 30 15:29:43 CDT 2007
John: I had to do something very similar with SSIS and found the Wizard to virtually useless. Even when you do save the package, it seems to be designed in such a convoluted way that it is nearly impossible to modify in any meaningful way. What you need to do is use Visual Studio directly. There is a ForEach loop container that will, among other things, loop through all the files in a directory that match a filespec, and allow you to execute a DataFlow task for each file. You can use the DataFlow task to define the columns of your text files and map those to specific columns in a SQL table. The Help file does have an example of how to do this. I can't say I like the Visual Studio interface, maybe I'm just used to the SQL Server 2000 dts designer, but it will do everything you need, and can be very powerful, but I have found it difficult to learn. Good luck and I hope this helps. James Barash -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby Sent: Friday, March 30, 2007 2:37 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] SSIS packages Yea, I found that. In fact it isn't terribly helpful for a wide variety of reasons. A) My SQL Server uses windows login for everything. Since I only log in as myself... And I run everything as myself... B) I cannot seem to find a place to run the SSIS from inside of SQL Server management studio. I dunno, probably just me. C) I cannot seem to find a way to load the SSIS package from the wizard. That seems a natural thing to do but... I dunno, probably just me. D) When run from a double click the SSIS actually executes but gives about 46 bajillion errors, probably as is common, all related to the first error which is that the table does not exist in the database. NSS, the table is supposed to be CREATED by the SSIS, at least it was created by the setup in the wizard that created the SSIS. I dunno, probably just me. E) Even if I do manage to do that, I still need to edit the SSIS to either change the raw file name, or even add a list of raw file names. I did see a place in the wizard to specify many input files. So it seems obvious to me that I would find some way to edit the SSIS to allow modifying the file list, and even make it append to an existing table instead of creating a new table all the time. I dunno, probably just me. If I were going to build a wizard that allows the save to SSIS, the very first page of that same wizard would offer to open an existing SSIS for modification. I dunno, probably just me.... I just expected a wizard to be a tool for ignorant people and kind of cater to ignorant people. That's why I use it after all. My experience with Access tells me that folks who know the product rarely use the wizards. I went all the way through the wizard, defined 150 fields, ran to completion, it didn't do what I expected but DIDN'T OFFER TO BACK UP anymore, SAVED THE DAMNED SSIS, and now can't seem to do anything with it. Sigh. Unless I figure out how to work with the SSIS I am faced with an endless cycle of defining fields and testing, defining fields, testing... It's not like I don't have real work to do after all. I suppose this is just the dues I am expected to pay to play with the big boys. FYI, I decided to "edit" the SSIS package from explorer and Visual Studio opened. That does allow me to work with the VSS graphically, although I am again not sure how much use it will be. I feel like I stepped in quicksand. 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 Jim Lawrence Sent: Friday, March 30, 2007 2:05 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] SSIS packages Hi John: Have you looked at this link: http://support.microsoft.com/?kbid=918760 yet? Jim -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby Sent: Friday, March 30, 2007 9:51 AM To: dba-sqlserver at databaseadvisors.com Subject: [dba-SQLServer] SSIS packages I need to import 50+ text files into SQL Server. These are fixed width files with 150 fields. I tried to run the wizard and save an SSIS package, and in fact I do have an SSIS package however I cannot execute it. So that is my first issue, how is it that I can create a SSIS package and then not have it function when immediately re-executed? In fact I am not sure that this will help anyway since I would need to either specify a different file name or play stupid games renaming the source file. Is there an editor for SSIS packages to allow going into them and making modifications once created? 1) Is there ANY way to do this via the wizard, so that I can graphically define the column positions, and then save that definition and run it again later? 2) Can the wizard run this "something" that I saved. It seems rather silly that the wizard cannot simply look for something that it saved just moments before, load that something, and execute it. 3) It seems rather silly that after executing the wizard, you no longer have the ability to "go back", you have no choice but to continue, whereupon you have to go back through the whole rather painful process of setting up 150 fields. 4) Is there any way to set up the column NAMES in the destination table inside of the import wizard? With fixed with data there is no header row of field names as there might be in CSV format. SQL Server is simultaneously so powerful and so stupid sometimes! I just want to get work done! 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