DTS from excel file (excel filename is different everyday)

Good Day to all,
Hope you could help me w/ my project.
Im creating a DTS Package. The source data will be coming from an excel file going to my SQL table. The DTS package is scheduled to execute daily, but the source data will be coming from different excel filename.
Example, today the DTS will get data from Data092506.xls. Then tomorrow, the data will be coming from Data092606.xls.
How can I do this? The DTS I've already done has a fixed source data file.
Please help.
Thank you so much.
God Bless.You will need to create a variable in your DTS package for the file name, and then construct the filename dynamically.|||Hi blindman,
I can't seem to figure out how will I do that.
Could you be more specific, pls.
Thanks for taking the time to answer my queries.
God Bless.|||Look here:

http://www.sqldts.com/default.aspx?234|||use the following DTS steps for this

1) create a Global variable of name say "aa" of string type
2) add a ActiveX Task where u assign the value of global variable from system date. something like
DTSGlobalVariables("aa").Value = "d:\Data" & "0" & month(date()) & day(date()) & year(date()) & ".xls"
3) add a Dynamic Property task. select the Excel connection and assign the "data Source" to that global variable.
4) place a work flow so that the execution sequence is ActiveX>>Dynamic Prop>>Other Steps that u already have.|||Hi,
I can't seem to get it yet. I am presented w/ so many information from all the websites and help files that I am reading, and I end up more confused. :eek:
I'm a newbie in SQL and I need instructions for dummies. :D

Here's what I did:
1.) I created a global variable named gVarPath through the DTS Package Properties.
2.) I'm adding now a "ActiveX Script" Task in the DTS Designer. Here's my script:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
Main = DTSTaskExecResult_Success
DTSGlobalVariables("gVarPath").Value="D:\PROJECTS\Attendance-Excel\" & RIGHT('0'+ RTRIM(CAST(MONTH(GETDATE()-2) AS CHAR)),2) & RIGHT('0'+ RTRIM(CAST(DAY(GETDATE()-2) AS CHAR)),2) & RIGHT(YEAR(GETDATE()-2),2) & "_ALB.xls"
End Function

There's a syntax error. I will debug this later.

3.) I'm adding a "Dynamic Properties" task.
Question: Where can I select the excel connection? And how can I assign the data source to my global variable?
4.) And how can I place a workflow.

Please help :o|||Hi upalsen,

I got it already!
I followed your instructions. Many thanks to you. :)

Now, I have another question.:D
I need to import data from 24 excel files everyday. Excel filenames are like these:

100206_AAA
100206_BBB
100206_CCC
up to
100206_XXX

wherein 100206 is a date which I already knew how to alter for everyday DTS package execution. The last 3 characters are the branch code, in which we have 24 branches (ex. 100206_AAA, 100206_BBB,...100206_XXX).
How can I make a loop, so I can run the DTS package 24 times. Each run will get data from each excel files.

Here's how my ActiveX Script looks like:

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* *********************

Option Explicit

Function Main()
Dim vDay, vMonth, vYear, vDate

vDay=RIGHT(RTRIM("0" & DAY(DATE()-2)),2)
vMonth=RIGHT(RTRIM("0" & MONTH(DATE()-2)),2)
vYear=RIGHT(YEAR(DATE()-2),2)
vDate=vMonth & vDay & vYear

DTSGlobalVariables("gVarPath").Value=vDate & "_AAA.xls"

Main = DTSTaskExecResult_Success
End Function

Thank you so much... :)
God Bless.|||i am not sure if those branch codes r really fixed and hardcoded as AAA, BBB etc? or they will come from another table? assuming they are hard coded, u can ...
create another global variable, say vCounter. start with vCounter=1. add another ActiveX step. put it at the end of the existing workflow. add the following code

Function Main()
if vCounter <= 24 then
vCounter = vCounter+1
DTSGlobalVariables.Parent.Steps ("<NAME_OF_STEP1>").ExecutionStatus = DTSStepExecStat_Waiting
end if

Main = DTSTaskExecResult_Success
End Function

in your starting ActiveX script consider vCounter and write code to get branch code for each value

if vCounter = 1 then
BrCode = "AAA"
elseif vCounter .....

.......
DTSGlobalVariables("gVarPath").Value=vDate & "_" & BrCode & ".xls"|||Hi upalsen,

Yup. The branch codes are fixed and will be hardcoded.
Following your instructions, I created another global variable named "gVarCounter". How can I referenced "gVarCounter" in my Dynamic Properties Task? In my first global variable "gVarPath", I referenced it by assigning the data source of the excel connection to it.

