I have a DTS package that I currently execute manually. This DTS package
reads a text file and imports it into a table.
I would like to have it run automaticly.
For that to work correctly, it would need to check to see if the txt file is
there (the name is always teh same), if it is not there, then to quit. If
it is there, to read and import it as normal, then once completed, for it to
rename teh file and move it to another location on teh computer.
How do I do that?I'd use ActiveXScipt and the FileSystemObject. Here is some sample code:
Function Main()
dim fso, source, dest, oldFile
set fso = CreateObject("Scripting.FileSystemObject")
source = "\\myBox\FAAIVR\BENEFIT_GROUP_INFO.txt"
oldFile = replace(source, ".txt", "_" & replace(date - 7, "/","_") & ".txt")
if (fso.FileExists(source)) then
dest = replace(source, ".txt", "_" & replace(date, "/","_") & ".txt")
fso.MoveFile source, dest
end if
if (fso.FileExists(oldFile)) then
fso.DeleteFile (oldFile)
"Johnfli" wrote:
> I have a DTS package that I currently execute manually. This DTS package
> reads a text file and imports it into a table.
> I would like to have it run automaticly.
> For that to work correctly, it would need to check to see if the txt file is
> there (the name is always teh same), if it is not there, then to quit. If
> it is there, to read and import it as normal, then once completed, for it to
> rename teh file and move it to another location on teh computer.
> How do I do that?
>
>
Showing posts with label text. Show all posts
Showing posts with label text. Show all posts
Friday, March 30, 2012
Wednesday, March 21, 2012
Move Text Data
Does anyone have a proven way to directly move text data (text/image data
type) from a field in one table to a field in another table?
I need to update (not insert) text data in one table with data text data
that exists in another table.
I can't use local variables to process the transaction...
ThanksTake a look at READTEXT, WRITETEXT, TEXTPTR and UPDATETEXT in Books Online.
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:Oyen1sVFGHA.648@.TK2MSFTNGP14.phx.gbl...
> Does anyone have a proven way to directly move text data (text/image data
> type) from a field in one table to a field in another table?
> I need to update (not insert) text data in one table with data text data
> that exists in another table.
> I can't use local variables to process the transaction...
> Thanks
>|||I have looked at these functions... but can't make them work.
the following code gets this error: < Data stream missing from
WRITETEXT statement. >
-- create pointers
declare @.ptr1 binary(16)
declare @.ptr2 binary(16)
-- initialize pointers
select @.ptr1 = textptr(text_field1) from table1 where pk = 1
select @.ptr2 = textptr(text_field2) from table2 where pk = 1
-- move text
writetext ppd_object_version.script_text @.ptr1 readtext
ppd_setup_log.script_text @.ptr2 1 100
the writetext statement seems only to want a inline string, as follows...
writetext ppd_object_version.script_text @.ptr1 'xxxxxxxxx'
Robert
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eKRzmBWFGHA.140@.TK2MSFTNGP12.phx.gbl...
> Take a look at READTEXT, WRITETEXT, TEXTPTR and UPDATETEXT in Books
Online.
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message
> news:Oyen1sVFGHA.648@.TK2MSFTNGP14.phx.gbl...
data
>|||Another possibility is to use VBScript or some other language. For example,
this is pretty trivial if it is a one-time thing:
set conn = CreateObject("ADODB.Connection")
conn.open "<connection string>"
set rs = conn.execute("SELECT primary_key,text_column FROM table")
do while not rs.eof
pk = rs(0) : tc = replace(rs(1), "'", "''")
sql = "UPDATE other_table SET text_column = '" & tc & _
"' WHERE primary_key = " & pk
conn.execute sql,,129
rs.movenext
loop
rs.close: set rs = nothing: conn.close: set conn = nothing
Now, depending on the size of your table, it may take a while, so you may
have to play with commandTimeout. But that took 30 seconds to throw
together, versus who knows how long it will take to develop something using
READTEXT/WRITETEXT etc.
A
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:Oyen1sVFGHA.648@.TK2MSFTNGP14.phx.gbl...
> Does anyone have a proven way to directly move text data (text/image data
> type) from a field in one table to a field in another table?
> I need to update (not insert) text data in one table with data text data
> that exists in another table.
> I can't use local variables to process the transaction...
> Thanks
>|||here's a clearer version of the code:
I have looked at these functions... but can't make them work.
the following code gets this error:
< Data stream missing from WRITETEXT statement. >
-- create pointers
declare @.ptr1 binary(16)
declare @.ptr2 binary(16)
-- initialize pointers
select @.ptr1 = textptr(text_field1) from table1 where pk = 1
select @.ptr2 = textptr(text_field2) from table2 where pk = 1
-- move text
writetext table1.text_field1 @.ptr1 readtext table2.text_field2 ptr2 1 100
the writetext statement seems only to want a inline string, as follows...
writetext table1.text_field1 @.ptr1 'xxxxxxxxx'
Robert
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message news:eKRzmBWFGH
A.140@.TK2MSFTNGP12.phx.gbl...
> Take a look at READTEXT, WRITETEXT, TEXTPTR and UPDATETEXT in Books Online
.
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message
> news:Oyen1sVFGHA.648@.TK2MSFTNGP14.phx.gbl...
>
type) from a field in one table to a field in another table?
I need to update (not insert) text data in one table with data text data
that exists in another table.
I can't use local variables to process the transaction...
ThanksTake a look at READTEXT, WRITETEXT, TEXTPTR and UPDATETEXT in Books Online.
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:Oyen1sVFGHA.648@.TK2MSFTNGP14.phx.gbl...
> Does anyone have a proven way to directly move text data (text/image data
> type) from a field in one table to a field in another table?
> I need to update (not insert) text data in one table with data text data
> that exists in another table.
> I can't use local variables to process the transaction...
> Thanks
>|||I have looked at these functions... but can't make them work.
the following code gets this error: < Data stream missing from
WRITETEXT statement. >
-- create pointers
declare @.ptr1 binary(16)
declare @.ptr2 binary(16)
-- initialize pointers
select @.ptr1 = textptr(text_field1) from table1 where pk = 1
select @.ptr2 = textptr(text_field2) from table2 where pk = 1
-- move text
writetext ppd_object_version.script_text @.ptr1 readtext
ppd_setup_log.script_text @.ptr2 1 100
the writetext statement seems only to want a inline string, as follows...
writetext ppd_object_version.script_text @.ptr1 'xxxxxxxxx'
Robert
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eKRzmBWFGHA.140@.TK2MSFTNGP12.phx.gbl...
> Take a look at READTEXT, WRITETEXT, TEXTPTR and UPDATETEXT in Books
Online.
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message
> news:Oyen1sVFGHA.648@.TK2MSFTNGP14.phx.gbl...
data
>|||Another possibility is to use VBScript or some other language. For example,
this is pretty trivial if it is a one-time thing:
set conn = CreateObject("ADODB.Connection")
conn.open "<connection string>"
set rs = conn.execute("SELECT primary_key,text_column FROM table")
do while not rs.eof
pk = rs(0) : tc = replace(rs(1), "'", "''")
sql = "UPDATE other_table SET text_column = '" & tc & _
"' WHERE primary_key = " & pk
conn.execute sql,,129
rs.movenext
loop
rs.close: set rs = nothing: conn.close: set conn = nothing
Now, depending on the size of your table, it may take a while, so you may
have to play with commandTimeout. But that took 30 seconds to throw
together, versus who knows how long it will take to develop something using
READTEXT/WRITETEXT etc.
A
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:Oyen1sVFGHA.648@.TK2MSFTNGP14.phx.gbl...
> Does anyone have a proven way to directly move text data (text/image data
> type) from a field in one table to a field in another table?
> I need to update (not insert) text data in one table with data text data
> that exists in another table.
> I can't use local variables to process the transaction...
> Thanks
>|||here's a clearer version of the code:
I have looked at these functions... but can't make them work.
the following code gets this error:
< Data stream missing from WRITETEXT statement. >
-- create pointers
declare @.ptr1 binary(16)
declare @.ptr2 binary(16)
-- initialize pointers
select @.ptr1 = textptr(text_field1) from table1 where pk = 1
select @.ptr2 = textptr(text_field2) from table2 where pk = 1
-- move text
writetext table1.text_field1 @.ptr1 readtext table2.text_field2 ptr2 1 100
the writetext statement seems only to want a inline string, as follows...
writetext table1.text_field1 @.ptr1 'xxxxxxxxx'
Robert
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message news:eKRzmBWFGH
A.140@.TK2MSFTNGP12.phx.gbl...
> Take a look at READTEXT, WRITETEXT, TEXTPTR and UPDATETEXT in Books Online
.
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message
> news:Oyen1sVFGHA.648@.TK2MSFTNGP14.phx.gbl...
>
Monday, March 12, 2012
move reports service system
--____KFWWXQZZZBOZZRPOGMHS____
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
hi all
how can move all reports system (web and database) from one box to other = box.
thx. from spain.
_______________________________
* Alberto Garc=EDa S=E1nchez
* Subdirector Inform=E1tica
* Autobar Spain, S.A.
* Email: agarcia@.autobar-spain.com
* Web: www.autobar-spain.com * Telef. +34 916787345 _______________________________
___________________________________________________________________________=
Este mensaje se dirige exclusivamente a su destinatario
y puede contener informaci=F3n privilegiada o confidencial. Si no es vd. = el destinatario
indicado, queda notificado de que la utilizaci=F3n, divulgaci=F3n y/o = copia sin
autorizaci=F3n est=E1 prohibida en virtud de la legislaci=F3n vigente. Si = ha
recibido este mensaje por error, le rogamos que nos lo comunique
inmediatamente por esta misma v=EDa y proceda a su destrucci=F3n.
This message is intended exclusively for its addressee and may contain
information that is CONFIDENTIAL and protected by professional privilege.
If you are not the intended recipient you are hereby notified that any
dissemination, copy or disclosure of this communication is strictly
prohibited by law. If this message has been received in error, please
immediately notify us via e-mail and delete it.
___________________________________________________________________________
--____KFWWXQZZZBOZZRPOGMHS____
Content-Type: multipart/related; boundary="____BIYGCADYSRXUIZFQTHGP____"
--____BIYGCADYSRXUIZFQTHGP____
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: quoted-printable
&
hi all
how can move all reports system (web and database) from one box to = other box.
thx. from spain.
_______________________________
* Alberto Garc=EDa S=E1nchez* Subdirector Inform=E1tica* = Autobar Spain, S.A.* Email: agarcia@.autobar-spain.com* Web: www.autobar-spain.com * Telef. +34 916787345 = _______________________________
__________________________________________________________________= _________Este mensaje se dirige exclusivamente a su destinatarioy = puede contener informaci=F3n privilegiada o confidencial. Si no es vd. el = destinatarioindicado, queda notificado de que la utilizaci=F3n, = divulgaci=F3n y/o copia sinautorizaci=F3n est=E1 prohibida en virtud = de la legislaci=F3n vigente. Si harecibido este mensaje por error, le = rogamos que nos lo comuniqueinmediatamente por esta misma v=EDa y = proceda a su destrucci=F3n. This message is intended = exclusively for its addressee and may containinformation that is = CONFIDENTIAL and protected by professional privilege.If you are not = the intended recipient you are hereby notified that anydissemination, = copy or disclosure of this communication is strictlyprohibited by law. = If this message has been received in error, pleaseimmediately notify = us via e-mail and delete it.___________________________________________= ________________________________
--____BIYGCADYSRXUIZFQTHGP____--
--____KFWWXQZZZBOZZRPOGMHS____--This is a multi-part message in MIME format.
--=_NextPart_000_00F6_01C5434B.2C395450
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi alf,
look here, there a kb from MS for it:
http://support.microsoft.com/default.aspx?scid=3D842425
Moving the database will affect the web because the data for the UI is =stored in SQL Server.
First you have to install the Reporting Services on the second box of =course, because there are many entries made during the installation =process which won=B4t be done without the instal process and just plain =copying.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
<alf> schrieb im Newsbeitrag =news:%23$4m8kfQFHA.3120@.TK2MSFTNGP10.phx.gbl...
hi all
how can move all reports system (web and database) from one box to =other box.
thx. from spain.
_______________________________
* Alberto Garc=EDa S=E1nchez
* Subdirector Inform=E1tica
* Autobar Spain, S.A.
* Email: agarcia@.autobar-spain.com
* Web: www.autobar-spain.com * Telef. +34 916787345 _______________________________
=_________________________________________________________________________=__
Este mensaje se dirige exclusivamente a su destinatario
y puede contener informaci=F3n privilegiada o confidencial. Si no es =vd. el destinatario
indicado, queda notificado de que la utilizaci=F3n, divulgaci=F3n y/o =copia sin
autorizaci=F3n est=E1 prohibida en virtud de la legislaci=F3n vigente. =Si ha
recibido este mensaje por error, le rogamos que nos lo comunique
inmediatamente por esta misma v=EDa y proceda a su destrucci=F3n.
This message is intended exclusively for its addressee and may contain
information that is CONFIDENTIAL and protected by professional =privilege.
If you are not the intended recipient you are hereby notified that any
dissemination, copy or disclosure of this communication is strictly
prohibited by law. If this message has been received in error, please
immediately notify us via e-mail and delete it.
=_________________________________________________________________________=__
--=_NextPart_000_00F6_01C5434B.2C395450
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi alf,
look here, there a kb from MS for =it:
http://support.microsoft.com/default.aspx?scid=3D842425">http://s=upport.microsoft.com/default.aspx?scid=3D842425
Moving the database will affect the web =because the data for the UI is stored in SQL Server.
First you have to install the Reporting Services =on the second box of course, because there are many entries made during the installation process which won=B4t be done without the instal process =and just plain copying.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
schrieb im Newsbeitrag news:%23$4m8kfQFHA.=3120@.TK2MSFTNGP10.phx.gbl...
hi all
how can move all reports system (web and database) from one box =to other box.
thx. from spain.
_______________________________
* Alberto Garc=EDa S=E1nchez* Subdirector Inform=E1tica* =Autobar Spain, S.A.* Email: agarcia@.autobar-spain.com* Web: http://www.autobar-spain.com">www.autobar-spain.com * Telef. +34 916787345 _______________________________
=________________________________________________________________=___________Este mensaje se dirige exclusivamente a su destinatarioy puede contener = informaci=F3n privilegiada o confidencial. Si no es vd. el destinatarioindicado, queda notificado de que la utilizaci=F3n, =divulgaci=F3n y/o copia sinautorizaci=F3n est=E1 prohibida en virtud de la =legislaci=F3n vigente. Si harecibido este mensaje por error, le rogamos que nos =lo comuniqueinmediatamente por esta misma v=EDa y proceda a su destrucci=F3n. This message is intended exclusively for =its addressee and may containinformation that is CONFIDENTIAL and =protected by professional privilege.If you are not the intended recipient you =are hereby notified that anydissemination, copy or disclosure of this communication is strictlyprohibited by law. If this message has =been received in error, pleaseimmediately notify us via e-mail and =delete =it.__________________________________________________________________=_________
--=_NextPart_000_00F6_01C5434B.2C395450--
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
hi all
how can move all reports system (web and database) from one box to other = box.
thx. from spain.
_______________________________
* Alberto Garc=EDa S=E1nchez
* Subdirector Inform=E1tica
* Autobar Spain, S.A.
* Email: agarcia@.autobar-spain.com
* Web: www.autobar-spain.com * Telef. +34 916787345 _______________________________
___________________________________________________________________________=
Este mensaje se dirige exclusivamente a su destinatario
y puede contener informaci=F3n privilegiada o confidencial. Si no es vd. = el destinatario
indicado, queda notificado de que la utilizaci=F3n, divulgaci=F3n y/o = copia sin
autorizaci=F3n est=E1 prohibida en virtud de la legislaci=F3n vigente. Si = ha
recibido este mensaje por error, le rogamos que nos lo comunique
inmediatamente por esta misma v=EDa y proceda a su destrucci=F3n.
This message is intended exclusively for its addressee and may contain
information that is CONFIDENTIAL and protected by professional privilege.
If you are not the intended recipient you are hereby notified that any
dissemination, copy or disclosure of this communication is strictly
prohibited by law. If this message has been received in error, please
immediately notify us via e-mail and delete it.
___________________________________________________________________________
--____KFWWXQZZZBOZZRPOGMHS____
Content-Type: multipart/related; boundary="____BIYGCADYSRXUIZFQTHGP____"
--____BIYGCADYSRXUIZFQTHGP____
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: quoted-printable
&
hi all
how can move all reports system (web and database) from one box to = other box.
thx. from spain.
_______________________________
* Alberto Garc=EDa S=E1nchez* Subdirector Inform=E1tica* = Autobar Spain, S.A.* Email: agarcia@.autobar-spain.com* Web: www.autobar-spain.com * Telef. +34 916787345 = _______________________________
__________________________________________________________________= _________Este mensaje se dirige exclusivamente a su destinatarioy = puede contener informaci=F3n privilegiada o confidencial. Si no es vd. el = destinatarioindicado, queda notificado de que la utilizaci=F3n, = divulgaci=F3n y/o copia sinautorizaci=F3n est=E1 prohibida en virtud = de la legislaci=F3n vigente. Si harecibido este mensaje por error, le = rogamos que nos lo comuniqueinmediatamente por esta misma v=EDa y = proceda a su destrucci=F3n. This message is intended = exclusively for its addressee and may containinformation that is = CONFIDENTIAL and protected by professional privilege.If you are not = the intended recipient you are hereby notified that anydissemination, = copy or disclosure of this communication is strictlyprohibited by law. = If this message has been received in error, pleaseimmediately notify = us via e-mail and delete it.___________________________________________= ________________________________
--____BIYGCADYSRXUIZFQTHGP____--
--____KFWWXQZZZBOZZRPOGMHS____--This is a multi-part message in MIME format.
--=_NextPart_000_00F6_01C5434B.2C395450
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi alf,
look here, there a kb from MS for it:
http://support.microsoft.com/default.aspx?scid=3D842425
Moving the database will affect the web because the data for the UI is =stored in SQL Server.
First you have to install the Reporting Services on the second box of =course, because there are many entries made during the installation =process which won=B4t be done without the instal process and just plain =copying.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
<alf> schrieb im Newsbeitrag =news:%23$4m8kfQFHA.3120@.TK2MSFTNGP10.phx.gbl...
hi all
how can move all reports system (web and database) from one box to =other box.
thx. from spain.
_______________________________
* Alberto Garc=EDa S=E1nchez
* Subdirector Inform=E1tica
* Autobar Spain, S.A.
* Email: agarcia@.autobar-spain.com
* Web: www.autobar-spain.com * Telef. +34 916787345 _______________________________
=_________________________________________________________________________=__
Este mensaje se dirige exclusivamente a su destinatario
y puede contener informaci=F3n privilegiada o confidencial. Si no es =vd. el destinatario
indicado, queda notificado de que la utilizaci=F3n, divulgaci=F3n y/o =copia sin
autorizaci=F3n est=E1 prohibida en virtud de la legislaci=F3n vigente. =Si ha
recibido este mensaje por error, le rogamos que nos lo comunique
inmediatamente por esta misma v=EDa y proceda a su destrucci=F3n.
This message is intended exclusively for its addressee and may contain
information that is CONFIDENTIAL and protected by professional =privilege.
If you are not the intended recipient you are hereby notified that any
dissemination, copy or disclosure of this communication is strictly
prohibited by law. If this message has been received in error, please
immediately notify us via e-mail and delete it.
=_________________________________________________________________________=__
--=_NextPart_000_00F6_01C5434B.2C395450
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi alf,
look here, there a kb from MS for =it:
http://support.microsoft.com/default.aspx?scid=3D842425">http://s=upport.microsoft.com/default.aspx?scid=3D842425
Moving the database will affect the web =because the data for the UI is stored in SQL Server.
First you have to install the Reporting Services =on the second box of course, because there are many entries made during the installation process which won=B4t be done without the instal process =and just plain copying.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
schrieb im Newsbeitrag news:%23$4m8kfQFHA.=3120@.TK2MSFTNGP10.phx.gbl...
hi all
how can move all reports system (web and database) from one box =to other box.
thx. from spain.
_______________________________
* Alberto Garc=EDa S=E1nchez* Subdirector Inform=E1tica* =Autobar Spain, S.A.* Email: agarcia@.autobar-spain.com* Web: http://www.autobar-spain.com">www.autobar-spain.com * Telef. +34 916787345 _______________________________
=________________________________________________________________=___________Este mensaje se dirige exclusivamente a su destinatarioy puede contener = informaci=F3n privilegiada o confidencial. Si no es vd. el destinatarioindicado, queda notificado de que la utilizaci=F3n, =divulgaci=F3n y/o copia sinautorizaci=F3n est=E1 prohibida en virtud de la =legislaci=F3n vigente. Si harecibido este mensaje por error, le rogamos que nos =lo comuniqueinmediatamente por esta misma v=EDa y proceda a su destrucci=F3n. This message is intended exclusively for =its addressee and may containinformation that is CONFIDENTIAL and =protected by professional privilege.If you are not the intended recipient you =are hereby notified that anydissemination, copy or disclosure of this communication is strictlyprohibited by law. If this message has =been received in error, pleaseimmediately notify us via e-mail and =delete =it.__________________________________________________________________=_________
--=_NextPart_000_00F6_01C5434B.2C395450--
Subscribe to:
Posts (Atom)