Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Cralis
Starting Member
11 Posts |
 Posted - 2009-03-04 : 19:33:05
|
| Hi guys,I have been put forward for a position where I need to impliment a SSIS solution that basically pulls data from a poorly designed database, and places it into a new database (I need to design it) that will allow SSRS to pull data from for reporting. Between then old database and the new database will be SSIS which will handle the import of data.Now, I once watched a guy using SSIS, but have never really used it myself. So, I need to learn, and fast. I have good SQL background, but that's not really helping me yet.I have a book, Wrox Professional MS SQL Server 2008 Integration Services, and am on page 27 or something (Out of around 1000!), and it's OK so far, but I'm really in the basics. I have my first question.They have the steps to create small examples. The first was the we placed two 'Execute Process Tasks' onto the Control Flow form. One launched NotePad, and the other launched Calc. That worked fine, but later they talked about a 'Failure' control flow. I can't seem to get that to appear on the 'NotePad' task. It only has the green arrow, which I attached to the Calc. What if NotePad failed to load? Is there no way to have a failure flow path from a Process Task? |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-03-04 : 19:44:43
|
| Cralis same spot you are in. Just open SSIS and start hitting man. It is not that complicated, the only thing is it is so rich it can be overwhelming but otherwise learnable. Especially if you SQL background and DTS it helps a lot. Even if you do not have DTS bacnground. Just open Visual Studio and you will say to yourself , was that it? forget kindergarden mickey mouse notepad calc and stuff. what is it you want to do just dive into it.For failure you can create a template that either dumps into a table you can read and/or send email. Read in this awesome forum adn you will get going. |
 |
|
|
Cralis
Starting Member
11 Posts |
Posted - 2009-03-04 : 19:53:59
|
| Thanks man. So, I'm not alone. You seem a lot more advanced though, but I like the method you're following. "Just go for it." With regards the failure, I'm just trying to understand the basic flow between 'objects' in the Control Flow area. So, I have my 'Notepad' object that runs Notepad.exe, and I have the Calculator object that simply runs Calc.exe. All good, they run, one by one, and all is green. I am trying to expand on the given example though. I want the NotePad object to fail, and if it failes, make it run WinWord.exe instead. So, I added a WinWord.exe object, and found that I can create a link between my NotePad.exe process and my WinWord process, and I can make it red. I made it red by editing the flow arrow, and changing the value to 'Failure'. I then added a flow from WinWord to Calc, Green. So, in english, I want my DTS to launch NotePad. If that goes well, it must then launch Calc. However, if it fails to launch Notepad, it must launch WinWord instead, and then launch Calc.Problem is, when I run it, NotePad launches... and when I close it, I expect Calc to launch - but it doesnt. The package execution ends. :( |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-03-04 : 20:01:27
|
| dude just started on the job SSIS training. Not advanced at all. I just come to this forum and any question asked I try it out to the best of my ability, I read up thouroughly on SQL help and google. If I can't resolve questions asked on this forum I wait for the expert answers here. For your SSIS to make it fail just name notepad.exe to kermit.exe (unless you have an kermit exe :) ) if the two are seperare tasks only the first one will run click on the "notepad" task then you will a little arrow appendage pop up drag it to the calc task, then it is self explanatory. try it |
|
|
|
Cralis
Starting Member
11 Posts |
Posted - 2009-03-15 : 20:09:15
|
| OK, the example have actually helped. I've managed to extract data from a flat file, check that the lookup data is valid, and put it into a SQL table. All went rather well, and you're right - it is straight forward. I've battled to find why a box goes red, but the last bit of what I was learning was about logging, and that's helped a bit. So SSIS, in it's basic form, are good so far. I haven't worked out how to create a row in the lookup table, if the data doesn't fit.For example, say we have a colours table, containing red, white and blue.When then have an import file, with Red Mazda, Red, Ford, White BMW, Green VW.It bombs out on Green VW, as there is no 'Green' in the colours table. Is there a way to make it add 'Green', and continue? |
|
|
|
jmill130
Starting Member
26 Posts |
|
|
|
|
|
|
|
Basic SSIS - I'm in the deep end