[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