Solved

SSIS Variables

Posted on 2012-03-15
6
380 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 40

Accepted Solution

by:
Kyle Abrahams earned 320 total points
ID: 37725031
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
ID: 37725089
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 40

Expert Comment

by:Kyle Abrahams
ID: 37725198
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:aneilg
ID: 37725317
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 40

Expert Comment

by:Kyle Abrahams
ID: 37725438
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
ID: 37786875
thanks
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article covers the basics of the Sass, which is a CSS extension language. You will learn about variables, mixins, and nesting.
Introduction Knockoutjs (Knockout) is a JavaScript framework (Model View ViewModel or MVVM framework).   The main ideology behind Knockout is to control from JavaScript how a page looks whilst creating an engaging user experience in the least …
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

813 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

8 Experts available now in Live!

Get 1:1 Help Now