And another question, how will I know the ("<NAME OF STEP1>")?
Here's my ActiveX script:
IF gVarCounter<=24 then
gVarCounter=gVarCounter+1
DTSGlobalVariables.Parent.Steps("DTSStep_DTSActiveScriptTask_1").ExecutionStatus=DTSStepExecStat_Waiting
END IF

I saw it in the Dynamic Property Task under Steps. Am I correct?

Thank you so much. :)|||u need not reference gVarCounter in your dynamic property task. all that u need to do is use gVarCounter in preparing the value of your previous gVarPath variable. like below. and dynamic prop will still use only gVarPath.

if gVarCounter = 1 then
BrCode = "AAA"
elseif gVarCounter=2 then
BrCode = "BBB"
.....

DTSGlobalVariables("gVarPath").Value=vDate & "_" & BrCode & ".xls"

yes, u r right. step names r listed in dynamic prop under "steps" heading.

DTS

Hi friends,
In my project I have redesigned my database structure. In the existing
structure there is no Primary key and no relationship b/w data.
In the new structure Primary key and the relationship is added.
Now I want to migrate the existing data into the new structure.
There is a chance for duplicate records and also records that does not
satisfy referential integrity.
How to migrate the data? I want to have a copy of the duplicate records and
also the records which does not satisfy referential integrity.
Its a huge database, so i can't query table by table to find the mismatch
records.
How to proceed?
thanks
vanitha
thanks a lot
vanithaHi
I think a multi-phase data pump may be what you are looking for check out
http://www.sqldts.com/default.aspx?282
John
"Vanitha" wrote:

> Hi friends,
> In my project I have redesigned my database structure. In the existing
> structure there is no Primary key and no relationship b/w data.
> In the new structure Primary key and the relationship is added.
> Now I want to migrate the existing data into the new structure.
> There is a chance for duplicate records and also records that does not
> satisfy referential integrity.
> How to migrate the data? I want to have a copy of the duplicate records an
d
> also the records which does not satisfy referential integrity.
> Its a huge database, so i can't query table by table to find the mismatch
> records.
> How to proceed?
> thanks
> vanitha
> thanks a lot
> vanitha|||Thanks John.
I am not getting the Phases Tab. How we will get "Visual Basic
Transformation Script"
Thanks
vanitha
"John Bell" wrote:
> Hi
> I think a multi-phase data pump may be what you are looking for check out
> http://www.sqldts.com/default.aspx?282
> John
> "Vanitha" wrote:
>|||Hi
If you have enabled viewing multiphase transaformations (see figure 1.1)
then to get you will need to delete any existing transformations created and
then create new activeX transformation. You can add phases on the phases tab
on the transformation options, or the phases tab on the Active X
transformation properties (properties button on the general tab of the
transformation options dialogue)
John
"Vanitha" wrote:
> Thanks John.
> I am not getting the Phases Tab. How we will get "Visual Basic
> Transformation Script"
> Thanks
> vanitha
> "John Bell" wrote:
>

DTC Error

I have a project with two SQL servers on two seperate
subnet, following distribute query will run on B server.
update [xxx.xxx.xxx.xxx].[abce].[dbo].[abcede] set
uploadstatus='InProgress'
If I include this statement in Transaction Like this way:
Begin Tran
update [xxx.xxx.xxx.xxx].[abce].[dbo].[abcede] set
uploadstatus='InProgress'
commit Tran
I got following error' Server: Msg 7391, Level 16, State
1, Line 2
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].'
Link server is setup okay for sure, any suggestions from
microsoft?
For DTC , two sql server have to be the same subnet or
same domain?
Thanks
David Liu
MCSE/MCDBA
Moneris.com
Is one of the servers Windows Server 2003 by any chance - see below
http://support.microsoft.com/default...b;en-us;816701
Andy Ball
Greenfell Computing Ltd
"David Liu" <david.z.liu@.moneris.com> wrote in message
news:2ef101c4289c$c6c0a660$a101280a@.phx.gbl...
> I have a project with two SQL servers on two seperate
> subnet, following distribute query will run on B server.
> update [xxx.xxx.xxx.xxx].[abce].[dbo].[abcede] set
> uploadstatus='InProgress'
> If I include this statement in Transaction Like this way:
> Begin Tran
> update [xxx.xxx.xxx.xxx].[abce].[dbo].[abcede] set
> uploadstatus='InProgress'
> commit Tran
> I got following error' Server: Msg 7391, Level 16, State
> 1, Line 2
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].'
> Link server is setup okay for sure, any suggestions from
> microsoft?
> For DTC , two sql server have to be the same subnet or
> same domain?
> Thanks
> David Liu
> MCSE/MCDBA
> Moneris.com
>
>
>
|||Both of them are Windows 2000 Advanced Server.
Does this case need to be same subnet and same domain?
DTC use RPC for communication for sure.
Really wired
Thanks for your information

