How tro read Excel Through Variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

How tro read Excel Through Variable

Author

Topic

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-06-27 : 03:58:34

Hi Team,
I have created on SSIS package which export data from excel to SQL server 2005.It's working fine.Now I am trying to make it dynamic using variable. It means I wil just change the path or name of the excel file
How to access excel file using variables ?
Need help any link,suggestion .....

Yogesh V. Desai. | SQLDBA|

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-27 : 04:47:56

You can get the file from a user variable.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-06-27 : 08:47:45

I have defined variable already...But How can I access excel file using variable

Yogesh V. Desai. | SQLDBA|

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-27 : 09:32:49

I don't have a v2005 machine available at the moment but isn't there a filename from variable option? If not I'm sure you can set it from an expression.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-06-28 : 07:30:36

Actually I have defined a varible as XL file and provided a path to create connection.
In connection Manager window there is option to choose data access mode Under this option
1 Table or View
2 Table Name or View Name varaible
3 Sql Command
4 Sql Command from Varaible

But we can access excel file only using Table or View Name, If you chose Table Name or View Name varible It will give you error Becuase excel file uses worksheets.The Main Problem Is How to provide the path of Worksheet. For example We provide the d:\test.xls.....but you cannot specify sheet 1 or sheet 2.
Do you any suggestions for this

Yogesh V. Desai. | SQLDBA|

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-28 : 10:40:37

Usually the worksheet is exposed as a table - you could try that.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-06-29 : 02:21:08

no but using variable is it possible. e.g. I have two excel file on my disk let's assume d:\test.xls and d:\test1.xls having same structure.
I just wanna change it at runtime.For this I have created one web page and I am trying to send Excel Sorce from web page.
any help............

Yogesh V. Desai. | SQLDBA|

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources