James Barash
James at fcidms.com
Fri Mar 30 15:57:04 CDT 2007
John: The Visual Studio that comes with SQL Server 2005 is a subset of .NET so you don't need to be conversant with VB.NET to use it. It is a graphical development system so you shouldn't need to get into the gritty details, like editing the xml or writing .NET code. You can do everything by dragging the appropriate objects onto a canvass and setting the necessary properties. Open Visual Studio and create a new Business Intelligence/Integration Services Project. Then Search the Help for ForEach and that should get you started. The key pieces are setting up the appropriate Connection Manager objects and learning how to use Expressions. Expression are how the ForEach container passes the file names to the Connection Objects. It did take me a long time to get used to it and whenever I don't use it for a few weeks, I feel like I'm starting over since nothing seems intuitive but it can be very powerful. James -----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 4:41 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] SSIS packages James, Thanks for that. I opened the SSIS directly in an editor and it is XML of sorts. It didn't look like it was going to be easy to work with though. Something like an iterator through a directory sounds just right. The problem of course is that I am not up to speed on VB.Net by any stretch of the imagination. Still, given example code I might actually manage that. The SSIS looks like it was designed to be used directly in .NET though. 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 James Barash Sent: Friday, March 30, 2007 4:30 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] SSIS packages 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 _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com