Reading a file name that changes

Brand new to SSIS so bear with me, if something is obvious.

I want to be able to read a file from a certain directory. But the file name changes every day. So today its File20061203 tomorrow File20061304 or the next day it could be FileNB4434. The format in the file will always be the same though. I just want for a user to be able to drop a file in a directory and the package pick it up once a day.

Would I have to to create a script task or could I use a variable. I have been trying to use the variable but have not been able to get them to work. This calls for only looking for 1 text file in a folder but any additional links that show some good variable examples would be appreciated. One where only part of the variable changes File(Variable)Division.txt

Thanks in advance

Try using the ForEach Loop Container with a file enumerator. That will store the file name in a variable you specify. Set the connect string on the File connection manager by using an expression (@.User:<name of your variable>).

Normally the Loop container is used when you have a number of files, but it works just fine with one.

Read only permissions for report writer

I have an msde database (SQL Server 2000) with an web front end. I would
like to create an MS Access Project to allow certain users to create ad hoc
reports.
I have created a new login named Reporter and connected to the msde database
using this user. This user has dbreadonly permissions.
This works to a certain extent in that new reports can be created based on
existing tables. However, I would like the user to be able to create their
own select queries and cannot find a way to manage this. I recieve a
message saying the user needs 'Create Procedure' rights.
Can anyone tell me how I can let Reporter create their own queries but not
add/edit/delete any existing data?
Many thanks
June
hi June,
June Macleod wrote:
> I have an msde database (SQL Server 2000) with an web front end. I
> would like to create an MS Access Project to allow certain users to
> create ad hoc reports.
> I have created a new login named Reporter and connected to the msde
> database using this user. This user has dbreadonly permissions.
> This works to a certain extent in that new reports can be created
> based on existing tables. However, I would like the user to be able
> to create their own select queries and cannot find a way to manage
> this. I recieve a message saying the user needs 'Create Procedure'
> rights.
> Can anyone tell me how I can let Reporter create their own queries
> but not add/edit/delete any existing data?
> Many thanks
> June
if you are required to allow "CREATE PROC" statement you have to grant
membership to ddladmin database role but, with such a permission, Reporter
can even create new tables and of course access/modify their data..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||better,
if you only are required to create procedures, you can grant that specific
permission to Reporte user like
GRANT CREATE PROCEDURE TO [Reporter]
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Read MS SQL 2000 backup file without restoring the database

I want to read certain data from the backup file of MS SQL 2000 without restoring the database backup file. Based on the data selected I will manage the further functionality such as restoration of the backup file.
Is it possible to read some data from MS SQL 2000 backup file without restoring? If yes then please let me know how?
I don't think you can read the data without restoring the backup file...................also you need to restore the entire backup file and after restoring it you can choose the data you need..........may be if you already have a db take a backup before restoring from the backup (overwrite) and then choose your data and if needed you can export it and then once again restore the original backup of the db...........|||

Just to be clear, you definitely can't directly read from a backup file.

|||hmmmm.....Ok, is it possible that I can restore only one table from my MS SQL backup file to some other temp db? Which will help me to get the required value from that table.
|||you can restore your backup file as a entire db and then export the required table to another db in the same server or in another server ...
|||

No - you can only restore a complete database. Restoring a single table is a commonly requested feature - it's not included in SQL Server 2008 but may make it into the following version.

Thanks

Read MS SQL 2000 backup file without restoring the database

I want to read certain data from the backup file of MS SQL 2000 without restoring the database backup file. Based on the data selected I will manage the further functionality such as restoration of the backup file.
Is it possible to read some data from MS SQL 2000 backup file without restoring? If yes then please let me know how?
I don't think you can read the data without restoring the backup file...................also you need to restore the entire backup file and after restoring it you can choose the data you need..........may be if you already have a db take a backup before restoring from the backup (overwrite) and then choose your data and if needed you can export it and then once again restore the original backup of the db...........|||

