Integrated Security: loop

Showing posts with label loop. Show all posts

Wednesday, March 28, 2012

Intermittent Error! Help!

I have a sequence of 3 operations, and its repeated many times by an
aplication (timer loop)
1) Open a transaction
2) Execute 1 (ONE) insert in a "simple" table. This table has only an
auto-increment identificator and some fields numbers and chars.
3) Comitt the transaction
This proccess is executed normaly during many days, but many times this
command returns "0 rows affected" to the application, but with no exception,
just returns "0 rows affected".
The version is 2000 with all SP.
Thanks
RaphaelConfirm if there are any triggers on the table that might be causing this.
"Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
news:OwPvNdq7FHA.2092@.TK2MSFTNGP12.phx.gbl...
>I have a sequence of 3 operations, and its repeated many times by an
>aplication (timer loop)
> 1) Open a transaction
> 2) Execute 1 (ONE) insert in a "simple" table. This table has only an
> auto-increment identificator and some fields numbers and chars.
> 3) Comitt the transaction
> This proccess is executed normaly during many days, but many times this
> command returns "0 rows affected" to the application, but with no
> exception, just returns "0 rows affected".
> The version is 2000 with all SP.
> Thanks
> Raphael
>|||Also confirm if there are any constraints.
"Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
news:OwPvNdq7FHA.2092@.TK2MSFTNGP12.phx.gbl...
>I have a sequence of 3 operations, and its repeated many times by an
>aplication (timer loop)
> 1) Open a transaction
> 2) Execute 1 (ONE) insert in a "simple" table. This table has only an
> auto-increment identificator and some fields numbers and chars.
> 3) Comitt the transaction
> This proccess is executed normaly during many days, but many times this
> command returns "0 rows affected" to the application, but with no
> exception, just returns "0 rows affected".
> The version is 2000 with all SP.
> Thanks
> Raphael
>|||Can you show the code? Where are you getting the 0 rows affected? By the
way there is no need to wrap a single Insert in a transaction as it is
ATOMIC on it's own.
Andrew J. Kelly SQL MVP
"Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
news:OwPvNdq7FHA.2092@.TK2MSFTNGP12.phx.gbl...
>I have a sequence of 3 operations, and its repeated many times by an
>aplication (timer loop)
> 1) Open a transaction
> 2) Execute 1 (ONE) insert in a "simple" table. This table has only an
> auto-increment identificator and some fields numbers and chars.
> 3) Comitt the transaction
> This proccess is executed normaly during many days, but many times this
> command returns "0 rows affected" to the application, but with no
> exception, just returns "0 rows affected".
> The version is 2000 with all SP.
> Thanks
> Raphael
>|||1) Create TABLE COMMAND
CREATE TABLE [cm].[BILHETE] (
[IDBILHETE] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[BILHETE] [varchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[IDHOTEL] [numeric](18, 0) NULL ,
[DATACAPTURA] [datetime] NULL ) ON [PRIMARY]
2) Insert COMMAND executed by the application. Only an example, the error is
intermittent and random.
INSERT INTO BILHETE (BILHETE, IDHOTEL, DATACAPTURA) VALUES
('1450290000001010022161313', 1, getDate());
3) I've already tried with a Stored Procedure, but accurs the same problem.
After many times the SP returns "0 (zero) rows affected". Do not insert
nothing and no exceptions are generated.
CREATE PROCEDURE CM.SP_BILHETE(@.Bilhete AS Varchar(220),@.IdHotel AS
Numeric ) AS
INSERT INTO BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
(@.Bilhete,@.IdHotel,getdate());
3) The table has the follow DELETE TRIGGER, that saves the deleteds records
in another table _BILHETE with the same structure:
CREATE TRIGGER tdBilhete
ON BILHETE
FOR DELETE AS
DECLARE @.STRBILHETE VARCHAR(120)
DECLARE @.IDHOTEL NUMERIC
SELECT @.STRBILHETE = D.BILHETE FROM DELETED D
SELECT @.IDHOTEL = D.IDHOTEL FROM DELETED D
INSERT INTO _BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
(@.STRBILHETE,@.IDHOTEL,getdate());
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escreveu na mensagem
news:efrF9oq7FHA.3416@.TK2MSFTNGP15.phx.gbl...
> Can you show the code? Where are you getting the 0 rows affected? By the
> way there is no need to wrap a single Insert in a transaction as it is
> ATOMIC on it's own.
> --
> Andrew J. Kelly SQL MVP
>
> "Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
> news:OwPvNdq7FHA.2092@.TK2MSFTNGP12.phx.gbl...
>|||Well you still didn't say how you determined the result was 0. I see no
code to indicate where you are collecting @.@.ROWCOUNT or returning any value.
Why are you using Numeric(18,0) when the value is obviously an Integer? You
are using 9 bytes to store what you can in 4 bytes with an Integer. You
also do not specifyt he size in the parameter of the sp. Always specify the
size when you declare a DataType or you can get unexpected results. Are you
sure there are no INSERT triggers on this table?
Andrew J. Kelly SQL MVP
"Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
news:esPC7Gr7FHA.3224@.TK2MSFTNGP09.phx.gbl...
> 1) Create TABLE COMMAND
> CREATE TABLE [cm].[BILHETE] (
> [IDBILHETE] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [BILHETE] [varchar] (500) COLLATE Latin1_General_CI_AS NULL ,
> [IDHOTEL] [numeric](18, 0) NULL ,
> [DATACAPTURA] [datetime] NULL ) ON [PRIMARY]
> 2) Insert COMMAND executed by the application. Only an example, the error
> is
> intermittent and random.
> INSERT INTO BILHETE (BILHETE, IDHOTEL, DATACAPTURA) VALUES
> ('1450290000001010022161313', 1, getDate());
> 3) I've already tried with a Stored Procedure, but accurs the same
> problem.
> After many times the SP returns "0 (zero) rows affected". Do not insert
> nothing and no exceptions are generated.
> CREATE PROCEDURE CM.SP_BILHETE(@.Bilhete AS Varchar(220),@.IdHotel AS
> Numeric ) AS
> INSERT INTO BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
> (@.Bilhete,@.IdHotel,getdate());
> 3) The table has the follow DELETE TRIGGER, that saves the deleteds
> records
> in another table _BILHETE with the same structure:
> CREATE TRIGGER tdBilhete
> ON BILHETE
> FOR DELETE AS
> DECLARE @.STRBILHETE VARCHAR(120)
> DECLARE @.IDHOTEL NUMERIC
> SELECT @.STRBILHETE = D.BILHETE FROM DELETED D
> SELECT @.IDHOTEL = D.IDHOTEL FROM DELETED D
> INSERT INTO _BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
> (@.STRBILHETE,@.IDHOTEL,getdate());
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escreveu na mensagem
> news:efrF9oq7FHA.3416@.TK2MSFTNGP15.phx.gbl...
>|||The result was determined ZERO trought the ROWSAFFECTED property from DB
connection class, that returns the @.@.ROWCOUNT value. So i verified through a
select tha the record was no inserted.
The SP was an alternative, but even in the application the INSERT generated
the same error.
There's no INSERT TRIGGER on this table.
Thanks Andrew i really appreciated your dedication with my problem...
Raphael
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escreveu na mensagem
news:%23u$agwr7FHA.4036@.TK2MSFTNGP11.phx.gbl...
> Well you still didn't say how you determined the result was 0. I see no
> code to indicate where you are collecting @.@.ROWCOUNT or returning any
> value. Why are you using Numeric(18,0) when the value is obviously an
> Integer? You are using 9 bytes to store what you can in 4 bytes with an
> Integer. You also do not specifyt he size in the parameter of the sp.
> Always specify the size when you declare a DataType or you can get
> unexpected results. Are you sure there are no INSERT triggers on this
> table?
> --
> Andrew J. Kelly SQL MVP
>
> "Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
> news:esPC7Gr7FHA.3224@.TK2MSFTNGP09.phx.gbl...
>|||Are there any unique indexes on the table other than the PK constraint? If
you are positive there is no other triggers that can affect this then there
must be an error of some type generated when it fails to insert. In the sp
try this to see if it makes any difference.
DECLARE @.Error INT, @.Rows INT
INSERT INTO BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
(@.Bilhete,@.IdHotel,getdate());
SELECT @.Error = @.@.ERROR, @.Rows = @.@.ROWCOUNT
IF @.Error <> 0
RAISERROR(@.Error, 16,1)
RETURN @.Rows
Andrew J. Kelly SQL MVP
"Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
news:eEtxEAs7FHA.1420@.TK2MSFTNGP09.phx.gbl...
> The result was determined ZERO trought the ROWSAFFECTED property from DB
> connection class, that returns the @.@.ROWCOUNT value. So i verified through
> a select tha the record was no inserted.
> The SP was an alternative, but even in the application the INSERT
> generated the same error.
> There's no INSERT TRIGGER on this table.
> Thanks Andrew i really appreciated your dedication with my problem...
> Raphael
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escreveu na mensagem
> news:%23u$agwr7FHA.4036@.TK2MSFTNGP11.phx.gbl...
>|||Good idea Andrews,
I will test this and after i post here the result.
tks,
Raphael
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escreveu na mensagem
news:O$TB20s7FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Are there any unique indexes on the table other than the PK constraint? If
> you are positive there is no other triggers that can affect this then
> there must be an error of some type generated when it fails to insert. In
> the sp try this to see if it makes any difference.
> DECLARE @.Error INT, @.Rows INT
> INSERT INTO BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
> (@.Bilhete,@.IdHotel,getdate());
> SELECT @.Error = @.@.ERROR, @.Rows = @.@.ROWCOUNT
> IF @.Error <> 0
> RAISERROR(@.Error, 16,1)
> RETURN @.Rows
>
> --
> Andrew J. Kelly SQL MVP
>
> "Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
> news:eEtxEAs7FHA.1420@.TK2MSFTNGP09.phx.gbl...
>

Intermittent Error! Help!

Friday, February 24, 2012

Integration Services For Each Loop Question

.
1 - Open your new SSIS project, and add a Foreach Loop container
2 - In the properties, make it a Foreach File Enumerator, and choose
the folder you want. Set the other properties, such as if you want the
fully qualified filename
3 - In the variable mappings area, Add a new user variable named
"FileName"; it should appear as User::FileName. Click OK & you're done
with the loop container
4 - Now go modify the variable properties (View, Other Windows,
Variables). Highlight the variable created in #3 and press F4 to get
the properties window.
5 - Change "EvaluateAsExpression" to True
6 - Change the "Expression" property to be your SQL Statement, but now
you're including your variable (notice the single quotes around your
variable):
"INSERT test SELECT '" + @.[User::FileName] + "'"
7 - Now add an "Execute SQL Task" to your loop container. Choose your
database. Change the SQL SourceType to be Variable.
8 - Choose your variable, which has now been filled with your SQL
statement.
9 - test out by debugging & then select the data from your table
SELECT * FROM test
Your next step - you mentioned it was to copy the files from the
original location to a new location using the table to loop. You can
skip all of the above madness, by just using the file system task, and
copy the entire contents of directory #1 to directory #2.
---
I'm wondering if this can be done (I've had no luck so far):
I would like to loop thru a set of files in a directory, place the file
name
into a variable, then insert the file name as a record into a table.
I've gotten the for each (file) loop set up and working fine, placing
the
file name into a user variable, but can't figure out how to use the
value of
the variable to insert it into a table.
The next step would be to loop thru the records in the table retrieving
each
file name into a variable to use in another for each file loop to copy
the
file to another location.
The purpose of the entire exercise is to put file names into two tables
for
log backups. I'm backing up log files to a local directory, and want to
copy
them to a network share. Since I only want to copy newly backed up
files, I
would like to go thru all the files on the local drive, write them to a
table, go thru all the previously copied files on the network share,
write
the names to another table, join the two tables to find which files do
not
exist on the network share and copy them to it.
There may be a better way to do this, but I've not figured it out so
far.
Thanks for any assistance with this.
TomT|||Thanks Corey - that's the direction I wanted to go with this (getting the
variable mapped properly in the sql statement.
There are a couple of reasons I'm going about things in this way. Since I'm
doing log backups every hour locally, and then copying these files over to
another system, I want to only copy over the most recent file. The other
(network) system should also have copies of previously copied logs. I wanted
to keep the i/o down, and only copy over the latest backup.
Secondly, I wanted to do this as a way to get more familiar with SSIS. I
think all I need to do really is just get the name of the most recent backup
log file, but I don't know how to get that info via SSIS.
So, e.g., say I've got two days worth of log backups on the local system,
then copy these over (say manually just for now) to a network system. Then,
the next time a log backup takes place on the local system, I want to copy
JUST that one over to the network system. If I could just identify that
particular (most recent) log backup's file name, I'd be set.
Again, this is just as much to learn SSIS as anything else right now.
Thanks for your help
"CoreyB" wrote:

> TomT - it sounds like everyone has an opinion! Here is a way to do
> what you want with SSIS. Then see below that for my opinion

.
> 1 - Open your new SSIS project, and add a Foreach Loop container
> 2 - In the properties, make it a Foreach File Enumerator, and choose
> the folder you want. Set the other properties, such as if you want the
> fully qualified filename
> 3 - In the variable mappings area, Add a new user variable named
> "FileName"; it should appear as User::FileName. Click OK & you're done
> with the loop container
> 4 - Now go modify the variable properties (View, Other Windows,
> Variables). Highlight the variable created in #3 and press F4 to get
> the properties window.
> 5 - Change "EvaluateAsExpression" to True
> 6 - Change the "Expression" property to be your SQL Statement, but now
> you're including your variable (notice the single quotes around your
> variable):
> "INSERT test SELECT '" + @.[User::FileName] + "'"
> 7 - Now add an "Execute SQL Task" to your loop container. Choose your
> database. Change the SQL SourceType to be Variable.
> 8 - Choose your variable, which has now been filled with your SQL
> statement.
> 9 - test out by debugging & then select the data from your table
> SELECT * FROM test
>
> Your next step - you mentioned it was to copy the files from the
> original location to a new location using the table to loop. You can
> skip all of the above madness, by just using the file system task, and
> copy the entire contents of directory #1 to directory #2.
>
> ---
> I'm wondering if this can be done (I've had no luck so far):
> I would like to loop thru a set of files in a directory, place the file
> name
> into a variable, then insert the file name as a record into a table.
> I've gotten the for each (file) loop set up and working fine, placing
> the
> file name into a user variable, but can't figure out how to use the
> value of
> the variable to insert it into a table.
> The next step would be to loop thru the records in the table retrieving
> each
> file name into a variable to use in another for each file loop to copy
> the
> file to another location.
> The purpose of the entire exercise is to put file names into two tables
> for
> log backups. I'm backing up log files to a local directory, and want to
> copy
> them to a network share. Since I only want to copy newly backed up
> files, I
> would like to go thru all the files on the local drive, write them to a
> table, go thru all the previously copied files on the network share,
> write
> the names to another table, join the two tables to find which files do
> not
> exist on the network share and copy them to it.
> There may be a better way to do this, but I've not figured it out so
> far.
> Thanks for any assistance with this.
> TomT
>|||Sounds good - good luck!
TomT wrote:[vbcol=seagreen]
> Thanks Corey - that's the direction I wanted to go with this (getting the
> variable mapped properly in the sql statement.
> There are a couple of reasons I'm going about things in this way. Since I'
m
> doing log backups every hour locally, and then copying these files over to
> another system, I want to only copy over the most recent file. The other
> (network) system should also have copies of previously copied logs. I want
ed
> to keep the i/o down, and only copy over the latest backup.
> Secondly, I wanted to do this as a way to get more familiar with SSIS. I
> think all I need to do really is just get the name of the most recent back
up
> log file, but I don't know how to get that info via SSIS.
> So, e.g., say I've got two days worth of log backups on the local system,
> then copy these over (say manually just for now) to a network system. Then
,
> the next time a log backup takes place on the local system, I want to copy
> JUST that one over to the network system. If I could just identify that
> particular (most recent) log backup's file name, I'd be set.
> Again, this is just as much to learn SSIS as anything else right now.
> Thanks for your help
> "CoreyB" wrote:
>

