• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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