Results 1 to 5 of 5
  1. #1
    Join Date
    May 2010
    Posts
    3

    Question Unanswered: DTS Dynamic properties task global variable settign

    I have a Main dts package,In that by using execute package task I will call another child DTS package(see diagram),I am passing paths through global variables,In child package I am getting error , The child DTS is for pumping data into table by reading text files,these text files are dynamic
    In child package dynamicpropertiestask---->Text(source)------>TransformDataTask----->ConnectionProperties
    Here In dynamicpropertiestask I have 2 global variables one is textfilepath second one is .udl path ,now I need to create another for new text file
    My question is how to set global variables in dynamic properties If I click on existing dynamic package it is showing 'Cannot locate the DestinationPropertyID'
    'Connections'
    'CONTROL_FILE'
    'OLEDBProperties'
    'DataSource'
    'Properties'
    'value'

    In google I found through vb code how to add global variable,
    but I need In dynamic properties task after clicking add how set these values.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If you right click on the background of the package in the editor (i.e. not on a particular object), you should be able to get the package properties, and there should be a tab for Global Variables there.

  3. #3
    Join Date
    May 2010
    Posts
    3
    Hi MCrowley,thx for u r quick reply,I already tried that aproach. In child package I have 54 branches(dynamicpropertiestask-->Text(source)-->TransformDataTask----->ConnectionProperties) because I need to read 54 txt files Now the requirement is I need to create another branch for new 55 txt file.so I copied existing dynamic properties task & I created another branch.but package is working only for text source saved file i.e.,venky0120100511.txt, but it need to work dynamic files(venky0120100512.txt) also.So I am thinking I copied existing dynamic properties task I din't create new d.p.t.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    We handle this in the .BAT that calls the DTS. In it we copy the dynamical "venky0120100511.txt" to a constant "venky01.txt", "venky0120100512.txt" to a constant "venky02.txt", ..., "venky0589892356.txt" to a constant "venky55.txt",...

    In the DTS we use fixed references to "venky01.txt", "venky02.txt", ..., "venky55.txt".

    55 files though is way more than I have ever seen in a DTS.


    <edit> corrected typo </edit>
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    May 2010
    Posts
    3

    Let me tell u clearly

    Hi Everyone

    I have little problem designing a dts package. Scenario is this ,I receive 54 text files in different timings daily (all files names,data everything is different that's y we created 54 branches)so that we have to import data from a

    directory every one hour where another application done in unix creates text files. So we have a package to be executed every one hour goes check in that

    directory if new text file came then import that file into sql server.The files are named like NAME01YYYY-MM-DD.txt.So in DTS package I have 54 braches

    (dynamicpropertiestask-->Text(source)-->TransformDataTask-->ConnectionProperties) to read & pump text files data into sqlserver DB. Now the requirement is I

    have to design one more branch for new text file (i.e., 55 text file).

    In dynamic property task how to set global variables for Connections,Text File (Source),OLEDBProperties,Data Source,Properties,Value?
    By seeing existing dynamic properties task I am unable to follow.
    I set up a dts package with dynamicpropertiestask,globalvariables,execute sql task, transform data task.
    DTS is running fine for the default saved text source file only. When I run dts and put in different text files unable to load.
    I know this might enough information. please help me it is really urgent.

Tags for this Thread

Posting Permissions

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