Just to be clear, you definitely can't directly read from a backup file.

|||hmmmm.....Ok, is it possible that I can restore only one table from my MS SQL backup file to some other temp db? Which will help me to get the required value from that table.
|||you can restore your backup file as a entire db and then export the required table to another db in the same server or in another server ...
|||

No - you can only restore a complete database. Restoring a single table is a commonly requested feature - it's not included in SQL Server 2008 but may make it into the following version.

Thanks

sql

RDA Pull with existing table

Hi Forum,

I am currently implementing RDA Pulls and Pushes. Both worked fine for me, except when I try to pull a certain table twice. I read that in order to pull the table a second time I must drop it on the client.

My original approach was to use a select statement with a where criterium in the pull statement (e.g. SELECT * from tblPhonebook where Pulled = 0), then set Pulled to 1 and pull again later.

My understanding of RDA was that I use the where criterium to filter the data at the server side and simply append that data at the client side.

Can I simply append data in some way or do I really have to drop the table on the client side every time I pull?

Regards
Tobias

you have to drop the table before the pull each time. some people solve this by introducing an inbox/outbox concept in your SQLCE/Mobile db. You RDA pull to the inbox table. you copy those rows over to your OLTP table that your mobile app intends to work against (which has same schema as inbox, except for the rda tracking columns). you post back the changes you made to the OLTP table to the inbox table (which now becomes an outbox). you push back to server, then drop the inbox/outbox table and repeat the process. burden to manage synchronization between the oltp table and inbox/outbox is on you.

this situation is really what merge replication is for.

-Darren

|||Thanks Darren,

I guessed it would be that way...

Regards
Tobias|||

I've been using the RDA pull successfully for years now. It's always been a problem that during or right before an RDA Pull there's a vulnerable period where the local table has been dropped but the RDA pulled table to replace it is not completely transfered. So if the connection breaks the local table is pretty much hosed. I read the above with interest and have often thought this would be the way to go. Any good links to a more detailed explanation of this technique? Anyone else out there using this successfully.

Another idea is to use the Select @.@.DBTS on SQLServer to get the last update deal and then bring back updates and news only without RDA but this seems convoluted....

RDA Pull with existing table

Hi Forum,

I am currently implementing RDA Pulls and Pushes. Both worked fine for me, except when I try to pull a certain table twice. I read that in order to pull the table a second time I must drop it on the client.

My original approach was to use a select statement with a where criterium in the pull statement (e.g. SELECT * from tblPhonebook where Pulled = 0), then set Pulled to 1 and pull again later.

My understanding of RDA was that I use the where criterium to filter the data at the server side and simply append that data at the client side.

Can I simply append data in some way or do I really have to drop the table on the client side every time I pull?

Regards
Tobias

you have to drop the table before the pull each time. some people solve this by introducing an inbox/outbox concept in your SQLCE/Mobile db. You RDA pull to the inbox table. you copy those rows over to your OLTP table that your mobile app intends to work against (which has same schema as inbox, except for the rda tracking columns). you post back the changes you made to the OLTP table to the inbox table (which now becomes an outbox). you push back to server, then drop the inbox/outbox table and repeat the process. burden to manage synchronization between the oltp table and inbox/outbox is on you.

this situation is really what merge replication is for.

-Darren

|||Thanks Darren,

I guessed it would be that way...

Regards
Tobias|||

I've been using the RDA pull successfully for years now. It's always been a problem that during or right before an RDA Pull there's a vulnerable period where the local table has been dropped but the RDA pulled table to replace it is not completely transfered. So if the connection breaks the local table is pretty much hosed. I read the above with interest and have often thought this would be the way to go. Any good links to a more detailed explanation of this technique? Anyone else out there using this successfully.

Another idea is to use the Select @.@.DBTS on SQLServer to get the last update deal and then bring back updates and news only without RDA but this seems convoluted....