dts parameters, more values in one parameter

hy all,

i'm using the object transform data task from a server (where i'm not dbo) to another server (where i'm dbo).

i'm getting data from a table. i need to select just some records from this table. i need to use a query like this one below...

select * from {table_name} where operationedate in ('20070101', '20070205', '20060524')

... cause everytime i run the dts the operationdate field must be filtered on different date.

so i tried to use the parameter from a global variables. i've tried lots of things but everytime i failed.

i used to try:

select * from {table_name} where operationedate in (?)

but it doesnt work!

any one can understand what i try to explain and even help me?

bye

nicola

Have you tried using a SSIS variable with an expression to build query dinamically?

There are a lot of post about it. You would need the set the EvaluateAsExpression porperty of the variable as TRUE and then build the expression. The expression will use another variable that has the coma delimited list of values with the dates you care about.

|||

Build two variables: varInClause (string) & varSQLStatement (string)

For the value of varInClause, you can type your dates including the quotes and commas: '20070101','20070205',...

Set EvaluateAsExpresstion to true on the varSQLStatement variable. In the expressions box, type this as your expression:

"select * from table_name where operationdate in (" + @.{User::varInClause} + ")"

Then, use the variable, varSQLStatement, in your OLE DB source.

|||

thank you very much for answered so soon

but maybe i'm still in trouble

i forgot you to say i'm working in SQL Server 2000

i think that is possible to use SSIS only in SQL Server 2005

i'm right or not?

bye

nicola

|||Oh, well, that is a very important piece of information because this is not a DTS forum. If you're working with DTS, you need to post that question on the DTS newsgroup.

http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg|||

thank you very much

i'm sorry for time you loose with my problem

bye

nicola

Dts package that imports difference

Assuming you are using an Execute SQL task, you can use a script like the
one below:
SET IDENTITY_INSERT Database2.dbo.MyTable ON
GO
INSERT INTO Database2.dbo.MyTable
(
ID,
MyData
)
SELECT
ID,
MyData
FROM Database1.dbo.MyTable AS t1
WHERE NOT EXISTS
(
SELECT *
FROM Database2.dbo.MyTable AS t2
WHERE t2.ID = t1.ID
)
Hope this helps.
Dan Guzman
SQL Server MVP
"italic" <hugur@.hotmail.com> wrote in message
news:%23M7cQ36kHHA.4624@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have to server and two different database on them. I want to import one
> table from database1 to database2. But I want to import only difference
> between two table.I want to also keep identity column value. I created a
> dts package but I don't know how can I put a condition like "where myid
> not in(Select myid from database2.table)" and where can I put Set
> IDENTITY_INSERT mytable ON?
> Thanks in advance,
>
>
in DTS,
first you have to get the lastet ID (select max(id) from destination)
then you have to dynamically create the select statement (Select * from
source where id > maxid)
(or you have to use a parametrized query where the parameter will be the ID)
and finally load the data.
"italic" <hugur@.hotmail.com> wrote in message
news:Ogw1D77kHHA.2272@.TK2MSFTNGP02.phx.gbl...
>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
>
|||>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
You are correct. It was unclear to me from your message that the databases
were on different servers.
If you have a lot of changes, I suggest you copy the data from the source
table into a staging table on the target databse server and then use an
Execute SQL task.
Hope this helps.
Dan Guzman
SQL Server MVP
"italic" <hugur@.hotmail.com> wrote in message
news:Ogw1D77kHHA.2272@.TK2MSFTNGP02.phx.gbl...
>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
>
|||Hi
"italic" wrote:

> I don't think you can connect from "Execute SQL task" to both server. You
> need to select connection(server) first. Am I wrong?
> Thanks
You can create a linked server and reference that in your query, or as you
have a second connection to the destination server you could use a query
(task) to retrieve the highest identity from the destination server then use
that in a global variable as the start of the information retrieved from the
second server.
See http://www.sqldts.com/280.aspx
http://www.sqldts.com/205.aspx
The second method would not necessarily be transactionally consistent,
therefore you should not be changing the destination table when this is
running.
John
|||Hi
"italic" wrote:

> Linked Server is not allowed. I can try the second way.
> Thanks
>
You would need a 4 part name! What error do you get?
John
sqlsql