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