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 Package: Data Loading ?

Hi,
Can someone help me with the following
I have to automate the process of loading the files from source to target.
I have to use partitioned tables which should be created automatically to
load the data. These tables should have different name each week(for e.g
customers40, customers48 etc)
Using Northwind Example for Customers table
The syntax is
declare @.tablestmt nvarchar(2555)
set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)'
exec sp_executesql @.tablestmt
The problem is each week I get a file with different name. Using Nothwind
Example. Suppose I get a file CustomersA1. Next week I get the file with
the name CustomersA8. This file that I get each week has to go in a table
that is created at run time with different name(as described above). How do I
tell DTS that the file name changes every week. What tasks should I use.
All the task have specified path for the filenames. How do I tell the task
that filename changes every week.
Any example with syntax(for Northwind/Customers in this case) will be a
great help.
Thanks
Steve
Hi Steve,
well the only way I see is to use dynamic properties. Define a global
variable, assign the global to the specific data transformation task and use
a ActiveX task at the beginning of your packge to calculate the name of your
import table and store it in the global variable.
Regards,
Meinhard
"Steve" <Steve@.discussions.microsoft.com> schrieb im Newsbeitrag
news:20AE8726-8E58-4373-BF88-D06736C8FBAF@.microsoft.com...
> Hi,
> Can someone help me with the following
> I have to automate the process of loading the files from source to target.
> I have to use partitioned tables which should be created automatically to
> load the data. These tables should have different name each week(for e.g
> customers40, customers48 etc)
> Using Northwind Example for Customers table
> The syntax is
> declare @.tablestmt nvarchar(2555)
> set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
> getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )'
> exec sp_executesql @.tablestmt
>
> The problem is each week I get a file with different name. Using Nothwind
> Example. Suppose I get a file CustomersA1. Next week I get the file with
> the name CustomersA8. This file that I get each week has to go in a table
> that is created at run time with different name(as described above). How
> do I
> tell DTS that the file name changes every week. What tasks should I use.
> All the task have specified path for the filenames. How do I tell the
> task
> that filename changes every week.
> Any example with syntax(for Northwind/Customers in this case) will be a
> great help.
>
> Thanks
> Steve

DTS Package: Data Loading ?

Hi,
Can someone help me with the following
I have to automate the process of loading the files from source to target.
I have to use partitioned tables which should be created automatically to
load the data. These tables should have different name each week(for e.g
customers40, customers48 etc)
Using Northwind Example for Customers table
The syntax is
declare @.tablestmt nvarchar(2555)
set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)'
exec sp_executesql @.tablestmt
The problem is each week I get a file with different name. Using Nothwind
Example. Suppose I get a file CustomersA1. Next week I get the file with
the name CustomersA8. This file that I get each week has to go in a table
that is created at run time with different name(as described above). How do
I
tell DTS that the file name changes every week. What tasks should I use.
All the task have specified path for the filenames. How do I tell the task
that filename changes every week.
Any example with syntax(for Northwind/Customers in this case) will be a
great help.
Thanks
SteveHi Steve,
well the only way I see is to use dynamic properties. Define a global
variable, assign the global to the specific data transformation task and use
a ActiveX task at the beginning of your packge to calculate the name of your
import table and store it in the global variable.
Regards,
Meinhard
"Steve" <Steve@.discussions.microsoft.com> schrieb im Newsbeitrag
news:20AE8726-8E58-4373-BF88-D06736C8FBAF@.microsoft.com...
> Hi,
> Can someone help me with the following
> I have to automate the process of loading the files from source to target.
> I have to use partitioned tables which should be created automatically to
> load the data. These tables should have different name each week(for e.g
> customers40, customers48 etc)
> Using Northwind Example for Customers table
> The syntax is
> declare @.tablestmt nvarchar(2555)
> set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
> getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
> NULL ,
> [ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
> [Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
> [City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
> [Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )'
> exec sp_executesql @.tablestmt
>
> The problem is each week I get a file with different name. Using Nothwind
> Example. Suppose I get a file CustomersA1. Next week I get the file with
> the name CustomersA8. This file that I get each week has to go in a table
> that is created at run time with different name(as described above). How
> do I
> tell DTS that the file name changes every week. What tasks should I use.
> All the task have specified path for the filenames. How do I tell the
> task
> that filename changes every week.
> Any example with syntax(for Northwind/Customers in this case) will be a
> great help.
>
> Thanks
> Steve