Within a package and finally how to use data views to monitor the flow of data so let's get started before I create the package which will import our data what I want to do is.
Show what is trying to do I've got a tip file setup called apps dot txt you can see the name there which contains three things it contains the name of an app the rating of it and the summary of what.
It actually does and it's heavily influenced by what I've currently got on my iPhone and what we're going to do is import that later into a sequel server table so we end up with the table like.
This the app ID will be generated automatically as a unique number and then we'll import the name of the app it's rating and the synopsis which if you notice has been truncated to 50.
Sequel server I'll load business intelligence development studio instead but they do pretty much the same thing so now what I'm going to do is create a new project.
To contain my packages so I can do that by choosing file from the menu choosing new and choosing project I'm going to make sure that I'm creating an integration services project I'm going.
To make sure I'm not using the wizard and I'm going to give it a name and I'll call it basic project 4 tutorial which seems a reasonable name and then she's okay I'll just close down the getting.
Started window which I I never use and you can see the visual studio has automatically created a package on my behalf called package DTS X I'm going to create.
A different one just to show how it's done by right-clicking on my SSIS packages choosing to create a new SSIS packages and having created it I'm then going to rename it by clicking once on.
Posts Related:
- 5 Mind-Blowing FREE Windows Apps (you probably didn't know existed)
- Power BI: How to Always Show Current Week vs. Prior Week Automatically!
- Excel 2019 VBA Beginner Tutorial (Part 1 of 4)
The name clicking once again selecting
The file name and changing it to the basic package and pressing return and having done that I've got two windows open the top here.What I'm going to do is right-click on the one I want to use and choose close all but this and what that handy little shortcut menu does is close down every window apart from the one I'm actually.
Working with so in order to be able to create a flat file connection the first thing I need to do is create a connection manager and I can do that in one of two places either within the.
Project to make it globally available to all the packages or as in this case within the connection managers for the package itself so you can see it says right click here to add a new connection.
Manager so that's exactly what I'm going to do and I'm going to create a new flat file connection I'm going to leave the name of the connection manager has a default because I think it sums it up.
Pretty well and then I can choose to assign a file name to it I'm going to double click on the file called apps dot txt which contains a list of the apps to it be imported and you can see there's a.
Tick in the Box saying column names in the first data row to signify the fact that the first row of my file contains the names of the columns I'm then going to go into columns and the column.
Delimiter has been cleverly detected to be a vertical bar you can see there's a range of choices but in this case I can leave it as the one which has been selected for me by visual studio I can.
Then go on to preview and check that my data is going to be imported sensibly and all is looking good so I can choose okay so what that does is create a connection to the file on the hard disk.
But what I now need to do is to create a
Data task with a source which uses that flat file connection so I'm going to go to the control flow and I'm going to double click on the data flow task to.Add one in to my package I'll just move it down a bit and what this is going to do if I rename it is it's going to import all the apps so I'm going to rename it like that now.
To go from this control flow task to the corresponding data flow tasks the simpler thing to do is just a double-click on the icon on the left hand side and you can see the.
Window switch from control flow to data flow so eventually I'm going to have a source and a destination in this but let's start with the source telling me I'm getting the information from my text.
File of apps so if I scroll down I'll see there's a section on the SSIS toolbox called other sources and what I'm going to do is click on the flat file source and drag it into my window.
I'm going to rename that and call it list of apps you can see there's a Red Cross to the right of it because I haven't yet assigned which file I'm going to use so perhaps that's a good.
First thing to do if I double click on the icon on the left to edit this particular data flow task what Visual Studio will do is detect the fact there's only one flat file connection.
Manager available to me and automatically assign that to this task if I then go on to columns you can see that it is automatically picking up on the columns in the underlying file app.
Name rating and summary and I could if I like at this point rename those to something different but I'm going to use the default names and just choose ok so that's great I've got my list of apps as.
My source what I now need to do is say where they're going and I need to create a sequel server destination so it's the same story for the sequel server destination as it was for the.
Flat file data source the first thing I need to do is to create a connection to my data and again I can do that either as part of my package or as part of my project now in this.
Occasion I'm going to make it part of my project because it's quite likely I want to use the same connection to my sequel server database several different times in several different packages so I'm.
Going to right click on connection managers in my solution Explorer window choose to create a new connection manager and for sequel server the best one to use is ole ADB I'm going to click.
On that and then choose add now you can see I've got a couple of connections I've created already but just to illustrate what's going on I'm going to create a brand new one by clicking on.
The new button I can choose here the name of my server but anybody who's used sequel server will know that it's much quicker to type it in the full stop there denotes that I'm using my local.
Machine as a server and sequel 2008 r2 happens to be the named instance on my machine I'm using Windows authentication so now I can choose the name my database which happens to be SSIS and then I can.
Choose ok and you can see it's created that in fact it was already there so I can choose ok and what I can now do is create my destination you can see it's put not only connection manager in my.
Solution Explorer window but also in brackets a project version of it for use in my data flow task what I can now do is find my destination to do that I'm going to go to other destinations and.
I'm going to add in no lady be a lady B destination task by clicking on it and dragging it into my data flow window now practice suggests to me that the best thing to do at this point is to.
Immediately link the two things up together so if I click on my source and drag the blue arrow which represents successful data directly on to the destination the red arrow immediately.
Disappears although if I click back on the source it will reappear in the later video I'll show how to reconfigure errors so that they flow to a different place in your task but for the moment.
We'll concentrate on getting our destination to work you can see if I let my mouse linger over it it says that no connection manager has been assigned so what I'll do is double click on that.
Destination and it will automatically assign the only connection manager available in this project which is the one I've just created had there be more than one I would have had to click on.
The drop down list and choose the one I wanted to use what I can then do is choose the table in the underlying database I'm in the happy position of only having one table available to me so.
That's not going to take very long and then I can click on mappings and what this will do is ask me which column in my source I want to go to which column in my destination and what I can do is.
Click on each column and drag it on to the destination column and it just so happens in this case that the three columns are in the correct order but that wouldn't always be the case in.
Which case some of these lines would cross each other I'm not going to do anything about the app ID because the value of that will automatically be set by sequel server it's what's called an.
Identity column so then I can choose ok to confirm that and what I should have is a package which when I run it will import data from the source the flat file into my sequel server table the.
Destination so let's see if it actually works in a previous tutorial I showed the one way to run a package was just to right-click on it and choose execute.
