Dynamically Change Source File Name

Question

Dynamically Change Source File Name

AVB

SSChampion

Points: 11418
More actions
February 11, 2010 at 7:47 am

#73483

I want to execute a DTS package from a Stored Procedure that will pass a file path to the package. The DTS Package will then use that file path as the path of the source file it is going to import.
I've been digging thru the mounds of DTS articles and forum posts. So far I think I'll have to use DTSRun from within an SP with the /A switch to specify the global variable.
Create Proc sDTS
@FilePath varchar(500)
as
DTSRUN /F c:\TestPackage.dts /N ImportPackage.... /A gvFileName:8=@FilePath
I've setup a Global Variable in the package and then created a Dynamic Properties task that assisgns the DataSource of Connection1 (The Text File) to the global variable gvFileName. I'm not sure if this will work... any thoughts?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

AVB SSChampion Points: 11418 More actions · Answer 1

I am able to execute the DTS Package but it won't work if I supply a value to the GlobalVariable in the DTSRun command. However If I hardcode that same value in the package itself and don't specify it in the execute statement it works fine bu.

This yields an error:

exec master.dbo.xp_Cmdshell 'dtsrun /F "\\PC1\c$\documents and settings\User1\desktop\TestImport.dts"

/N TestImport /A "gvFileName":8="\\PC1\c$\Data.txt" '

Error string: Error opening datafile: The filename, directory name, or volume label syntax is incorrect.

Same GV value as above "\\PC1\c$\Data.txt" set to value of Global Variable in DTS Package

exec master.dbo.xp_Cmdshell 'dtsrun /F "\\PC1\c$\documents and settings\User1\desktop\TestImport.dts" /N TestImport '

The DTS Package is setup like so:

Dynamic Properties --- On Success -> Text File (source) --- Transform Data Task --> Database Connection

Any ideas?

tomailvenky Valued Member Points: 66 More actions · Answer 2

Hi I also strucked on same problem,

I have little problem designing a dts package. Scenario is this ,I am receiving 54 text files in different timings daily. So we have a package to be executed every one hour .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., 55th text file).

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.

My question is, DTS is running fine for default text file(source), So I am thinking there is nothing wrong in coding part.but In the case of dynamicpropertiestask

2 global variables setted in existing branches.

1)G_RegLoadUdl_Path ,If we click on existing branch dynamicpropertiestask It is clearly pointing to .ini file,so we can easily set this one.

2)G_CTL_FILEPATH ,If we click on existing branch dynamicpropertiestask It is showing msgbox( Connections,Text File (Source),OLEDBProperties,Data Source,Properties,Value) ,so we don't know how to set these settings

So we copied and paste existing branch dynamicproperties task in our new branch but It is working fine for one text file only.

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.

Please suggest .

AVB SSChampion Points: 11418 More actions · Answer 3

tomailvenky,

I had a few questions...

1. Why do you have 54 branches? Is that to handle the 54 different file names?

2. Do all 54 files get imported, transformed and handled the same way in that DTS Package or are some transformed differently?

3. Are all 54 files located in the same Folder when imported or are they scattered throughout different folders/systems?

If you package is how I think it is then you only need one dynamic properties task. I would write a proc that would dynamically read the file names and then pass those names to the DTSRun command. This is how I handle multiple file imports. It also helps because the file names can be anything.

When I execute my package it is done within a Stored Proc. I dynamically assign the Source File path to the variable @Filepath within the DTS Run command:

--Execute Package

SET @CMD =

'DTSRun /S "ServerName" /N "ImportPackageName" /G "{233370-E341-304-83344-554fsdr37B3}" /A "gvFilePath":"8"="' + @FilePath + '" /W "0" /E '

exec master.dbo.xp_Cmdshell @CMD