DTS Looping
Hi,
I have read articles on DTS looping http://www.sqldts.com/default.aspx?6,103,246,0,1 and a few others, but I still have a problem.
Trying to connect to different Interbase databases via Interbase ODBC driver.
-After connection, use a datapump to select data from db A
-Then loop, based on a global variable that holds the count/ID of the new database B to connect to.
Problem
-GLobal variable successfully getting updated with new branch details
-ODBC driver 'seems' to be switching to new branch db B
-But datapump goes to database A and tries to select the same data again it has done already.
-Loop fails. But if all steps run manually one by one the loop works i.e goes to A then goes to B.
HeeeEEEllLLLppppp !See Attached file for further details|||Hello,
Hoping somebody would have an idea on this one...
Please Help.|||Howdy
Not being a DTS coding expert, I'd suggest adding an extra step in the branch choosing VB script.
If its a timing issue, this will allow the correct value to appear where its supposed to be , ready for the next sweep of the reading program.
Cheers,
SG|||Thanks SG.
Never thought that it could be a timing issue. Will into this.
But found another article on dynamically changing connections in a DTS...
http://www.databasejournal.com/features/mssql/article.php/1461481
Maybe I have luck here.
Question:
Can one call a DTS Package from another DTS Package ?
Thanks again.
Originally posted by sqlguy7777
Howdy
Not being a DTS coding expert, I'd suggest adding an extra step in the branch choosing VB script.
If its a timing issue, this will allow the correct value to appear where its supposed to be , ready for the next sweep of the reading program.
Cheers,
SG|||Still Trying........SO GO ON Sql Experts.take a chance with this one !
Originally posted by bhandp
Thanks SG.
Never thought that it could be a timing issue. Will into this.
But found another article on dynamically changing connections in a DTS...
http://www.databasejournal.com/features/mssql/article.php/1461481
Maybe I have luck here.
Question:
Can one call a DTS Package from another DTS Package ?
Thanks again.|||Originally posted by bhandp
Still Trying........SO GO ON Sql Experts.take a chance with this one !
Are you resetting any database properties within the datapump task? This one quite often trips people up as DTS uses the fully qualified name of the table eg: database.owner.table|||Originally posted by phillcart
Are you resetting any database properties within the datapump task? This one quite often trips people up as DTS uses the fully qualified name of the table eg: database.owner.table
Hi Phill,
How can I give a fully qualified name of a table 'database.owner.table' within the datapump task when my database will change with every time it loops ?
Is there a possibility that, the first database the datapump connects to, remains stored in the datapump ? Here is the script the datapump uses, in its properties:
Function Main()
Dim oPkg, oDataPump, sSQLStatement
Dim FromDt
Dim ToDtLocal
'Assign global variable to local variable
FromDt = DTSGlobalVariables("FromDate").Value
ToDtLocal = DTSGlobalVariables("ToDtLocal").Value
' Build new SQL Statement
sSQLStatement = "SELECT all the fields here"
Where d.updDT >= '"& FromDt &"' " & _
"AND d.updDT < '"& ToDtLocal & "' "
' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
' Assign SQL Statement to Source of DataPump
oDataPump.SourceSQLStatement = sSQLStatement
' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing
Main = DTSStepScriptResult_ExecuteTask
End Function
Let me know what you think.
Thanks in advance. Thanks so muchly !
-Parul
Showing posts with label looping. Show all posts
Showing posts with label looping. Show all posts
Sunday, February 26, 2012
DTS Looping
DTS Looping
Hi,
I have read articles on DTS looping http://www.sqldts.com/default.aspx?6,103,246,0,1 and a few others, but I still have a problem.
Trying to connect to different Interbase databases via Interbase ODBC driver.
-After connection, use a datapump to select data from db A
-Then loop, based on a global variable that holds the count/ID of the new database B to connect to.
Problem
-GLobal variable successfully getting updated with new branch details
-ODBC driver 'seems' to be switching to new branch db B
-But datapump goes to database A and tries to select the same data again it has done already.
-Loop fails. But if all steps run manually one by one the loop works i.e goes to A then goes to B.
HeeeEEEllLLLppppp !Hi,
Looks like I have had no luck in this, and nor has anybody else.
Calling all SQL server experts to take a look :)
Hi,
I have read articles on DTS looping http://www.sqldts.com/default.aspx?6,103,246,0,1 and a few others, but I still have a problem.
Trying to connect to different Interbase databases via Interbase ODBC driver.
-After connection, use a datapump to select data from db A
-Then loop, based on a global variable that holds the count/ID of the new database B to connect to.
Problem
-GLobal variable successfully getting updated with new branch details
-ODBC driver 'seems' to be switching to new branch db B
-But datapump goes to database A and tries to select the same data again it has done already.
-Loop fails. But if all steps run manually one by one the loop works i.e goes to A then goes to B.
HeeeEEEllLLLppppp !Hi,
Looks like I have had no luck in this, and nor has anybody else.
Calling all SQL server experts to take a look :)
DTS Looping
Hi to everyone!
It's my first mission in DTS.
I fill global variables with e-mail addresses (maybe with empty string)
of my accounts.
If I have an e-mail(not empty), I need to create an excel file with
data to this account and send messages to him.
I can do it to some alone account, but I have a problem with looping
I saw an example from http://www.sqldts.com/Default.aspx?246
it helped me very much, but when I run it in the loop it dos not
working!
I think a problem in :
stpEnterLoop.DisableStep = False
stpFinished.DisableStep = True
stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
or I do something wrong.
I have 5 ActiveX task. First - fill global variables.
Second starting a loop. Maybe the problem is that i call a second
ActiveX task from itselfe?
Help me please.
Thanks a lot!It's hard to say something without seeing the code (with dts always is the
same) but first of all I'd put a log for that DTS and I'll try to see what
happen.
--
Current location: Alicante (ES)
"Dima" wrote:
> Hi to everyone!
> It's my first mission in DTS.
> I fill global variables with e-mail addresses (maybe with empty string)
> of my accounts.
> If I have an e-mail(not empty), I need to create an excel file with
> data to this account and send messages to him.
> I can do it to some alone account, but I have a problem with looping
> I saw an example from http://www.sqldts.com/Default.aspx?246
> it helped me very much, but when I run it in the loop it dos not
> working!
> I think a problem in :
> stpEnterLoop.DisableStep = False
> stpFinished.DisableStep = True
> stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
> or I do something wrong.
> I have 5 ActiveX task. First - fill global variables.
> Second starting a loop. Maybe the problem is that i call a second
> ActiveX task from itselfe?
> Help me please.
> Thanks a lot!
>
It's my first mission in DTS.
I fill global variables with e-mail addresses (maybe with empty string)
of my accounts.
If I have an e-mail(not empty), I need to create an excel file with
data to this account and send messages to him.
I can do it to some alone account, but I have a problem with looping
I saw an example from http://www.sqldts.com/Default.aspx?246
it helped me very much, but when I run it in the loop it dos not
working!
I think a problem in :
stpEnterLoop.DisableStep = False
stpFinished.DisableStep = True
stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
or I do something wrong.
I have 5 ActiveX task. First - fill global variables.
Second starting a loop. Maybe the problem is that i call a second
ActiveX task from itselfe?
Help me please.
Thanks a lot!It's hard to say something without seeing the code (with dts always is the
same) but first of all I'd put a log for that DTS and I'll try to see what
happen.
--
Current location: Alicante (ES)
"Dima" wrote:
> Hi to everyone!
> It's my first mission in DTS.
> I fill global variables with e-mail addresses (maybe with empty string)
> of my accounts.
> If I have an e-mail(not empty), I need to create an excel file with
> data to this account and send messages to him.
> I can do it to some alone account, but I have a problem with looping
> I saw an example from http://www.sqldts.com/Default.aspx?246
> it helped me very much, but when I run it in the loop it dos not
> working!
> I think a problem in :
> stpEnterLoop.DisableStep = False
> stpFinished.DisableStep = True
> stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
> or I do something wrong.
> I have 5 ActiveX task. First - fill global variables.
> Second starting a loop. Maybe the problem is that i call a second
> ActiveX task from itselfe?
> Help me please.
> Thanks a lot!
>
Friday, February 17, 2012
DTS Import txt File looping.
Hello,
Here i my problem;
I have a number of files with all the exact same format and i need to import
all of them into the same table.
As i never faced the problem before im thinking
to created a txt source and and sql server source with a data transfrom
between them. I want to change the the txt source connection "File Name"
properties for each file I need to import and loop until im done importing
all my files.(FYY: I hold all the table names in a sql table that I refreash
before importing)
So my question are ;
Is this the best approch to solve the issue with i data provided above? how
(high level - unless u want to give me the code ;-) )
or is there other ways that are better then this?
FYI: I cant use a bulk insert because the flat file layout it has follows.
H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
D`DVAL1 1
D`DVAL1 1`DVAL2 2
D`DVAL1 1`DVAL2 2`DVAL3 3
D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
i could use a bulk insert if the layout were as follows BUT its not.
H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
D`DVAL1 1```
D`DVAL1 1`DVAL2 2``
D`DVAL1 1`DVAL2 2`DVAL3 3`
D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
Thanks!!I just did this very thing. You're on the right track.
I disabled the transformation step using Disconnected Edit (in my case this
step is called DTSStep_DTSDataPumpTask_2). I then had an ActiveX script loop
thru the file names calling that transformation step.
Here's some code/pseudocode:
Set oPkg = DTSGlobalVariables.Parent
Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim strThisFileName
strThisFileName = yourfirstfilename ' Get the first file name from
your table here.
' Loop thru your file names here
Do While NOT ...EOF
If oFSO.FileExists(strThisFileName) then
oPkg.Connections("YourSourceFileConnection").DataSource =
strThisFileName ' Set the filename
oPkg.Steps("DTSStep_DTSDataPumpTask_2").Execute
' Import file into table
else
exit do
end if
strThisFileName = yournextfilename
loop
Set oPkg= Nothing
Set oFSO = Nothing
"John Smith" <zzaro@.excite.com> wrote in message
news:O4gYZ96cGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hello,
> Here i my problem;
> I have a number of files with all the exact same format and i need to
> import all of them into the same table.
> As i never faced the problem before im thinking
> to created a txt source and and sql server source with a data transfrom
> between them. I want to change the the txt source connection "File Name"
> properties for each file I need to import and loop until im done importing
> all my files.(FYY: I hold all the table names in a sql table that I
> refreash before importing)
> So my question are ;
> Is this the best approch to solve the issue with i data provided above?
> how (high level - unless u want to give me the code ;-) )
> or is there other ways that are better then this?
> FYI: I cant use a bulk insert because the flat file layout it has follows.
>
> H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
> D`DVAL1 1
> D`DVAL1 1`DVAL2 2
> D`DVAL1 1`DVAL2 2`DVAL3 3
> D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
> i could use a bulk insert if the layout were as follows BUT its not.
> H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
> D`DVAL1 1```
> D`DVAL1 1`DVAL2 2``
> D`DVAL1 1`DVAL2 2`DVAL3 3`
> D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
>
> Thanks!!
>
Here i my problem;
I have a number of files with all the exact same format and i need to import
all of them into the same table.
As i never faced the problem before im thinking
to created a txt source and and sql server source with a data transfrom
between them. I want to change the the txt source connection "File Name"
properties for each file I need to import and loop until im done importing
all my files.(FYY: I hold all the table names in a sql table that I refreash
before importing)
So my question are ;
Is this the best approch to solve the issue with i data provided above? how
(high level - unless u want to give me the code ;-) )
or is there other ways that are better then this?
FYI: I cant use a bulk insert because the flat file layout it has follows.
H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
D`DVAL1 1
D`DVAL1 1`DVAL2 2
D`DVAL1 1`DVAL2 2`DVAL3 3
D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
i could use a bulk insert if the layout were as follows BUT its not.
H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
D`DVAL1 1```
D`DVAL1 1`DVAL2 2``
D`DVAL1 1`DVAL2 2`DVAL3 3`
D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
Thanks!!I just did this very thing. You're on the right track.
I disabled the transformation step using Disconnected Edit (in my case this
step is called DTSStep_DTSDataPumpTask_2). I then had an ActiveX script loop
thru the file names calling that transformation step.
Here's some code/pseudocode:
Set oPkg = DTSGlobalVariables.Parent
Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim strThisFileName
strThisFileName = yourfirstfilename ' Get the first file name from
your table here.
' Loop thru your file names here
Do While NOT ...EOF
If oFSO.FileExists(strThisFileName) then
oPkg.Connections("YourSourceFileConnection").DataSource =
strThisFileName ' Set the filename
oPkg.Steps("DTSStep_DTSDataPumpTask_2").Execute
' Import file into table
else
exit do
end if
strThisFileName = yournextfilename
loop
Set oPkg= Nothing
Set oFSO = Nothing
"John Smith" <zzaro@.excite.com> wrote in message
news:O4gYZ96cGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hello,
> Here i my problem;
> I have a number of files with all the exact same format and i need to
> import all of them into the same table.
> As i never faced the problem before im thinking
> to created a txt source and and sql server source with a data transfrom
> between them. I want to change the the txt source connection "File Name"
> properties for each file I need to import and loop until im done importing
> all my files.(FYY: I hold all the table names in a sql table that I
> refreash before importing)
> So my question are ;
> Is this the best approch to solve the issue with i data provided above?
> how (high level - unless u want to give me the code ;-) )
> or is there other ways that are better then this?
> FYI: I cant use a bulk insert because the flat file layout it has follows.
>
> H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
> D`DVAL1 1
> D`DVAL1 1`DVAL2 2
> D`DVAL1 1`DVAL2 2`DVAL3 3
> D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
> i could use a bulk insert if the layout were as follows BUT its not.
> H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
> D`DVAL1 1```
> D`DVAL1 1`DVAL2 2``
> D`DVAL1 1`DVAL2 2`DVAL3 3`
> D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
>
> Thanks!!
>
Subscribe to:
Posts (Atom)