Looping thru time..

I have a DTS PACKAGE IN SQL 2000 where I need to use vbscript to loop thru the files. I have the package detecting the directory - and the file, BUT the file itself has an interval number from 1- 48 set within it. So in one day I am getting 48 different files.

I can not delete previous files or move them - so I need to - each day - loop thru the day for each interval.

Any thoughts on how I can do this?

Interval 1 = 12:00 AM

Interval 2 = 12:30 AM

Interval 3 = 1:00 AM

etc. etc.

Thanks!

M

I am not clear exactly what you're after. If you want to translate intervals into respective times for the 48 intervals, you can do like so:

Code Snippet

--create dummy #digits table
select top 48 i=identity(int,1,1)
into #digits
from syscolumns

--the query
select i, right(convert(varchar,dateadd(minute,(i-1)*30,0),100),7) [time]
from #digits

--drop #digits
drop table #digits

|||

Well not exactly taking the intervals and transposing them into a time. Each interval means it is a certain time frame (a 30 min time frame within the 24 hours - giving me a total of 48 intervals in a day).

The file that I get - I get 48 times a day. So I have to loop thru the 48 files in activex for the dts. And I am a bit stuck on that part.

|||

Moved to the SSIS forum, since you are looking for a solution with no T-SQL code. This is more likely to have someone to answer it here. Thanks

|||Are you trying to do this in DTS or SSIS? If it's DTS, try this newsgroup - microsoft.public.sqlserver.dts.|||Forums site is screwy - finally was able to get in and edit/reply... this is for DTS in SQL 2000...

Looping thru time..

I have a DTS PACKAGE IN SQL 2000 where I need to use vbscript to loop thru the files. I have the package detecting the directory - and the file, BUT the file itself has an interval number from 1- 48 set within it. So in one day I am getting 48 different files.

I can not delete previous files or move them - so I need to - each day - loop thru the day for each interval.

Any thoughts on how I can do this?

Interval 1 = 12:00 AM

Interval 2 = 12:30 AM

Interval 3 = 1:00 AM

etc. etc.

Thanks!

M

I am not clear exactly what you're after. If you want to translate intervals into respective times for the 48 intervals, you can do like so:

Code Snippet

--create dummy #digits table
select top 48 i=identity(int,1,1)
into #digits
from syscolumns

--the query
select i, right(convert(varchar,dateadd(minute,(i-1)*30,0),100),7) [time]
from #digits

--drop #digits
drop table #digits

|||

Well not exactly taking the intervals and transposing them into a time. Each interval means it is a certain time frame (a 30 min time frame within the 24 hours - giving me a total of 48 intervals in a day).

The file that I get - I get 48 times a day. So I have to loop thru the 48 files in activex for the dts. And I am a bit stuck on that part.

|||

Moved to the SSIS forum, since you are looking for a solution with no T-SQL code. This is more likely to have someone to answer it here. Thanks

|||Are you trying to do this in DTS or SSIS? If it's DTS, try this newsgroup - microsoft.public.sqlserver.dts.|||Forums site is screwy - finally was able to get in and edit/reply... this is for DTS in SQL 2000...

Lookup not detecting new column on table

I have added a new column to a table, but it is not showing up in the column mapping tab of the lookup editor.

It does show up in the preview.

Without deleting and recreating the component (with all the ensuing broken metadata fixes 'downstream' that this always entails), how can I get it to recognise the change?

In future I will do all LUs as a sql statement so at least I can control columns. This again defeats the purpose of the drag and drop environment..... If it were a harry potter character, it would be a dementor

Unfortunately, Lookup does not automatically updates the reference metadata, but you can do this using Advanced UI. Right click the component, select 'Show Advanced Editor'. Click Refresh button in the lower-left corner, click OK.

Now open regular Lookup Editor, you should see the new columns.

|||My workaround was to script out the table as a select and change the type to sql stmt.

The column names were all the same so this didn't seem to break it.|||

Adolf,

More than a workaround; that is the best way of using a lookup transformation. There are several negative effects performance wise when you select the table from the list as opposed to provide a sql statement.

Lookup transformation by default uses RAM and you can reduce that impact by trimming the number of columns and rows that it uses (via query).

Lookup not detecting new column on table

I have added a new column to a table, but it is not showing up in the column mapping tab of the lookup editor.

It does show up in the preview.

Without deleting and recreating the component (with all the ensuing broken metadata fixes 'downstream' that this always entails), how can I get it to recognise the change?

In future I will do all LUs as a sql statement so at least I can control columns. This again defeats the purpose of the drag and drop environment..... If it were a harry potter character, it would be a dementor

Unfortunately, Lookup does not automatically updates the reference metadata, but you can do this using Advanced UI. Right click the component, select 'Show Advanced Editor'. Click Refresh button in the lower-left corner, click OK.

Now open regular Lookup Editor, you should see the new columns.

|||My workaround was to script out the table as a select and change the type to sql stmt.

The column names were all the same so this didn't seem to break it.|||

Adolf,

More than a workaround; that is the best way of using a lookup transformation. There are several negative effects performance wise when you select the table from the list as opposed to provide a sql statement.

Lookup transformation by default uses RAM and you can reduce that impact by trimming the number of columns and rows that it uses (via query).