Solved

SSIS Variables

Posted on 2012-03-15
6
376 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Custom auto number 6 59
Scraping specific data within an XML document 19 54
Import data into excel from web page 10 83
Randomize in Owl Carousel v1.3.2 6 20
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
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).

895 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

14 Experts available now in Live!

Get 1:1 Help Now