[dba-SQLServer] SSIS packages

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




More information about the dba-SQLServer mailing list