This post explains in detail about creating a SSIS package in VS 2008, project folder structure and designer. You can read this post to get understanding on SSIS.
BIDS(Business Intelligence Development Studio) can be found after installing the full version of SQL server 2005 or 2008. I am using the 2008 version for this post. The SSIS development environment is detached from SQL server and you can develop the package offline then can be deployed to the server.
BIDS can be found in the SQL server 2008 group as shown below
1. To start a new SSIS project, first you need to open BIDS and select File —-> New —–> Project
2. In the solution explorer you will notice an empty package called package.dtsx was created.
3. Drag the Execute Process Task to designer from tool box. Double click the task to configure it. Name the task Notepad and browse the file location for executing the task.
4. Drag another Executable Task to designer and double-click on it to open the editor. Name the task calc and browse the exe file for executing the task.
These tasks are now connected, and the calc task will not execute until the first task succeeds.
5. Save the project and run it you were able to see the notepad. After closing the notepad you will get the calc. After execution of this package the tasks should show as green in color, which means successfully executed.
If you look into the directory that contains your solution , you were able to see
- .dtsx – A SSIS package
- .ds – A shared data source file
- .dsv – A data source view
- dtproj – A SSIS project file
The toolbox will look like the same
Thank you, It is very helpful for beginners
Hey Kalyan, this post helped me to figure out how to create an SSIS package. Thanks. Hope everything is good at your end.
I am fine. Thanks Gopi.
This was helpful.. thanks..
Thanks it was helpful
Thank-you for this … I was able to make my way thru this …
On step #3, it was hard for me to find “Execute Process Task ” in the toolbox as I did not see your screen shot showing it in “Control Flow Items” …
On step #3, it took me a while to figure out what “browse the file location for executing the task” meant … I figured out that this demo package will simply run “Notepad” and then “Calc” … so then I had to find the executables for those programs … for me, they turned out to be in my C:\Windows\System32 folder.
On step #4, it says “These tasks are now connected” … but that was not free/automatic for me … I had to figure out how to connect them, which wasn’t hard, but …
After step #5, I “look into the directory that contains your solution” … and I do see “dtsx” and “dtproj” files, but not “ds” nor “dsv” files … have I missed something? Also, I have a “bin” folder with another “dtsx” file in it … is that OK?
Again, many thanks for this … it has helped me, a very new user of SSIS …
And now I have 2 questions …
1. How can I run this “package” outside of Visual Studio?
2. How do I create a package that does ETL? (Extract-Transform-Load data from 1 database to another)
Here’s where I’m going to learn how to create an SSIS package which does ETL from an Oracle table to a SQL/Server table …
http://www.microsoft.com/sqlserver/2008/en/us/ssis-oracle.aspx