Can I use a variable to hold the target table name.

We need to pull from a table that is named tablename_mmddyy and populate a table with the same format tablename_mmddyy. The date will be different every month so I want to be able to build the tablenames every month. Is there a way to do this in SSIS? Thank you.Yes, using expressions in your variables, you can do what you want.

Create a variable and then for its properties, set EvaluateAsExpression to True and then build your expression accordingly in the Expression box.

Then in your OLE DB source/destination, you can use that variable as the table. Just know that you'll want to probably set DelayValidation to True on your connection managers when you do this.

There are plenty of examples in this forum if you search for them on how to build expressions, if you need.|||I found the option for this under Data Access Mode but we need to do the same thing for the database name which will be in a similar format e.g. databasename_mmddyy and the source db name will change every month.|||Same thing. You can use expressions on your connection managers to dynamically create the current connection string.|||The same principals can be applied to a connection manager (i.e. database name of database server). You can use an expression to build the connection string from a variable in the properties window of the connection manager.|||Any examples of this?|||

agentf1 wrote:

Any examples of this?

In the ConnectionString expression box for a connection manager. Also it can't hurt to set DelayValidation to True.

"Data Source=" + @.[User::YourDatabaseNameVariable] +";Initial Catalog=Report;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"|||What goes in the database name field? I am assuming that it gets overridden by the expression.|||

agentf1 wrote:

What goes in the database name field? I am assuming that it gets overridden by the expression.

The ConnectionString will override any other parameters.|||

agentf1 wrote:

Any examples of this?

http://search.live.com/results.aspx?FORM=QBJK&q1=macro%3Ajamiet.ssis&q=expressions

-JT

|||This seems to be changing both of my OLE DB Connections. Is there a way one can be static and the other updated by the variable? It also appears that Data Source controls the server and Catalog is the database name.|||

agentf1 wrote:

This seems to be changing both of my OLE DB Connections. Is there a way one can be static and the other updated by the variable?

That's impossible (unless one of us has misunderstood). An expression on the ConnectionString property of a connection manager only changes that connection manager.

agentf1 wrote:

It also appears that Data Source controls the server and Catalog is the database name.

Correct.

-Jamie

|||

Jamie Thomson wrote:

agentf1 wrote:

This seems to be changing both of my OLE DB Connections. Is there a way one can be static and the other updated by the variable?

That's impossible (unless one of us has misunderstood). An expression on the ConnectionString property of a connection manager only changes that connection manager.

agentf1 wrote:

It also appears that Data Source controls the server and Catalog is the database name.

Correct.

-Jamie

My bad, I had the expression on both connections. I am OK, thanks for all the help, worked beautifully.

Can I pull the data to an existing table?

I am building RDA process between SQL server and SQL server CE. Since when we use the method 'SqlCeRemoteDataAccess.pull()', the data will be pulled down from SQL server to CE server and a new table is created with some addtional columns. As I only intend to pull the data and never push it back to SQL server, if I pull the data down for the second time, there will be an error saying that table is already existing. I wonder whether I can pull the data to an existing table everytime I do the RDA?
Thanks,
JustinRDA doesn't keep track of incremental changes from the server. If you want to update the client with new changes from the server, RDA requires that the table at the client be dropped and re-created via pull.

http://msdn2.microsoft.com/en-us/library/ms240401(en-US,SQL.90).aspx

can i pull data from a sql server express 2005 db onto my ce device?

Will my 'smart device application' designed with VS.Net 2005 allow my ce
device to pull data from a SQL Server EXPRESS 2005 database residing on the
server computer? Or must I be using the full version of SQL Server 2005 to
accomplish this movement of data between my ce device and my server
computer?
Thanks!
hi Jesse,
Jesse Aufiero wrote:
> Will my 'smart device application' designed with VS.Net 2005 allow my
> ce device to pull data from a SQL Server EXPRESS 2005 database
> residing on the server computer? Or must I be using the full version
> of SQL Server 2005 to accomplish this movement of data between my ce
> device and my server computer?
>
i you mean, for instance, querying a SQLExpress instance via (say) wi-fi
connected CE devices, then yes.. just verify the SQLExpress instances allows
remote connections..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||thanks, i need to know specifically if a vs.net 2005 smart device
application can issue an RDA pull method (like i was able to do with vs.net
2003) to a sql server express 2005 database residing on a remote server
computer.
i'm actually hoping that someone can answer the question or point me to an
article so that i don't have to go through extensive r&d to determine if it
can be done. normally i would be more than happy to do this r&d, however,
to answer this seemingly simple question would require that i purchase and
install vs.net 2005, install sql server express 2005, learn how to use them,
write test applications, install the test applications to devices, etc.
before i go down this expensive and arduous road, i figured maybe someone
might know the answer off the top of their head. ah, the beauty of
newsgroups!
thank you
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:42i54eF1jj877U1@.individual.net...
> hi Jesse,
> Jesse Aufiero wrote:
> i you mean, for instance, querying a SQLExpress instance via (say) wi-fi
> connected CE devices, then yes.. just verify the SQLExpress instances
> allows remote connections..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

