At creating a project and package within SSIS so firstly by creating a project in a package itself then within the package creating control flow tasks to monitor flow of execution for the package.
Creating data tasks with the source and a destination and finally how to run a package to import data from a sequel server table into an Excel workbook so let's begin in order to be able to.
Create projects in integration services I need to be able to load a visual studio and I can do that in a couple of ways depending on which version of sequel server.
I've got installed so if I go to the Start menu and go to programs I can find sequel Server 2012 and within that they'll be a full of an icon called sequel server data tools and what that.
Because that's a bit of a mouthful most people tend to abbreviate it to bids and although that will load up Visual Studio 2008 which sounds like it's all very different in practice it's virtually.
Indistinguishable from sequel server data tools but I'll go for the latest one sequel server data tools and what this will do is load up Visual Studio 2010 and as you can see the first thing.
It does is load the start page now what I would much rather see is the thing I was just working with so the first thing I'm going to do is reconfigure Visual Studio to tell it when I next go into.
Visual Studio it should display the project I was last working with and I can do that by choosing tools from the menu then choosing options going to the.
Environment tab and choosing the startup tab and changing it so at startup instead of choosing the start page it chooses to load the last loaded solution which is in my opinion a much better.
Posts Related:
Option so I'm going to choose ok to - Make Automate
Confirm that and then I'll close down my start page because I don't really like it there's also a window appeared for getting started in SSIS so I'm going to.Close that down and in fact I'm also going to close down my tool box because while this is useful for other visual studio applications it's actually completely irrelevant for integration.
Services and quite confusing SSIS does have its own toolboxes we'll see shortly but this isn't it so I've got a fairly blank window what I'm now going to do is display the two most.
Important windows in integration services and they are solution Explorer and the properties window so let's start with solution explorer if I choose view from the menu and choose solution.
Explorer then it opens up in my case on the right-hand side now like all windows in Visual Studio it's got a pin at the top right and this can be in one of two positions red on.
Ttle as there or vertical when it's vertical you can dock the window anywhere you like on screen so you can drag it round and position it on any one of these arrows and what it will do is.
Dock the top or the left or the bottom or the right of your visual studio window so I'm going to keep mine on the right hand side because I'm a creature of habit and that's why I always put it.
And I'm now going to click on that pin again to make it horizontal so that it auto hides the window and an auto hiding window is good news because it doesn't take up any of the screen unless you.
Actually move your mouse over it and then you can see its content so I'll just display the properties window to go alongside it which will be useful later on and I'm going to dock.
That as well on the right hand side and
Then auto hide it so I've got my two main windows visible and it's time now to create a project but just before I do that let's have a.Look at what the project is going to do in sequel server management studio I've got a table of countries and you can see it contains eight countries and for each there's a country ID and a country name.
And what my project is going to do is to export these into an Excel workbook so that I can use them now there's easier ways to do this I'll be the first to admit copying and pasting springs to.
Mind and this is a bit of a sledgehammer to crack a nut but it does illustrate how to create basic integration services projects so the first thing I'm going to do is go back to visual studio and what.
I need to do is to create a new project to hold my package which will do or my data import manipulation and export to create a new project you can choose file from the menu then choose new and then.
Choose to create a project and it depends what sort of person you are what you do next if your C sharp programmer you'll doubtless create a windows forms or WPF application or an air speed on.
Their website if your VV VB program or Visual Basic programmer you'll do the same thing in Visual Basic but we're data analysts so we'll create a business intelligence project weather.
For analysis services cubes reporting services reports or is in our case integration services which will allow us as it says on the right to extract transform and load data and I'm going to.
Call my project my project and then choose ok so what integration services will do is firstly load up a window called getting started in SSIS which I'm going to close down because I assume.
You're watching this project so you can avoid that and it will also create the SSIS toolbox which I referred to earlier so that's going to be useful later so we've got our package visible on screen.
And it's got two main windows it's got the control flow which you use for creating flow chart type diagrams explaining in what order the various parts of your package will execute and.
It's got the data flow which allows you to create data tasks which is you can think of as like controlling a river of data controlling the Audion which it runs through your package if you've ever.
Had a marble run as a child imagine a marble run full of data instead of marbles and you'll have roughly the right idea so I'm going to go back to my control flow and I'm going.
To create two tasks what I want my package to do when I run it is firstly to display a message saying welcome to this package or something similar and then to actually import the data from.
The sequel server table into the Excel workbook so for the first task I'm going to go on the left hand side to the common tasks and find a script task and click on that and drag it where I wanted.
To go and what visual studio will do is create the task which you will call script tasks and there's always a little icon display to the left of that which shows what sort of task it is in this.
Case it's donating this is going to be a script written in either visual basic or c-sharp so I'm going to rename that and I'm going to call it display reassuring message the only reason I've included.
This task is just to illustrate that you can have more than one thing in the control flow my second task is going to be a data flow task so I can click on that and drag it in you'll notice data.
Flow tasks are so common that they're in the favorites at the top of the SSIS toolbox so if I drag that in to my control flow window and position it where I wanted to go and I'm going to.
Rename this again by clicking once on it and I'm going to call this import countries into Excel and then press return if I then click back on the first task you'll see there's a green arrow.
Coming out of it and this is called a precedence constraint and what I'm going to do is drag that green arrow on top of the second task so to ensure that the two tasks execute one after the other.
And if I click off that is actually easier to read and that green arrow will stay visible no matter where I move these two tasks to so that completes for the moment my control flow except that.
My script doesn't actually do anything so the next thing to do is to configure this script task so that it does what it says it does on the tin and displays a reassuring message in order to be able.
To edit my script task I need to double click on it and what that will do is bring up the script task editor dialog box which allows me to edit the script and I can do that by clicking on the.
Button at the bottom right corner of the screen but just before I do that I've got a choice to make do I create my script in Visual Basic or do I create it in c-sharp Microsoft seem to think.
C-sharp is more common in the world and I think I'll probably agree with them so somewhat reluctantly I'm going to go with the default choice and click on the edit script button what's happening now.
Is Visual Studio is creating a completely separate project just to contain this script you can see the project's name is I won't bother reading it out but it's clearly been generated.
By a computer and it's created a script called script main CS now later in another tutorial I'll explain more about what this is actually doing for the moment suffice it to say that there's a.
Programmer a function called main which is going to run which is going to return the fact that the script has been a success and all I need to do is added line into this to display my message and.
