How to create (and execute) a package in SSIS using SQL Server Business Intelligence Development Studio
1. Using the SSIS Configuration manager, configure a data source to the database to which you are connecting (DB2, Oracle, Sybase)
2. Open SQL Server Business Intelligence Development Studio: Start ÃƒÂ Programs ÃƒÂ Microsoft SQL Server 2005 ÃƒÂ SQL Server Business Intelligence Development Studio.
3. From the top menu, click on File ÃƒÂ New a Project - and select Integration Services Project
4. Name the project, select your location, and click OK
5. From the top menu, click on Project ÃƒÂ New SSIS package
6. Click the Data Flow tab under the package
7. In the Data Flow window, click where it says "No DataFlow tasks have been added to this package. Click here to add a new Data Flow task."
8. From the top menu, click View a Toolbox (make sure the view keeps the Data Flow tab open)
9. Click and drag "OLE DB Source" to the Data Flow window from the toolbox Data Flow Sources
10. Click and drag "OLE DB Destination" to the Data Flow window from the toolbox from the toolbox Data Flow Destination
11. Click (once) on the "OLE DB Source"
12. Move the green arrow attached to the "OLE DB Source" by clicking on the arrow and dragging it to the "OLE DB Destination"
13. Right click on "OLE DB Source" - select "edit"
*** If you have not configured a data source, go to the DataDirect SSIS Configuration Manager and configure a data source to Sybase, Oracle, or DB2.***
14. To the right of "OLE DB connection manager", select "New"
15. At the bottom of the Connection Manager dialog box, click "New".
16. Select the provider - DataDirect SSIS OLE DB Provider for Sybase.
17. In the "Server or filename" field, type the name of the data source you created in the DataDirect SSIS Configuration manager.
18. Type the user name and password; select "Allow saving password".
19. Click "Test Connection" to ensure your connection will succeed.
20. Click OK.
21. For the "Data access mode", select "SQL command"
22. Select "Build Query" (at the right of the dialog box)
23. A new box will pop up and you will have a row of icons at the top - click the "Add Table" icon which has a plus sign on it and is on the far right
24. A list of tables from the database (Oracle, DB2, or Sybase) will appear - select the table(s) you wish to transfer to SQL Server. Click "Add". Click "close".
25. Click the columns you want transferred in the new table to be created on SQL Server. You can select "*(All Columns)" if you want the entire table transferred. Click OK to exit the dialog box.
26. Click "preview" - you should see about the same results as you would if you did a "select * from table" on the table you are trying to transfer to SQL Server. Click close.
27. In the left navigation bar, click "Columns". Ensure all columns you want to include are checked.
28. In the left navigation bar, click "Connection Manager"
29. Click OK
30. From the "Data Flow" window, right click "OLE DB Destination" and select "edit"
31. To the right of the "OLE DB connection manager" field, select "New"
32. A new dialog box will appear. If you see a data connection to SQL Server under "Data connections", you may select it and skip to step 33. Otherwise, click "New" at the bottom of the dialog box.
33. For "Server name", select the name of the server on which SQL Server resides (and you are currently working).
34. For the "Log on to the server" section, select "Use SQL Server Authentication" and type in your SQL Server UID and PWD in the appropriate fields.
35. Select the option "Save my password" by checking the box.
36. Click "Test connection" to ensure the connection works. Click OK.
37. Click OK
38. Now you will see your new connection listed in the "Data connections" list - select your new connection and click OK.
39. For Data access mode, select "Table or view - fast load"
40. For "Name of the table or the view", you will be creating a new one on the SQL Server, so click "New"
41. Modify the "Create table" statement where it says "[OLE DB Destination]" with the following (including brackets): [schema].[tablename]
42. You can choose the same table name as the database from which the table is being transferred - or you can choose a new table name. Just be sure to include the schema and the brackets indicated in step 36
or you will get a syntax error. Click OK.
43. Click OK.
44. Click "preview"
45. You will see the column names but no rows.
46. In the left navigation bar, click "Mappings" - you will see how the columns from the source table will be mapped to the destination table
47. Click OK
48. In the solution explorer (top right), you will see the new package you created - right click on the package and select "execute package". Just below the "connection managers" window, you will see a message. If the package was executed successfully, you will see "package execution completed".
49. Using ODBC Test, connect to the SQL Server instance where you moved the table and try to issue a select from the new table