can I pull a sql 2000 datafile into sql express ?

Can I do this with a sql 2000 database
http://www.cryer.co.uk/brian/sqlserver/howto_movedatabasefile.htm

then attach sql express to this mdf ?

hi,

BitShift wrote:

Can I do this with a sql 2000 database
http://www.cryer.co.uk/brian/sqlserver/howto_movedatabasefile.htm

then attach sql express to this mdf ?

no, but you can do the contrary, attach the mdf (+ ldf) to SQLExpress

regards

|||So whats the best way to export tables and data from a sql 2K db and import /recreate it in sqlexpress ? I have a sql2k database on a server and id like to pull it down and work against it locally.|||

hi,

just perform a backup->copy->restore or a sp_detach_db->copy->CREATE DATABASE xx FOR ATTACH (sp_attach_db has been deprecated in SQL Server 2005, but still usable), http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_1up1.asp

then you have to modify the compatibility level of the attached database to 9, http://msdn2.microsoft.com/en-us/library/ms178653.aspx, modify the database owner to a valid user, http://msdn2.microsoft.com/en-us/library/ms178630.aspx and finally regenerate database user's tables statistics with full scan, http://msdn2.microsoft.com/en-us/library/ms187348.aspx, as SQL Server 2005 implements different algorithm on them..

regards

|||i did the first step.... i tried to attach a database... but then poof!!! i had a message in my console the msg 601 ''could not continue scan with nolock due to data movement''

i did install the recommended fix exe, the 2000 sP4.. but it still appears.

please help me out, i can't attach an existing sql2k database to my dev express.. wat am i gonna do? im desperate.
|||

hi,

actually I've only seen this exception related to DML operations and not with RESTORE tasks..

but let's wait for Mike to jump in...

regards

|||i still dont get it... how do i restore a database that is backed up from sql2k to sql express?

the sql express that i am talking about is the one that is included in the Microsoft Visual Web Developer 2005...

i don't see an option that i can restore a certain backup database. is my only way to restore is to access the tui console > sqlcmd -S .\sqlexpress?

how, how?!?! im so noob.

tnx|||hey! i got it... ^_^

i used

BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'
GO


from: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_25rm.asp

thanx PO!!

God bless

can I pull a sql 2000 datafile into sql express ?

Can I do this with a sql 2000 database
http://www.cryer.co.uk/brian/sqlserver/howto_movedatabasefile.htm

then attach sql express to this mdf ?

hi,

BitShift wrote:

Can I do this with a sql 2000 database
http://www.cryer.co.uk/brian/sqlserver/howto_movedatabasefile.htm

then attach sql express to this mdf ?

no, but you can do the contrary, attach the mdf (+ ldf) to SQLExpress

regards

|||So whats the best way to export tables and data from a sql 2K db and import /recreate it in sqlexpress ? I have a sql2k database on a server and id like to pull it down and work against it locally.|||

hi,

just perform a backup->copy->restore or a sp_detach_db->copy->CREATE DATABASE xx FOR ATTACH (sp_attach_db has been deprecated in SQL Server 2005, but still usable), http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_1up1.asp

then you have to modify the compatibility level of the attached database to 9, http://msdn2.microsoft.com/en-us/library/ms178653.aspx, modify the database owner to a valid user, http://msdn2.microsoft.com/en-us/library/ms178630.aspx and finally regenerate database user's tables statistics with full scan, http://msdn2.microsoft.com/en-us/library/ms187348.aspx, as SQL Server 2005 implements different algorithm on them..

regards

|||i did the first step.... i tried to attach a database... but then poof!!! i had a message in my console the msg 601 ''could not continue scan with nolock due to data movement''

i did install the recommended fix exe, the 2000 sP4.. but it still appears.

please help me out, i can't attach an existing sql2k database to my dev express.. wat am i gonna do? im desperate.
|||

hi,

actually I've only seen this exception related to DML operations and not with RESTORE tasks..

but let's wait for Mike to jump in...

regards

|||i still dont get it... how do i restore a database that is backed up from sql2k to sql express?

the sql express that i am talking about is the one that is included in the Microsoft Visual Web Developer 2005...

i don't see an option that i can restore a certain backup database. is my only way to restore is to access the tui console > sqlcmd -S .\sqlexpress?

how, how?!?! im so noob.

tnx|||hey! i got it... ^_^

i used

BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'
GO


from: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_25rm.asp

thanx PO!!

God bless