Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

SSIS Variables

i have the following code that works fine if the path is hard coded, but when i just a variable i get an error.
        Dim objExcel As Object
        Dim objWorkbook As Object

        sDate = Right("00" & Year(Now()), 4) & Right("0" & Month(Now()), 2) & Right("00" & Day(Now()), 2)

        objExcel = CreateObject("Excel.Application")

        objExcel.Visible = False

--variable
        objWorkbook = objExcel.Workbooks.Open(Str(Dts.Variables("User::LTH_IP").Value) & sDate & ".xls", , , , " test")

--hard coded.
        'objWorkbook = objExcel.Workbooks.Open("C:\Development\Dashboard Feeds\adm" & sDate & ".xls", , , , "test")

        objExcel.DisplayAlerts = False

        objWorkbook.Password = ""
        objWorkbook.SaveAs("C:\Development\Dashboard Feeds\adm" & sDate & ".xls")


        objExcel.Quit()
0
aneilg
Asked:
aneilg
  • 3
  • 3
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
in the variable I think you need to make it double slashes.  EG:  "C:\\Development\\Dashboard Feeds\\<file>"

Also

does User::LTH_IP include the whole file or are you going to append the date the same way?
0
 
aneilgAuthor Commented:
the hard coded section works fine its when i change it to use a varialbe.

variable LTH_IP "datatype string" "value C:\Development\Dashboard Feeds\adm"
In the stm:- User::LTH_IP

so i am trying to add "& sDate" which will give me th efile name admyyymmdd.xls
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
that should be fine . ..  like I said ni the variable I think you need the double slashes.

try that and report back if it doesn't work.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
aneilgAuthor Commented:
no luck, ive changed the variable path to "C:\\Development\\Dashboard Feeds\\adm"

get error

Exception has been thrown by the target of an invocation. ---> System.InvalidCastException: Argument 'Number' cannot be converted to a numeric value.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
http://bi-polar23.blogspot.com/2007/08/loading-multiple-excel-files-with-ssis.html

I've always used an excel connection.  Can you use that instead?
0
 
aneilgAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now