[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Capture file name for DTS

Posted on 2005-04-12
6
Medium Priority
?
368 Views
Last Modified: 2013-11-30
Hai all, I have a dynamic file name which downloaded daily and need to use as the DTS execution source will the filename is simply as follow

item-1 for monday
item-2

and so on... i realize that the dynamic properties task can get this done but i am not very sure the function of it.. since i am still very new in DTS and SQL server... please help.. i need this urgently... thanx a lot... and step by step guide will be appreciated.

cheers
yee
0
Comment
Question by:mingfattt
  • 4
  • 2
6 Comments
 
LVL 13

Expert Comment

by:ispaleny
ID: 13761436
I prefer more steps with defined functions:

1. ActiveX->Global variable
      DTSGlobalVariables("FileSource").value = "c:\test.txt"
2. Dynamic properties bind Global variable FileSource to Transform Task settings (DataSource)

0
 
LVL 2

Author Comment

by:mingfattt
ID: 13768558
can you be more clearer please... i am just a slower catcher and it is good if you can make it step by step
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13768583
i got some problem in creating the global variable and the question is how to integrate between the connection property in the text file (source) with the activeX variable and also the dynamic property task??... please help many thanx

me, yee
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 13

Accepted Solution

by:
ispaleny earned 2000 total points
ID: 13770243
1. i got some problem in creating the global variable ...
What kind of problem. Global variables can be created many ways in DTS.
The simplest one:
Right click on DTS package background->Package Properties->Global Variables->Fill variable name->Select type(preselected String)->Click on OK->(Save)

2. and the question is how to integrate between the connection property in the text file (source) with the activeX variable and also the dynamic property task??...

Lets split this large question into 3 parts:

2A. how to integrate between the activeX variable and the global variable
Function Main()
      dim LocalX
      LocalX = "c:\test.txt"
      DTSGlobalVariables("FileSource").Value = LocalX
      Main = DTSTaskExecResult_Success
End Function

2B. how to integrate between the global variable and the connection property in the text file (source) ?
Add "Dynamic properties" task.->Double click on it->Select Txt connection, DataSource property->Click on Set->Select Source=Global variable, your variable name->OK->Close->(Save)

2C. how to integrate it together?
.1. Create transformation (TXT->SQL)
.2. Create "ActiveX" task
.3. Create "Dynamic properties" task
.4. Create Global variable
.5. Link "ActiveX" task as a successfull precendent of "Dynamic properties" task
.6. Link "Dynamic properties" task as a successfull precendent of transformation (TXT->SQL) task (TXT part)
So it looks like this:
"ActiveX" ===>"Dynamic properties"===>TXT----->SQL

Of course you can merge "ActiveX" ,"Dynamic properties" and Global variable into one big "ActiveX" task. But if you want to reference tasks from ActiveX, you must rename auto-generated names of tasks and a related property of step. Otherwise it can become an ureadable script. Also you will not see on the first sight, that such a package is auto-reconfigured at runtime.




0
 
LVL 2

Author Comment

by:mingfattt
ID: 13770305
Thanx for your help.. i found it....
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13778141
now i got another problem which the active x connection not showing in the dynamic properties connection any idea?
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

872 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