May 18, 2005 at 6:07 am
May 18, 2005 at 7:03 am
I am guessing that you are importing identically structured files one after the other. If that is the case, rather than have 20 connections etc, see this link. Easy to follow and works well:
May 19, 2005 at 7:02 am
I don't have a firm solution for you for the 20 different files. However, I would probably look at creating a loop within an activeX script to execute a data import step. The data import step would use a text file connection which could be modified as shown below. This is just a thought, not sure if it'll help, but here goes.
First you must declare your global variables in your package under package properties.
Then in one Execute SQL Task you can get your filename from table as shown below:
Note that @OutputPath is a global variable in our example package.
*******************************
Declare @OutputPath varchar(500)
Set NoCount On
Set @OutputPath = (select [value_varchar] from
where process_control_def_id = '7024')
Select @OutputPath as OutputPath
*******************************
Afterwards your could take that global variable and use it to modify the connection properties datasource.
When connecting to a text file, you'll have your connection object which can be modified
by an activeX script as shown below:
'**********************************************************************
' Visual Basic ActiveX Script
Function Main()
Dim inputPath
Dim oPKG
Dim cn
inputPath = DTSGlobalVariables("InputFile").Value
Set oPKG = DTSGlobalVariables.Parent
Set cn = oPKG.Connections("Filexxx")
cn.DataSource = inputPath
Main = DTSTaskExecResult_Success
End Function
'************************************************************************
Hope it helps.
Paul Krasucki
Paul Krasucki, MCAD
May 19, 2005 at 7:05 am
May 19, 2005 at 2:41 pm
I think what you need is a Dynamic Property Task. When this task is executed it will assign values to your global variable at run time.
What you need to do is configure the task to assign values coming from Query on a predefied connexion.
Dynamic Property Task is standard with SQL SERVER 2000 DTS. you will find it on the toolbar is the DTS designer.
Habib.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply