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
> >
> >