| Author |
Topic |
|
nicky_river
Yak Posting Veteran
55 Posts |
 Posted - 2010-08-23 : 11:09:53
|
| Hi everyone,I need to pass an entire file path in SSIS. This file path contains an file with values that need to get inserted into the table in SQL Server.For eg :- "C:\documents and settings\my documents\importdata.xls"in the above example, importdata.xls contains values that need to get inserted to a table in SQL, the table in SQL also has the same number of columns as in the excelsheet.How can i do this in SSIS ? Please provide me with the steps in SSIS on how to achieve this as I m new to SSIS.Thanks !!!! |
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2010-08-23 : 11:28:20
|
| you can do this in SSMS.just right click on the database, select import Data and follow the wizard. select source data (your excel file) and destination (your sql table) and that is it.at the end of the wizard, you can save the whole project as SSIS package and this is the fastest and simplest way to do it. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-23 : 13:21:57
|
| and just in case the problem is path changes dynamically you can declare a variable in ssis and pass path through it by means of configuration. then in expression builder map the value of variable against datasource of excel connection manager------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nicky_river
Yak Posting Veteran
55 Posts |
Posted - 2010-08-23 : 13:50:38
|
| Hi visakh16,Thanks for your reply. Thats exactly what i need to do. I need to pass the filepath dynamically. because any filepath will be passed through SSIS and the records must be inserted into the table in SQL.Could you please provide me with the proper steps on how to declare a variable in SSIS and pass path by means of configuration and also what i need to do in expression builder ? as I m new to SSIS |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-23 : 13:56:38
|
| you can right click on package and in menu select variables to open variables window. In that click first button on top to create a new one. give it name and make type string as you're passing a path name. then in package right click and select configuration. config window opens. select add new option and select type (can be sql server or xml). then follow the wizard and when asked select variables then new variable added and map the value property. on finishing it saves the xml in path that you selected in wizard ( provided you selected xml). Then go inside xml and see the value you saved. you can change it to one you want and automatically package repoints to new file.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nicky_river
Yak Posting Veteran
55 Posts |
Posted - 2010-08-23 : 14:25:32
|
| Hi visakh16,thanks for ur reply. I wanted to clarify few things :-- is this "type" u mentioned, the destination path where the records from the file will go to the table in SQL ?- "then follow the wizard and when asked select variables then new variable added and map the value property" -In the above line that u said, do i need to select the variable I created in the beginning of the package ? - what does "map the value property" mean ? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-23 : 14:32:03
|
| -nope by type i meant type of configuration like XML,SQL Server etc which determines from where it need to pick up values to be passed for various properties. The simplest one is XML-yup you need to expand variables-> point to your variables expand and select value (click on checkbox near that) inside the wizard.- map property means open your file source in data flow task go to expressions tab expand it and click on ... to popup mapping window select from combo data source property on left and select your variable on right------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nicky_river
Yak Posting Veteran
55 Posts |
Posted - 2010-08-23 : 14:43:43
|
| Hi visakh16,Thanks for ur reply. If i need to pick up values from an excelsheet and put it in a table in SQL then, will this method u have given me be the same ? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-23 : 14:49:55
|
| you mean path of excel sheet is in some other excel sheet?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nicky_river
Yak Posting Veteran
55 Posts |
Posted - 2010-08-23 : 14:52:02
|
| Hi,No the path of the excelsheet in not in another excelsheet. it will be in any folder in the PC |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-23 : 14:56:30
|
| ok. then my point was you can pass path by passing through xml file or storing in sql table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nicky_river
Yak Posting Veteran
55 Posts |
Posted - 2010-08-23 : 15:04:07
|
| Hi,Can I use a variable of a stored procedure in SSIS ? for eg, when i execute this procedure i pass the filepath as a parameter to this procedure. please see below :-exec dbo.import_data 'C:\documents and settings\my documents\importdata.xls'can this parameter of a stored procedure be used in SSIS ? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-23 : 15:07:35
|
| you can. but for that you need to call ssis programatically from SP using dtexec utility with the help of xp_cmdshell extended proc.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nicky_river
Yak Posting Veteran
55 Posts |
Posted - 2010-08-23 : 15:10:05
|
| Hi,Can I use the same steps that u have given me above for this process also ? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-23 : 15:14:37
|
| yup. whatever i have suggested before is that happening inside ssis. what i've told in previous post is how to call ssis from procedure passing the parameter value.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nicky_river
Yak Posting Veteran
55 Posts |
Posted - 2010-08-23 : 15:15:50
|
| Thanks visakh16!!! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-23 : 15:17:48
|
welcome  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Passing a filepath in SSIS