April 1, 2008 at 3:34 pm
I just spent many hours over the past 2 days trying to get what should be a simple package in SSIS to work; or at least work thru the first part. I spent soo much time because of the extensive T's that must be crossed and I's that must be dotted to get even the simplest of SSIS packages to run. Thanks Microsoft!
Does anyone have or know of a good guide/manual or other similar documentation that clearly outlines all required actions & objects in SSIS to create a basic working SSIS package that exports/imports data between 2 DB's (not necessarily on the same SQL Server) as well as to and from a Flat file using one of the more common providers like the Native SQL or OLEDB Provider for SQL?
I did finally complete a SSIS package but I did not make extensive notes and if I don't do work with SSIS on a daily basis then I'll be facing this same issue again a month or 2 from now. I thought the Microsoft Training Manual I had received during a SQL training class would be sufficient but the Exercises in it I have found are incomplete at best, and confusing at worst when there is no instructor walking you thru the project. The guide's exercises leave out small but important steps that the manuals author must have assumed a reader would must know. Very frustrating. I hope I'm not the only one who feels that SSIS is as far opposite from intuitive as you can get.
Alternatively if anyone knows if it's possible to create a SSI package using the Data Import/Export Wizard in SQL 2005 that can be customized using the BI Studio after the Wizard has completed it that would be great too as the Wizard could be used to create starting point packages but so far I've had trouble finding a way to easily modify the package after it's been created.
Thanks In advance!
Kindest Regards,
Just say No to Facebook!April 1, 2008 at 7:46 pm
You are right. It is confusing and they assume that you spend weeks and weeks on digging forums, asking questions, trying before you understand how to _effectively_ and correctly use the tool.
What you have to really to is to spend some quality time doing hands-on job, such as creating connections, dataflow, writing some scripting, transformation etc to that you understand the tool, so that when you get to it in the future you won't be stuck with dumb questions.
Fortunately SSIS is a visual tool and essentially straightforward. But unfortunately there is little chance you will download a cool example, which will work on your computer for known reasons. Solutions are highly purpose specific and customised/parameterised based on the needs.
Once again - try try and try, and stick around here for a while, don't hesitate asking questions
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
April 3, 2008 at 12:52 pm
Valek,
So would SSIS packages setup to use the sample DB's like advetureworks not be portable and therefore be able to serve as templates? Aside from the login/security settings I would think everything else about the package would be identical betweeb different servers and domains so long as the SQL box has the sample Adventureworks DB installed.
What would be great is an outline or something that detailed the minimum required setup for doing something like exporting from a DB to an external text file, including what order those objects have to be added to the project/package.
OR better yet...
Is there any way to use the SSIS Import/Export Data wizard to create a package that can then be loaded into and edited with the BI Studio? The wizard would be a great and easy way to make sure all the necesay package parts are in the package and hopkked up correctly so you can then tweak it.
Thanks for your response,
Ed
Kindest Regards,
Just say No to Facebook!April 3, 2008 at 1:02 pm
A good book I'd recommend is Professional SQL Server 2005 Integration Services published by Wroks. I found that book extremely useful when writing my first SSIS package, and both my co-worker and I keep a copy handy when working on SSIS packages. It is also a good reference book.
😎
April 3, 2008 at 1:09 pm
Thanks for the recomendation. I have found Wrox to be a very excellent publisher in the past. My only concern is they are usually target at the more advanced user and I'm just starting out with SSIS.
Thanks
Kindest Regards,
Just say No to Facebook!April 3, 2008 at 1:25 pm
As I said, that is the book I used when I wrote my first SSIS package. I had no experience or training using SSIS prior to that, just my experience with DTS from 7.0/2000. It was very helpful.
😎
April 3, 2008 at 1:53 pm
William i am going to suggest you go here:
http://www.jumpstarttv.com/Channel.aspx?cat=c871236d-8554-42e3-8683-4d422356c0bd
and maybe start by the "Using a Basic Data Flow Task in SSIS""
also ssisjunquies at http://blogs.conchango.com/jamiethomson/default.aspx
has very good posts but you will need to search their archives.
Im experiencing the same problems as you since im a begginer.
April 4, 2008 at 5:35 am
I share your pain. I've forgotten more programming languages than most have ever learned, but SSIS simply befuddles me; I guess I am just too old fashioned to have ever been attracted to a diagram oriented programming approach. (I started to say visual programming, but that name has been usurped...)
In fact you can create a starting package using the import/export wizard, save it to file, and later open and modify it in a Visual Studio Integration Services Project.
(And no, it is not at all intuitive.) Just to show my ignorance, having done that several times, and having been able to add additional data flow steps, I am left with 3 or 4 SSIS packages which really should be all in one, but I haven't had the time or nerve to see if I can simply copy and paste tasks into a new Package that combines all the files. Maybe I just need to become more comfortable with the XML source code so I can do some old fashioned rape and paste consolidation.
I'm going to check out the references you were given earlier, in the hope that some of this stuff will start to make sense. Post back if you start to see the light.
April 4, 2008 at 8:50 am
I started using SSIS a few months ago and probably would have killed myself by now if I hadn't bought some training videos from LearnItFirst.
For $40 I was able to download about 130 SSIS videos (approx. 10 to 15 minutes each). There's no replacement for being able to see someone go thru (and explain) all the steps of a particular task.
I'm still struggling with certain aspects of SSIS but I've been able to create some cool dynamic packages. I still think SSIS is a crazy tool with a huge learning curve.
This isn't a sales pitch. I work for Ford Credit.
April 9, 2008 at 2:53 pm
Thanks to all for the replies. It's clear that the difficulty with SSIS is not just 'Me'. I've recently spoken with someone who reads and follows SQL Server very closely and apparently the backlash from users about the diffculty working with SSIS has caused Microsoft to revisit the product for SQL 2008 and re-work it so that it's not so counter intuitive but still powerfull & flexable.
I guess the best way to sum it up is that instead of letting the 'To make a short story long, and complicated, and creative, and orginal' personality type design the interface as was done with SSIS 2005, the 2008 version will be modeled by those who understand user appeal and ease of use principals.
Thanks Again!
Kindest Regards,
Just say No to Facebook!April 9, 2008 at 3:02 pm
The best SSIS book I have found is the Microsoft SQL Server 2005 Integration Services book published by SAMS and written by Kirk Haselden. It explains all the tasks in much more detail than any other book I've ever looked at.
However, I did start using SSIS without a book and struggled through it until I got the book Hands-On Microsoft SQL Server 2005 Integration Services published by McGraw Hill / Osborne and written by Ashwani Nanda. It's an okay book and worked for what little I needed originally, but when I needed more detail on the transformations & tasks that aren't often used, it just didn't have what I needed. It just basically said "here's the task and here's what the editor looks like".
The first book I listed actually tells you things like "This task bogs down the system and blocks processes while it's running, so use it as seldom as possible" kind of stuff. I love it!
I feel your pain and hope you find your future package development not nearly so difficult. I found the best way to learn some of the tasks was to upgrade an old-but-simple DTS package, open it up in BIDs and try to determine what SSIS was doing for the same tasks that I "understood" in DTS.
April 9, 2008 at 4:58 pm
As a SSIS newbie myself, i just successfully created a package, that imports data across two servers, and then performs a lookup and after that an update. It was messy, because i could've written a stored procedure for the same. But i wasnt very familiar with importing across two servers so i used SSIS. I anyways wanted to use it to see how powerful it is.
It is very resource intensive and importing huge sets of data creates a lot of problems. It also not very friendly during "data conversion" when importing.
However there are advantages and re usability is one. Moreover, it seems to be the way to go.
Here is what helped me
1]Microsoft SQL server integration services by Kirk Haselden. Read the first couple of chapters to help you create a package. Try it yourself. From then on read up various components from the microsoft BOL library.
2]Post the errors on this forum or on the official Microsoft forum for SSIS
3]Yeah Jumpstart TV also helps
I am still learning so any advice from "the others" ( i'm hooked on lost these days 🙂 ) would be great.
I'm debating whether i(or my company) should invest the time and money to go into training, it could save a lotta frustration..
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply