Solved

SSIS Variables

Posted on 2012-03-15
6
371 Views
Last Modified: 2013-11-18
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
Comment
Question by:aneilg
  • 3
  • 3
6 Comments
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 320 total points
Comment Utility
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
 

Author Comment

by:aneilg
Comment Utility
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
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:aneilg
Comment Utility
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
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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
 

Author Closing Comment

by:aneilg
Comment Utility
thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now