SSIS Hello World

I watched a video presentation last night on how to do a "Hello World" in an SSIS application. It answered a lot of questions I would not have answered for myself without someone holding my hand. The dummy use case was to take a bunch of records for contacts out of a .csv (commas separated value) file and put them in a database table. The contacts in the .csv had zip codes but lacked city and state values so those had to be drummed up by using a lookup table from a database as a step midstream in the chain of events. Packages contain task flows in SSIS. A package is typically a .dtsx file, but it could also be something stored at an MSSQL database. To use SSIS you will need BIDS (Business Intelligence Development Studio) installed. If you type "component services" at the start menu you should be able to see a list of services running. Hopefully you will see "SQL Server Integration Services 10.0" running. If so, you are golden. The next thing to do is to start BIDS. Go to "Project..." under "New" under "File" and make a new "Integration Services Project." The screen will be split. There will be four tabs across the top. The first two are "Control Flow" and "Data Flow." The lower fifth or fourth of the screen will be a subsection called "Connection managers" independent of the tabbed area. Right-click in "Connection managers" to make a "New Flat File Connection..." and fill out the dialog box which appears. The "Advanced" part of the dialog will allow you to assign column names to columns... and data types too. When you are done make a second connection. Pick "New OLE DB Connection..." this time and make a connection to a database where records will ultimately be put. Add a "Data Flow Task" at the "Control Flow" tab by pulling it out of the "Control Flow Items" panel. You may right-click in the task to massage its properties. Next go to the "Data Flow" tab, which show come up within the context of the "Data Flow Task" you just made. If you renamed the "Data Flow Task" you should see the name at the "Data Flow" tab. We will next drag three different controls into the "Data Flow" tab. The first task drug in from the side panel will be a "Flat File Source." Somehow the new task gets associated by default with the first of the connections. Perhaps because there is only one flat file connection. In watching the video, it seemed automatic. This association is crucial and cornerstone to our process. Again, you may right-click in this task to tweak its properties. The task will have a green arrow and a red arrow descending from it. The green arrow represents a happy pass scenario in which we read from the .csv file and then go on to what is appropriately next. The red arrow represents where we should go in the event of trouble, failure, drama, and heartache. Drag a "Lookup" into the Data Flow tab next. Drag the green arrow from the other task to the "Lookup." Tweak the "Lookup" into getting the state and city by right-clicking it and messing with the dialog box which appears. This task will use the OLE DB Connection. Finally, drag in an "OLDB Destination" task and make the green arrow that now comes off of the "Lookup" go to the "OLDB Destination" task. This task will have a red X on it until you configure it by, yes, right-clicking upon it and then monkeying with settings in a dialog box. The input setting needs to be "Match Output" and the output setting needs to be the OLE DB Connection. You will have to configure what "columns" from the .csv file map to which columns in the database, etc. Pick "Start Debugging" from the "Debug" menu to test and all three of the tasks in the chain should turn green. The "testing" will actually write to the database too. It is the real thing. The example in this blog posting is completely plagiarized from another source. :P