Mapping Package Variables to a SQL Query in an OLEDB Source Component

Learning how to use SSIS...

I have a data flow that uses an OLEDB Source Component to read data from a table. The data access mode is SQL Command. The SQL Command is:

select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare)

I wanted to add additional clauses to the where clause.

The problem is that I want to add to this SQL Command the ability to have it use a package variable that at the time of the package execution uses the variable value.

The package variable is called [User::Date_BeginningYesterday]

select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare) and record_modified < [User::Date_BeginningYesterday]

I have looked at various forum message and been through the BOL but seem to missing something to make this work properly.

http://msdn2.microsoft.com/en-us/library/ms139904.aspx

The article, is the closest I have (what I belive) come to finding a solution. I am sure the solution is so easy that it is staring me in the face and I just don't see it. Thank you for your assistance.

...cordell...

Not sure what your problem is; but the solution is to create a variable to hold your query, let's say [User::SQLStatement] and use as value your query. Then set EvaluateAsExpression property of the variable to true. In the expression property, create an expression that will be evaluate at run time where you concatenate your query with the [User::Date_BeginningYesterday] variable. Back in your OLE DB Component you need to choose 'SQL Statement from variable' and then choose [User::SQLStatement] from the list.

Notice that you need to cast the value of [User::Date_BeginningYesterday] to string in the expression builder before concatenating its value to the sql statement.

Rafael Salas

select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare) and record_modified < [User::Date_BeginningYesterday]

|||

The question that I have is: Can one embed a package variable into a sql statement while selecting "SQL Statement" from the data access mode. If so how would would go about that?

...cordell...

|||

The short answer is no. you cannot reference a SSIS variable directly in your sql statement. You need to use '?' and then use the parameter mapping in your OLE DB source OR, to concatenate it within a second varibale as I described in the previous post.

Rafael Salas

sql

many mistakes, one big mess

Hi there. I'm learning about MSDE, .adp and how to handle the files in between.
I have remote access to the server where the Database is, so this morning
I've detached the database, replace it with a newer version and re-attached...
I forgot to change the attribute and I re-attached as "read only". First big
mistake. Somebody tried to update a record and the project was "hanging" so
they press Ctr+Alt+Del and stop the Access project.
The database shows there is still one user connected
Trying to fix this, I've stopped the server... thinking this will disconnect
the user... nope.
Please help me to fix this mess and learn few big lessons (like never attach
a read only db)
How can I connect the Instance again? The service manager is not letting me
re-connect, everything is "disabled"
How can I disconnect the user so I can detach the read only database and
re-attache the good one?
Any help would be more than appreciated,
gaba
hi,
gaba wrote:
> Hi there. I'm learning about MSDE, .adp and how to handle the files
> in between. I have remote access to the server where the Database is,
> so this morning
> I've detached the database, replace it with a newer version and
> re-attached... I forgot to change the attribute and I re-attached as
> "read only". First big mistake. Somebody tried to update a record and
> the project was "hanging" so they press Ctr+Alt+Del and stop the
> Access project.
> The database shows there is still one user connected
> Trying to fix this, I've stopped the server... thinking this will
> disconnect the user... nope.
> Please help me to fix this mess and learn few big lessons (like never
> attach
> a read only db)
> How can I connect the Instance again? The service manager is not
> letting me re-connect, everything is "disabled"
> How can I disconnect the user so I can detach the read only database
> and re-attache the good one?
> Any help would be more than appreciated,
if your MSDE instance is running and you want to see/kill a certain user,
you can run, on oSql.exe the
EXEC sp_who
system stored procedure to identify who you want to eventually kill, and
then exeute the
KILL nr#
statement, where nr# states for the actual related spid of the "user" you
want to kill..
Andrea Montanari
http://www.asql.biz/DbaMgr.shtm
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
Thanks for your response. The MSDE instance is not running, acually is
trying to stop. Under SQL Server Service Manager, SQL server for this
instance says "stopping". It shows it is still connected to one user. I've
tried to delete the db, no luck.
Please advise
gaba
"Andrea Montanari" wrote:

> hi,
> gaba wrote:
> if your MSDE instance is running and you want to see/kill a certain user,
> you can run, on oSql.exe the
> EXEC sp_who
> system stored procedure to identify who you want to eventually kill, and
> then exeute the
> KILL nr#
> statement, where nr# states for the actual related spid of the "user" you
> want to kill..
> --
> Andrea Montanari
> http://www.asql.biz/DbaMgr.shtm
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||Andrea,
Is there any solution to this mess or should I uninstalled the instance and
re-install it? right now the server manager is greyed out and I can't do
anything. I'm using remote access to the server and have to let everybody
know if I'm going to re-start the server.
Thanks.
gaba
"Andrea Montanari" wrote:

