Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
I am going to illustrate how to create a basic package by doing something very simple. And I'll make a new project here in the Business Intelligence Development Studio. So create a new project. It's going to be Integration Services and I am going to call it the FlatFileTransform. Because we don't actually need to move our data in or out of SQL Server. That's an easy mistake to make. Yes, Integration Services runs as part of SQL Server but it's quite happy to move information from an Access database into an Oracle database or from a flat file into Access or from SQL out into Teradata.
Whatever you can connect to, it will work with. So I am going to begin with about as simple as you can get. One Data Flow Task. Our entire package is going to consist of this one step. Now inside it, we will have several steps, what are we going from, what are we going to, but this will do. So I double click it and I jump into the one Data Flow Task. What I am going to set up is a Flat File Source. I am going to then affect some of the text files inside it by doing a Character Map and if I mouse over character mapping, see that it says it applies string operations to character data so that's the one I want.
I am going to sort and then I am going to have the output be another flat file, a Flat File Destination. So let me go through and configure this step-by-step. Again, we are doing this to get the idea of the process. I define the source. I will double- click this first part. Bring this dialog box a little smaller so it's readable. And it's going to say, do I know about a flat file right now? No, I don't. I will have to click New. Now anytime, you're defining a connection to something in the outside world, a database, a flat file, an Access database, you typically have to give it a connection and give that connection a name.
So I'll call this FlatFileConnection. I will leave the description and it's asking, "Where is that file?" It could be on a network share, could be, as is my case, just a simple text file on my desktop. It's called names.txt. If I click through and look at the columns, what I actually find is that I have got some columns being picked up right now. Its name, first name, last name, email. It does look like I've got some columns in that first row.
And if I click back on the General tab, it will tell me. There is why I describe that. Column names are in the first row. This is just a very plain delimited text file. Clicking through it, it's read as much as it can out of that. It's done the usual thing but it tries to take a guess of what this data should be. We have got first name, last name, email. Well, I know that my email might be a little longer than 50. So I am going to make that 100. If this looks familiar, it's because it is very, very close to what you get when you are using the Import/Export wizard.
It's a little different than when you have to define a connection but the rest of it is kind of the same thing. You're describing what the data actually is. Going back, I can pick up the columns and it says "Yes, as far as I know, we've got FName, LNmae, Email, City, and Zip." You can define the Error Output, which is if there's any problems with this, what do we do? If there is an error, do we fail this part of the process? Do we ignore failure? Do we send that one particular row somewhere else? I'm going to say if there's ever any error and if there's ever any truncation, we are going to fail this part. So I click OK.
I'm going to say that data was good. I'll drag the little green arrow down to Character Mapping. What does Character Mapping do? If I double-click on it, I have defined what the input is. So it says okay, these are my available input columns. Which ones do you want to affect? What I would like to do is take any of the last names and I just want to change them in place. And in fact, I have the option of creating a new column or I can do an In-place change. And then, it says well, what's your operation? Lowercase, Uppercase, Byte reversal, Japanese Hiragana and Katakana? I'm not going to do any of that.
I'm just going to say let's convert the last name to uppercase. Click OK, click OK. That's a basic character mapping. Well, then what? Taking the output of that on to the Sort. Double click Sort to define him. What are we sorting on? Well, let's sort on last name ascending and then first name ascending, if there're any people with the last name. Do I want to remove rows with duplicate Sort values? No, definitely not. So I click OK and we have the Sort. The output of the Sort goes to the Flat File Destination.
We will double-click it. I can say do we want to use the same manager as last time? Basically, we are getting the option here, do we want to overwrite the file that we had before? Well, I could, but I'm going to say no, let's make a new one. It's going to ask me, do I want to create a delimited flat file of fixed width, fixed width with row delimiters? What do I want to do? Let's just accept the default as delimited. I click OK and it's going to ask me again describe very similar stuff except what's the output file is going to be called.
Well, I'll browse to the desktop and just invent a name. I'll call the output as output.txt and click Open. Then it's going to ask all these questions. Do I want this in Unicode? Yes, certainly I do. Do I want the column names in the first row? Sure, why not. I can take a look at what it thinks the columns are going to be with FName, LName, Email, City, and Zip. It looks okay. We can preview if I want to, but I am just leaving it because we haven't run this process. I am going to click OK. I do need to take a look at the Mappings page here before it lets me click OK, but I'm not going to change anything.
It's still following the fact that because I hooked up the green arrows, it knows what the available input is and what the output destination is. So, I am going to just click OK. And I am going to say I think I am done. So, I'm going to save this. I am going to go ahead and run it. I can click this little Start Debugging arrow up here. The benefit of doing it that way is even though this will happen very quickly, in a full production environment it will be quite slow and you'll have the steps showing up in green or red when they work. So let's try.
Now we have green, green, green, green. Okay, looks interesting. I get this message down here that all the different components have occurred, that the end result is apparently a success. So let's take a look. I will minimize this and go to my desktop. My input was this file. Names, comma delimited, and my output is this file and it certainly looks as if it did the trick. We've got comma delimited with line breaks in between them. We've got the first row with the columns in it and we have the capitalized and sorted output.
Yes, SQL Server Integration Services is a little bit of overkill for that, although even transforming a text file like that can certainly take a little time if you have to do it manually. But that's the general process. The real key of what you're doing is always going to be in the Data Flow and then you can typically surround that with Control Flow. Control Flow doing things like checking the integrity of the database, notifying an operator, sending it in the email, accessing something from FTP. But I find working with SQL Server Integration Services projects, it's really easy to begin with a simple one and then you can start injecting and dropping in the new and the more complex tasks around it.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.