DTS Pakcage File Import but Filename Changes

Hi All,

I am trying to automate a rather complicated file import routine using a DTS package. I'm getting on ok with it but have now reached the limits of my knowledge.

At the moment I am sent a file every month e.g. "perf04-05m1.csv". I then rename it to "import.csv" and run the DTS package. I would like to make the process more dynamic so that the the DTS package takes in the most recent file no matter it's filename e.g. I don't have to re-name the file to "import.csv" in order for the DTS package to run.

I have had a look at BOL and think that I need to use either a Dynamic Properties Task or a Global variable but I'm not sure how to tie it all together, can anyone offer any pointers or know of any good articles I can have a look at. I may also need to dynamically alter some of the SQL statements within the DTS package as well.

CheersWhy don't you do this in a sproc?

And then bcp the data...I also make sure I archive everything in the folder to a sub folder with the datetime of the move

Insert Into Ledger_Folder exec master..xp_cmdshell 'Dir d:\Data\Tax\SmartStreamExtracts\*.*'

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 5
Select @.Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END

-- select * from ledger_folder

Delete From Ledger_Folder_Parsed

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 6
Select @.Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END

Insert Into Ledger_Folder_Parsed (Create_Time, File_Size, File_Name )
Select Convert(datetime,Substring(dir_output,1,8)
+ ' '
+ (Substring(dir_output,11,5)
+ Case When Substring(dir_output,16,1) = 'a' Then ' AM' Else ' PM' End)) As Create_Time
, Convert(Int,LTrim(RTrim(Replace(Substring(dir_outp ut,17,22),',','')))) As File_Size
, Substring(dir_output,40,(Len(dir_output)-39)) As File_Name
From Ledger_Folder
Where Substring(dir_output,1,1) <> ' '
And (Substring(dir_output,1,1) <> ' '
And Substring(dir_output,25,5) <> '<DIR>')

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 7
Select @.Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END|||Thanks Brett that certainly points me in the right direction...I'll give it a go.

DTS Log Difference

Hi All,
I use the DTSRUN utility with /L option to provide the log filename with
path for logging.
I think in Package property "Error File" option is same as the above but
there the NAME will be given in design time.
Also there is an option to log to SQL Server - MSDB and also to eventlog.
Which method is the best for ERROR Log? Can any one explain with detail or
any website link that explain all that?
Thanks
PrabhatIt depends on who require the data. if the data is being used my
Network/System Administrators then you can log it into Event Log. If Databas
e
Administrators are going to use the data then u log it on MSDB.
If the data is required by the Application Suport Team the u can log it into
a flat file or one of the tables in the Production Database.
Where ever you log the data its one and the same.
hope this answers the question.
thanks and regards
Chandra
"Prabhat" wrote:

> Hi All,
> I use the DTSRUN utility with /L option to provide the log filename with
> path for logging.
> I think in Package property "Error File" option is same as the above but
> there the NAME will be given in design time.
> Also there is an option to log to SQL Server - MSDB and also to eventlog.
> Which method is the best for ERROR Log? Can any one explain with detail or
> any website link that explain all that?
> Thanks
> Prabhat
>
>
>|||Hi Chandra! Thanks for your reply.
Prabhat
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:8EF7A0BC-4391-40C0-A9FB-26A46DF9E14F@.microsoft.com...
> It depends on who require the data. if the data is being used my
> Network/System Administrators then you can log it into Event Log. If
Database
> Administrators are going to use the data then u log it on MSDB.
> If the data is required by the Application Suport Team the u can log it
into
> a flat file or one of the tables in the Production Database.
> Where ever you log the data its one and the same.
> hope this answers the question.
> thanks and regards
> Chandra
>
> "Prabhat" wrote:
>
eventlog.
or|||Good to know that the solution addressed your needs. Really appreciate if yo
u
can rate the Post.
This can be done by answering "Was this post helpful to you?"
"Prabhat" wrote:

> Hi Chandra! Thanks for your reply.
> Prabhat
>
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:8EF7A0BC-4391-40C0-A9FB-26A46DF9E14F@.microsoft.com...
> Database
> into
> eventlog.
> or
>
>|||Hi Chandra,
Yes This Post was answered my question and Helpful to me. Thanks.
Can U please look into my Post "DTS - With Conditions" and Help me in that?
Thanks
Prabhat
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:B1BF847C-25D8-44FE-B082-77D8687FBE0F@.microsoft.com...
> Good to know that the solution addressed your needs. Really appreciate if
you
> can rate the Post.
> This can be done by answering "Was this post helpful to you?"
> "Prabhat" wrote:
>
it
with
but
detail|||Hi Prabhat,
I will try to answer your post "DTS - With Conditions".
for more information on rating the post, please refer to
http://www.microsoft.com/wn3/locale...s.htm#RateAPost
thanks and regards
Chandra
"Prabhat" wrote:

> Hi Chandra,
> Yes This Post was answered my question and Helpful to me. Thanks.
> Can U please look into my Post "DTS - With Conditions" and Help me in that
?
> Thanks
> Prabhat
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:B1BF847C-25D8-44FE-B082-77D8687FBE0F@.microsoft.com...
> you
> it
> with
> but
> detail
>
>