OK. I am new to SSIS. I do not have control over the servers, so I have to provide a list of what features we need installed and such.
In order to use SSIS to import data files (from multiple flat file sources), what do I need installed?
I have SQL Server 2005 installed on a server. But the Business Intelligence Development Studio isn't present. Do I need to install that on the server? How about any services?
Also, can I/Should I install Business Intelligence Development Studio on my local development machine? What about licensing requirements for it?
Thank you for your help in this matter. Usually I have full control over the server and I can just poke through the install to get what I need.
Wow -- this is a big question, and you're going to get a lot of answers from different people.
The "Business Intelligence Development Studio" (BIDS) does not have to be installed on the server. The output from BIDS is a mangled XML file with a .DTSX extension. This file is processed by either the DTExec or DTExecui utility on the server. These utilities are installed along with SQL Server by default (in the Binn folder, I believe).
As a minimum, you'll need BIDS installed on your development machine. Because of licensing issues, DTExec and DTExecui only run on licensed servers. This means that, although you can create and run DTSX packages from your development machine from within BIDS, you won't be able to emulate server behavior on your local machine unless you've got a licensed copy of DTExec/DTExecui on your machine. IOW, you won't be able to run your packages at the Windows level without DTExec and DTExecui licensed on your development machine (this has hung up a lot of people -- the "insufficient product level" issue has been posted dozens of times on this forum)
Most of the SSIS tutorials you'll find on the Internet are laughably simplistic, and ignore real-world situations. You'll find dozens of brain-dead examples that import a well behaved 2- or 3-column flat file into an existing SQL Server table. While these examples are good for getting started, it'll take you 10 minutes of working with your own data to encounter a real-world situation such as permissions, ill-behaved data (missing delimiters, for instance), and issues with non-compliant data (such as column that look like dates, but aren't acceptable to SQL Server).
That's what this forum is all about. Lots of good help here.
|||Thank you for the response. That answers a lot of my questions. I couldn't find anything that talked about getting started with this.
I'll go find the CD's to install BIDS locally. I think I can test everything well enough through BIDS before deploying to the server. This is also a new app, so we will have a full testing phase on the server to catch anything I miss.
I'll definately be back if I get stuck on writing the SSIS packages. I have 15+ file formats to massage all into the same table... :)
|||That's precisely the sort of situation where the majority of examples you'll find will be of little help. Stuffing 15 or more formats into a single table will be challenging, depending on what you have to deal with.
Obviously, as with anything else, start with something you expect will be easy, such as plain text to a varchar or char column, and work up to the more challenging issues. In my case, I had some trouble with date values -- some of the columns I import are given as numbers (20070104), while others are text "01042007", and some include punctuation (01/03/2007 or 1-3-07). Cooercing these values into a format that's acceptable to SQL Server can be a challenge, sometimes.
Good luck! Jamie Thomsons blog (SSIS Junkie at http://blogs.conchango.com/jamiethomson/) is an invaluable source. By and large the support you'll get on this forum is top-notch and you shouldn't have too much trouble getting over the initial humps.
BTW: SSIS continues to surprise me an obvious paradox: while it's very powerful, it's got some of the quirkiest quirks I've ever seen in a Microsoft product. For instance, although the Script Task editor looks a lot like the standard Visual Studio editor, many of the menu commands do nothing -- you can't, for instance, "build" your script to check for errors. The only way I've found to validate my VB code is to actually run the package, or, at least, run the Script Task (right-click on a task in the Control Flow tab and select "Execute Task" from the shortcut menu) by itself to see if it's working.
Just a few of those "oh, so that's how it works" surprises now and then, but basically a good development platform. Much, much more powerful and flexible than DTS, to be sure.
sql
No comments:
Post a Comment