>--Original Message--
>Is one of the servers Windows Server 2003 by any chance -
see below
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;816701[vbcol=seagreen]
>
>--
>Andy Ball
>Greenfell Computing Ltd
>"David Liu" <david.z.liu@.moneris.com> wrote in message
>news:2ef101c4289c$c6c0a660$a101280a@.phx.gbl...
way:[vbcol=seagreen]
cannot
>
>.
>

DTC Error

I have a project with two SQL servers on two seperate
subnet, following distribute query will run on B server.
update [xxx.xxx.xxx.xxx].[abce].[dbo].[abcede] set
uploadstatus='InProgress'
If I include this statement in Transaction Like this way:
Begin Tran
update [xxx.xxx.xxx.xxx].[abce].[dbo].[abcede] set
uploadstatus='InProgress'
commit Tran
I got following error' Server: Msg 7391, Level 16, State
1, Line 2
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].'
Link server is setup okay for sure, any suggestions from
microsoft?
For DTC , two sql server have to be the same subnet or
same domain?
Thanks
David Liu
MCSE/MCDBA
Moneris.comIs one of the servers Windows Server 2003 by any chance - see below
http://support.microsoft.com/defaul...kb;en-us;816701
Andy Ball
Greenfell Computing Ltd
"David Liu" <david.z.liu@.moneris.com> wrote in message
news:2ef101c4289c$c6c0a660$a101280a@.phx.gbl...
> I have a project with two SQL servers on two seperate
> subnet, following distribute query will run on B server.
> update [xxx.xxx.xxx.xxx].[abce].[dbo].[abcede] set
> uploadstatus='InProgress'
> If I include this statement in Transaction Like this way:
> Begin Tran
> update [xxx.xxx.xxx.xxx].[abce].[dbo].[abcede] set
> uploadstatus='InProgress'
> commit Tran
> I got following error' Server: Msg 7391, Level 16, State
> 1, Line 2
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].'
> Link server is setup okay for sure, any suggestions from
> microsoft?
> For DTC , two sql server have to be the same subnet or
> same domain?
> Thanks
> David Liu
> MCSE/MCDBA
> Moneris.com
>
>
>|||Both of them are Windows 2000 Advanced Server.
Does this case need to be same subnet and same domain?
DTC use RPC for communication for sure.
Really wired
Thanks for your information

>--Original Message--
>Is one of the servers Windows Server 2003 by any chance -
see below
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;816701
>
>--
>Andy Ball
>Greenfell Computing Ltd
>"David Liu" <david.z.liu@.moneris.com> wrote in message
>news:2ef101c4289c$c6c0a660$a101280a@.phx.gbl...
way:[vbcol=seagreen]
cannot[vbcol=seagreen]
>
>.
>

dt_ stored procedures

I am trying to use microsoft access project file to access msde databases on local workstations. I have to set the users security to db_owner due to the application requirements.
The problem users are having is since they are owners they see the dt_?? stored procedures in the queries list which are created when access addes a table to the database.
I need to keep these "Version Control" stored procedures from being created. I have tried triggers on the sysobject table but this is not allowed. The users can not delete the triggers due to sql security.
I did find out that in access if the view system objects is not check the dt_ stored procedures will not show up. But I still want to stop these from being created.
Any ideas on how to stop them from being created.
dt_ procedures are created by default (I thought only when diagrams are
created). I don't believe you can prevent them from being created, however
you could have the app use a custom query from INFORMATION_SCHEMA views that
leaves out any objects that start with dt_
http://www.aspfaq.com/
(Reverse address to reply.)
"Eric Brasher" <Eric Brasher@.discussions.microsoft.com> wrote in message
news:FAB9C710-45A0-47D2-9914-CB94D55138F4@.microsoft.com...
> I am trying to use microsoft access project file to access msde databases
on local workstations. I have to set the users security to db_owner due to
the application requirements.
> The problem users are having is since they are owners they see the
dt_?? stored procedures in the queries list which are created when access
addes a table to the database.
> I need to keep these "Version Control" stored procedures from being
created. I have tried triggers on the sysobject table but this is not
allowed. The users can not delete the triggers due to sql security.
> I did find out that in access if the view system objects is not check the
dt_ stored procedures will not show up. But I still want to stop these from
being created.
> Any ideas on how to stop them from being created.