> hi,
> gaba wrote:
> if your MSDE instance is running and you want to see/kill a certain user,
> you can run, on oSql.exe the
> EXEC sp_who
> system stored procedure to identify who you want to eventually kill, and
> then exeute the
> KILL nr#
> statement, where nr# states for the actual related spid of the "user" you
> want to kill..
> --
> Andrea Montanari
> http://www.asql.biz/DbaMgr.shtm
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi,
gaba wrote:
> Andrea,
> Is there any solution to this mess or should I uninstalled the
> instance and re-install it? right now the server manager is greyed
> out and I can't do anything. I'm using remote access to the server
> and have to let everybody know if I'm going to re-start the server.
> Thanks.
actually I'm trying to figure out what's happening...
you have the server "stopping" but not able to complete the process "becouse
some user's still connected"...
actually this should not prevent the SQL Server service to stop at all...
ok.. before uninstalling...
try managing the service applet to start MSDE manually and not automatic..
shut down the machine.. possibly "softly", and then physically if it does
not work :D
once restarted MSDE should not be running.. delete the db files you are
interested with...
start MSDE manually... the database should be marked as corrupted or the
like..
drop the database via
DROP DATABASE xxxx
statement... you should be then ok with that.. repristinate the automatic
startup of the service...
Andrea Montanari
http://www.asql.biz/DbaMgr.shtm
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
please forgive my ignarance,
what do you mean by "shut down the machine possibly "softly"?
Thanks so much for your help
gaba
"Andrea Montanari" wrote:

> hi,
> gaba wrote:
> actually I'm trying to figure out what's happening...
> you have the server "stopping" but not able to complete the process "becouse
> some user's still connected"...
> actually this should not prevent the SQL Server service to stop at all...
> ok.. before uninstalling...
> try managing the service applet to start MSDE manually and not automatic..
> shut down the machine.. possibly "softly", and then physically if it does
> not work :D
> once restarted MSDE should not be running.. delete the db files you are
> interested with...
> start MSDE manually... the database should be marked as corrupted or the
> like..
> drop the database via
> DROP DATABASE xxxx
> statement... you should be then ok with that.. repristinate the automatic
> startup of the service...
> --
> Andrea Montanari
> http://www.asql.biz/DbaMgr.shtm
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi,
gaba wrote:
> Andrea,
> please forgive my ignarance,
> what do you mean by "shut down the machine possibly "softly"?
> Thanks so much for your help
I just meant using the "standard" shut down Windows features and not
pressing the pc's interrupt :D:D
Andrea Montanari
http://www.asql.biz/DbaMgr.shtm
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
THANK YOU. I re-started the server and drop the database, attach the good
one and we are back in business.
I've recreated my mistakes... oh Boy
I work on my cpu, test and then burn a copy of the project and database to a
cd, paste into server.
1) Didn't stop the SQL Server on my computer BEFORE copying files: 1 user
still connected
2) When pasting files from CD, didn't make them ARCHIVES, they were read only
3) Instead of disconnecting the user (Kill nr#) and drop corrupted db, I've
tried to stop the SQL Server.
Thanks so much for you help. I know one way of learning and to get
experience is by making mistakes and fixing them... but Oh Boy I really had a
BAD day: I've made one after the other. Maybe somebody else can learn too
from them.
I want to learn SQL Server and do it right, can you recomend any material or
book for Beginners? Thanks
gaba
|||hi,
gaba wrote:
> 1) Didn't stop the SQL Server on my computer BEFORE copying files: 1
> user still connected
another lesson, if I can help... please always DETACH dbs before copying
them ... detaching a database for later re-attach is the only proper and
supported way for doing that kind of operations, and stopping the server is
not needed...
ok. both way, detaching and stopping the server, should provide a proper db
closing... but stay the documented way :D:D

> I want to learn SQL Server and do it right, can you recomend any
> material or book for Beginners? Thanks
I'd recommend the SQL bible, "Inside SQL Server 2000",
http://www.amazon.com/exec/obidos/tg...books&n=507846 ,
not about Transact-SQL but the actual engines and related architecture...,
by Notre Dame SQL Server Kalen Delaney.. :D
Andrea Montanari
http://www.asql.biz/DbaMgr.shtm
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
Thanks a lot for your help and advise.
I'm using the DbaMGR2k, actually I'm reading the help files first ;)
Thanks for a great product!
I'll get the book tomorrow.
I'm sure we'll "talk" again,
gaba
sql

managing the transaction log

Hello!

I'm working with an SQL database that someone else has set up and this is a learning experience for me.

I understand what the transaction log is and a little about it.

What i would like to do is shrink it because it is full. If i use the wizard to truncate or shrink data it never seems to work. I have created a second log file but the server doesn't seem to use it. Increasing the log size does nothing also. DARN!

What is the best way to dump the old data?

thanks in advance?

RIMQ1 [i would like to do is shrink it because it is full]?

A1 Note: one cannot shrink a 'Full' log beyond an active VLF; moreover, one must either dump / back up the contents of a transaction log to a transaction log backup *.trn file (or truncate it) before DBCC ShrinkFile can shrink the file to any smaller size.
Frequently dumping / backing up your (production database) transaction logs to transaction log backup *.trn files will provide the means of point in time recoverability; and also keep the overall DB log size managable. (Typically, production user DBs should be using the Full backup recovery model.)

General production guidelines include:
i The use of DBCC ShrinkFile, (and / or enable autoshrink if appropriate).
ii Identify any long running transactions that may be filling up your DB Log rewrite them to be efficient.
iii Dump the DB transaction log to transaction log backup *.trn files as appropriate for the production environmen.t

DBCC ShrinkFile advantages:
* it is safe
* it may be safely used even if your DB has multiple log files (add several additional log files to your DB, then rigorously test your method)
* ordinary users may work in the DB while its files are being shrunk

Use MyDB
Go
DBCC ShrinkFile ([MyDB_Log], 1, TruncateOnly)
Go|||This was address a couple of weeks ago - check out the link:

link (http://dbforums.com/showthread.php?s=&threadid=546372)