Integration Services For Each Loop Question

I'm wondering if this can be done (I've had no luck so far):
I would like to loop thru a set of files in a directory, place the file name
into a variable, then insert the file name as a record into a table.
I've gotten the for each (file) loop set up and working fine, placing the
file name into a user variable, but can't figure out how to use the value of
the variable to insert it into a table.
The next step would be to loop thru the records in the table retrieving each
file name into a variable to use in another for each file loop to copy the
file to another location.
The purpose of the entire exercise is to put file names into two tables for
log backups. I'm backing up log files to a local directory, and want to copy
them to a network share. Since I only want to copy newly backed up files, I
would like to go thru all the files on the local drive, write them to a
table, go thru all the previously copied files on the network share, write
the names to another table, join the two tables to find which files do not
exist on the network share and copy them to it.
There may be a better way to do this, but I've not figured it out so far.
Thanks for any assistance with this.
TomTTomT wrote:
> I'm wondering if this can be done (I've had no luck so far):
> I would like to loop thru a set of files in a directory, place the file name
> into a variable, then insert the file name as a record into a table.
> I've gotten the for each (file) loop set up and working fine, placing the
> file name into a user variable, but can't figure out how to use the value of
> the variable to insert it into a table.
> The next step would be to loop thru the records in the table retrieving each
> file name into a variable to use in another for each file loop to copy the
> file to another location.
> The purpose of the entire exercise is to put file names into two tables for
> log backups. I'm backing up log files to a local directory, and want to copy
> them to a network share. Since I only want to copy newly backed up files, I
> would like to go thru all the files on the local drive, write them to a
> table, go thru all the previously copied files on the network share, write
> the names to another table, join the two tables to find which files do not
> exist on the network share and copy them to it.
> There may be a better way to do this, but I've not figured it out so far.
> Thanks for any assistance with this.
> TomT
You're going about this the hard way. If you can run xp_cmdshell, you
can do this with a single insert statement:
CREATE TABLE #Table (
[FileName] VARCHAR(255)
)
DECLARE @.Command VARCHAR(255)
SELECT @.Command = 'master..xp_cmdshell ''DIR /B C:\WINDOWS'''
INSERT INTO #Table
EXEC (@.Command)
SELECT * FROM #Table
DROP TABLE #Table
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy, I guess I was hoping to incoporate this into an SSIS package
which would do the backups, history clean up, and file copy. Also, trying to
get up to speed on SSIS capabilities.
"Tracy McKibben" wrote:
> TomT wrote:
> > I'm wondering if this can be done (I've had no luck so far):
> >
> > I would like to loop thru a set of files in a directory, place the file name
> > into a variable, then insert the file name as a record into a table.
> >
> > I've gotten the for each (file) loop set up and working fine, placing the
> > file name into a user variable, but can't figure out how to use the value of
> > the variable to insert it into a table.
> >
> > The next step would be to loop thru the records in the table retrieving each
> > file name into a variable to use in another for each file loop to copy the
> > file to another location.
> >
> > The purpose of the entire exercise is to put file names into two tables for
> > log backups. I'm backing up log files to a local directory, and want to copy
> > them to a network share. Since I only want to copy newly backed up files, I
> > would like to go thru all the files on the local drive, write them to a
> > table, go thru all the previously copied files on the network share, write
> > the names to another table, join the two tables to find which files do not
> > exist on the network share and copy them to it.
> >
> > There may be a better way to do this, but I've not figured it out so far.
> >
> > Thanks for any assistance with this.
> >
> > TomT
> You're going about this the hard way. If you can run xp_cmdshell, you
> can do this with a single insert statement:
> CREATE TABLE #Table (
> [FileName] VARCHAR(255)
> )
> DECLARE @.Command VARCHAR(255)
> SELECT @.Command = 'master..xp_cmdshell ''DIR /B C:\WINDOWS'''
> INSERT INTO #Table
> EXEC (@.Command)
> SELECT * FROM #Table
> DROP TABLE #Table
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||TomT wrote:
> Thanks Tracy, I guess I was hoping to incoporate this into an SSIS package
> which would do the backups, history clean up, and file copy. Also, trying to
> get up to speed on SSIS capabilities.
>
Ahh, ok... Well, this may or may not interest you, I have a script that
will automatically backup any database on your server, including t-logs,
and will handle the history cleanup too...
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/AutomaticBackupOfAllDatabases
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy - that's very helpful, and definately of interest to me.
At the same time, I'm still interested in finding out if there's a way to do
what I'm attempting via SSIS, particularly getting the file loop variable
into a sql insert statement.
I appreciate your feedback and assistance with this issue...
"Tracy McKibben" wrote:
> TomT wrote:
> > Thanks Tracy, I guess I was hoping to incoporate this into an SSIS package
> > which would do the backups, history clean up, and file copy. Also, trying to
> > get up to speed on SSIS capabilities.
> >
> Ahh, ok... Well, this may or may not interest you, I have a script that
> will automatically backup any database on your server, including t-logs,
> and will handle the history cleanup too...
> http://realsqlguy.com/twiki/bin/view/RealSQLGuy/AutomaticBackupOfAllDatabases
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Hi,
Thanks for your post!
To make me clear about your issue, I appreciate to know:
1) You could retrieve each filename in a variable now and you didn't know
how to insert it into a table?
If so, you can write SQL like this:
INSERT INTO Table_Name([colname1],...[colnamen])
values(@.FileName1,...,[@.othervalue])
2) Are your purpose as following ?
Retrieve related files on the local drive and insert their names into
one table;
Retrieve related files on the remote network drive and insert their
names into a second table;
Compare the data between the two tables, if the 1st table's files names
are not in the second table, copy them into the network drive.
For such issue, you can't directly use TSQL in workflow control in SSIS.
I recommend you use "Execute SQL task" and map the variable to a
parameter in the task.
Right click task->Edit->Parameter Mapping.
Then you could use the parameter as you mentioned in the SQLstatement in
General tab of "Execute SQL task"
http://msdn2.microsoft.com/en-us/library/ms141003.aspx
Also, you could configure "variable mapping" of foreach loop container
to the variable you uses.
Other tasks like ActiveX Scripts Task can also help you deal with this
case.
http://msdn2.microsoft.com/en-us/library/ms137525.aspx
If you have any other concerns, please feel free to let me know. I'm
happy for your assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others:
https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||TomT - it sounds like everyone has an opinion! Here is a way to do
what you want with SSIS. Then see below that for my opinion :).
1 - Open your new SSIS project, and add a Foreach Loop container
2 - In the properties, make it a Foreach File Enumerator, and choose
the folder you want. Set the other properties, such as if you want the
fully qualified filename
3 - In the variable mappings area, Add a new user variable named
"FileName"; it should appear as User::FileName. Click OK & you're done
with the loop container
4 - Now go modify the variable properties (View, Other Windows,
Variables). Highlight the variable created in #3 and press F4 to get
the properties window.
5 - Change "EvaluateAsExpression" to True
6 - Change the "Expression" property to be your SQL Statement, but now
you're including your variable (notice the single quotes around your
variable):
"INSERT test SELECT '" + @.[User::FileName] + "'"
7 - Now add an "Execute SQL Task" to your loop container. Choose your
database. Change the SQL SourceType to be Variable.
8 - Choose your variable, which has now been filled with your SQL
statement.
9 - test out by debugging & then select the data from your table
SELECT * FROM test
Your next step - you mentioned it was to copy the files from the
original location to a new location using the table to loop. You can
skip all of the above madness, by just using the file system task, and
copy the entire contents of directory #1 to directory #2.
---
I'm wondering if this can be done (I've had no luck so far):
I would like to loop thru a set of files in a directory, place the file
name
into a variable, then insert the file name as a record into a table.
I've gotten the for each (file) loop set up and working fine, placing
the
file name into a user variable, but can't figure out how to use the
value of
the variable to insert it into a table.
The next step would be to loop thru the records in the table retrieving
each
file name into a variable to use in another for each file loop to copy
the
file to another location.
The purpose of the entire exercise is to put file names into two tables
for
log backups. I'm backing up log files to a local directory, and want to
copy
them to a network share. Since I only want to copy newly backed up
files, I
would like to go thru all the files on the local drive, write them to a
table, go thru all the previously copied files on the network share,
write
the names to another table, join the two tables to find which files do
not
exist on the network share and copy them to it.
There may be a better way to do this, but I've not figured it out so
far.
Thanks for any assistance with this.
TomT|||Thanks Corey - that's the direction I wanted to go with this (getting the
variable mapped properly in the sql statement.
There are a couple of reasons I'm going about things in this way. Since I'm
doing log backups every hour locally, and then copying these files over to
another system, I want to only copy over the most recent file. The other
(network) system should also have copies of previously copied logs. I wanted
to keep the i/o down, and only copy over the latest backup.
Secondly, I wanted to do this as a way to get more familiar with SSIS. I
think all I need to do really is just get the name of the most recent backup
log file, but I don't know how to get that info via SSIS.
So, e.g., say I've got two days worth of log backups on the local system,
then copy these over (say manually just for now) to a network system. Then,
the next time a log backup takes place on the local system, I want to copy
JUST that one over to the network system. If I could just identify that
particular (most recent) log backup's file name, I'd be set.
Again, this is just as much to learn SSIS as anything else right now.
Thanks for your help
"CoreyB" wrote:
> TomT - it sounds like everyone has an opinion! Here is a way to do
> what you want with SSIS. Then see below that for my opinion :).
> 1 - Open your new SSIS project, and add a Foreach Loop container
> 2 - In the properties, make it a Foreach File Enumerator, and choose
> the folder you want. Set the other properties, such as if you want the
> fully qualified filename
> 3 - In the variable mappings area, Add a new user variable named
> "FileName"; it should appear as User::FileName. Click OK & you're done
> with the loop container
> 4 - Now go modify the variable properties (View, Other Windows,
> Variables). Highlight the variable created in #3 and press F4 to get
> the properties window.
> 5 - Change "EvaluateAsExpression" to True
> 6 - Change the "Expression" property to be your SQL Statement, but now
> you're including your variable (notice the single quotes around your
> variable):
> "INSERT test SELECT '" + @.[User::FileName] + "'"
> 7 - Now add an "Execute SQL Task" to your loop container. Choose your
> database. Change the SQL SourceType to be Variable.
> 8 - Choose your variable, which has now been filled with your SQL
> statement.
> 9 - test out by debugging & then select the data from your table
> SELECT * FROM test
>
> Your next step - you mentioned it was to copy the files from the
> original location to a new location using the table to loop. You can
> skip all of the above madness, by just using the file system task, and
> copy the entire contents of directory #1 to directory #2.
>
> ---
> I'm wondering if this can be done (I've had no luck so far):
> I would like to loop thru a set of files in a directory, place the file
> name
> into a variable, then insert the file name as a record into a table.
> I've gotten the for each (file) loop set up and working fine, placing
> the
> file name into a user variable, but can't figure out how to use the
> value of
> the variable to insert it into a table.
> The next step would be to loop thru the records in the table retrieving
> each
> file name into a variable to use in another for each file loop to copy
> the
> file to another location.
> The purpose of the entire exercise is to put file names into two tables
> for
> log backups. I'm backing up log files to a local directory, and want to
> copy
> them to a network share. Since I only want to copy newly backed up
> files, I
> would like to go thru all the files on the local drive, write them to a
> table, go thru all the previously copied files on the network share,
> write
> the names to another table, join the two tables to find which files do
> not
> exist on the network share and copy them to it.
> There may be a better way to do this, but I've not figured it out so
> far.
> Thanks for any assistance with this.
> TomT
>|||Sounds good - good luck!
TomT wrote:
> Thanks Corey - that's the direction I wanted to go with this (getting the
> variable mapped properly in the sql statement.
> There are a couple of reasons I'm going about things in this way. Since I'm
> doing log backups every hour locally, and then copying these files over to
> another system, I want to only copy over the most recent file. The other
> (network) system should also have copies of previously copied logs. I wanted
> to keep the i/o down, and only copy over the latest backup.
> Secondly, I wanted to do this as a way to get more familiar with SSIS. I
> think all I need to do really is just get the name of the most recent backup
> log file, but I don't know how to get that info via SSIS.
> So, e.g., say I've got two days worth of log backups on the local system,
> then copy these over (say manually just for now) to a network system. Then,
> the next time a log backup takes place on the local system, I want to copy
> JUST that one over to the network system. If I could just identify that
> particular (most recent) log backup's file name, I'd be set.
> Again, this is just as much to learn SSIS as anything else right now.
> Thanks for your help
> "CoreyB" wrote:
> > TomT - it sounds like everyone has an opinion! Here is a way to do
> > what you want with SSIS. Then see below that for my opinion :).
> >
> > 1 - Open your new SSIS project, and add a Foreach Loop container
> > 2 - In the properties, make it a Foreach File Enumerator, and choose
> > the folder you want. Set the other properties, such as if you want the
> > fully qualified filename
> > 3 - In the variable mappings area, Add a new user variable named
> > "FileName"; it should appear as User::FileName. Click OK & you're done
> > with the loop container
> > 4 - Now go modify the variable properties (View, Other Windows,
> > Variables). Highlight the variable created in #3 and press F4 to get
> > the properties window.
> > 5 - Change "EvaluateAsExpression" to True
> > 6 - Change the "Expression" property to be your SQL Statement, but now
> > you're including your variable (notice the single quotes around your
> > variable):
> >
> > "INSERT test SELECT '" + @.[User::FileName] + "'"
> >
> > 7 - Now add an "Execute SQL Task" to your loop container. Choose your
> > database. Change the SQL SourceType to be Variable.
> > 8 - Choose your variable, which has now been filled with your SQL
> > statement.
> > 9 - test out by debugging & then select the data from your table
> >
> > SELECT * FROM test
> >
> >
> > Your next step - you mentioned it was to copy the files from the
> > original location to a new location using the table to loop. You can
> > skip all of the above madness, by just using the file system task, and
> > copy the entire contents of directory #1 to directory #2.
> >
> >
> > ---
> >
> > I'm wondering if this can be done (I've had no luck so far):
> >
> > I would like to loop thru a set of files in a directory, place the file
> > name
> > into a variable, then insert the file name as a record into a table.
> >
> > I've gotten the for each (file) loop set up and working fine, placing
> > the
> > file name into a user variable, but can't figure out how to use the
> > value of
> > the variable to insert it into a table.
> >
> > The next step would be to loop thru the records in the table retrieving
> > each
> > file name into a variable to use in another for each file loop to copy
> > the
> > file to another location.
> >
> > The purpose of the entire exercise is to put file names into two tables
> > for
> > log backups. I'm backing up log files to a local directory, and want to
> > copy
> > them to a network share. Since I only want to copy newly backed up
> > files, I
> > would like to go thru all the files on the local drive, write them to a
> > table, go thru all the previously copied files on the network share,
> > write
> > the names to another table, join the two tables to find which files do
> > not
> > exist on the network share and copy them to it.
> >
> > There may be a better way to do this, but I've not figured it out so
> > far.
> >
> > Thanks for any assistance with this.
> >
> > TomT
> >
> >

Wednesday, March 28, 2012

Intermittent Error! Help!

Intermittent Error! Help!

Intermittent Error! Help!

Intermittent Error! Help!

Friday, February 24, 2012

Integration Services For Each Loop Question

Integration Services For Each Loop Question

Integrated Security

Blog Archive

About Me