Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2005
    Posts
    119

    Unanswered: DTS from excel file (excel filename is different everyday)

    Good Day to all,
    Hope you could help me w/ my project.
    Im creating a DTS Package. The source data will be coming from an excel file going to my SQL table. The DTS package is scheduled to execute daily, but the source data will be coming from different excel filename.
    Example, today the DTS will get data from Data092506.xls. Then tomorrow, the data will be coming from Data092606.xls.
    How can I do this? The DTS I've already done has a fixed source data file.
    Please help.
    Thank you so much.
    God Bless.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You will need to create a variable in your DTS package for the file name, and then construct the filename dynamically.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Oct 2005
    Posts
    119
    Hi blindman,
    I can't seem to figure out how will I do that.
    Could you be more specific, pls.
    Thanks for taking the time to answer my queries.
    God Bless.

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    use the following DTS steps for this

    1) create a Global variable of name say "aa" of string type
    2) add a ActiveX Task where u assign the value of global variable from system date. something like
    DTSGlobalVariables("aa").Value = "d:\Data" & "0" & month(date()) & day(date()) & year(date()) & ".xls"
    3) add a Dynamic Property task. select the Excel connection and assign the "data Source" to that global variable.
    4) place a work flow so that the execution sequence is ActiveX>>Dynamic Prop>>Other Steps that u already have.

  6. #6
    Join Date
    Oct 2005
    Posts
    119
    Hi,
    I can't seem to get it yet. I am presented w/ so many information from all the websites and help files that I am reading, and I end up more confused.
    I'm a newbie in SQL and I need instructions for dummies.

    Here's what I did:
    1.) I created a global variable named gVarPath through the DTS Package Properties.
    2.) I'm adding now a "ActiveX Script" Task in the DTS Designer. Here's my script:
    '************************************************* *********************
    ' Visual Basic ActiveX Script
    '************************************************* ***********************

    Function Main()
    Main = DTSTaskExecResult_Success
    DTSGlobalVariables("gVarPath").Value="D:\PROJECTS\ Attendance-Excel\" & RIGHT('0'+ RTRIM(CAST(MONTH(GETDATE()-2) AS CHAR)),2) & RIGHT('0'+ RTRIM(CAST(DAY(GETDATE()-2) AS CHAR)),2) & RIGHT(YEAR(GETDATE()-2),2) & "_ALB.xls"
    End Function
    There's a syntax error. I will debug this later.

    3.) I'm adding a "Dynamic Properties" task.
    Question: Where can I select the excel connection? And how can I assign the data source to my global variable?
    4.) And how can I place a workflow.

    Please help

  7. #7
    Join Date
    Oct 2005
    Posts
    119
    Hi upalsen,

    I got it already!
    I followed your instructions. Many thanks to you.

    Now, I have another question.
    I need to import data from 24 excel files everyday. Excel filenames are like these:

    100206_AAA
    100206_BBB
    100206_CCC
    up to
    100206_XXX

    wherein 100206 is a date which I already knew how to alter for everyday DTS package execution. The last 3 characters are the branch code, in which we have 24 branches (ex. 100206_AAA, 100206_BBB,...100206_XXX).
    How can I make a loop, so I can run the DTS package 24 times. Each run will get data from each excel files.

    Here's how my ActiveX Script looks like:

    '************************************************* *********************
    ' Visual Basic ActiveX Script
    '************************************************* *********************

    Option Explicit

    Function Main()
    Dim vDay, vMonth, vYear, vDate

    vDay=RIGHT(RTRIM("0" & DAY(DATE()-2)),2)
    vMonth=RIGHT(RTRIM("0" & MONTH(DATE()-2)),2)
    vYear=RIGHT(YEAR(DATE()-2),2)
    vDate=vMonth & vDay & vYear

    DTSGlobalVariables("gVarPath").Value=vDate & "_AAA.xls"

    Main = DTSTaskExecResult_Success
    End Function
    Thank you so much...
    God Bless.

  8. #8
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    i am not sure if those branch codes r really fixed and hardcoded as AAA, BBB etc? or they will come from another table? assuming they are hard coded, u can ....
    create another global variable, say vCounter. start with vCounter=1. add another ActiveX step. put it at the end of the existing workflow. add the following code

    Code:
    Function Main()
    	if vCounter <= 24 then 
                    vCounter = vCounter+1
    		DTSGlobalVariables.Parent.Steps ("<NAME_OF_STEP1>").ExecutionStatus = DTSStepExecStat_Waiting
    	end if
    
    	Main = DTSTaskExecResult_Success
    End Function
    in your starting ActiveX script consider vCounter and write code to get branch code for each value

    Code:
    if vCounter = 1 then
           BrCode = "AAA"
    elseif vCounter ......
    
    .........
    DTSGlobalVariables("gVarPath").Value=vDate & "_" & BrCode & ".xls"

  9. #9
    Join Date
    Oct 2005
    Posts
    119
    Hi upalsen,

    Yup. The branch codes are fixed and will be hardcoded.
    Following your instructions, I created another global variable named "gVarCounter". How can I referenced "gVarCounter" in my Dynamic Properties Task? In my first global variable "gVarPath", I referenced it by assigning the data source of the excel connection to it.

    And another question, how will I know the ("<NAME OF STEP1>")?
    Here's my ActiveX script:
    IF gVarCounter<=24 then
    gVarCounter=gVarCounter+1
    DTSGlobalVariables.Parent.Steps("DTSStep_DTSActive ScriptTask_1").ExecutionStatus=DTSStepExecStat_Wai ting
    END IF
    I saw it in the Dynamic Property Task under Steps. Am I correct?

    Thank you so much.

  10. #10
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    u need not reference gVarCounter in your dynamic property task. all that u need to do is use gVarCounter in preparing the value of your previous gVarPath variable. like below. and dynamic prop will still use only gVarPath.
    Code:
    if gVarCounter = 1 then
           BrCode = "AAA"
    elseif gVarCounter=2 then
           BrCode = "BBB"
    ......
    
    DTSGlobalVariables("gVarPath").Value=vDate & "_" & BrCode & ".xls"
    yes, u r right. step names r listed in dynamic prop under "steps" heading.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •