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, global variable, test vs live?

Are global variable parameters in a DTS package specific to "only" that one package and do not effect other packages?

I need to add a variable to a test package but am worried that if I add this parameter it might affect the live production Table. There are two different servers on different sides of the firewall, there are two different databases and there are two tables but with different names, and there are DTS packages but they reference different tables in the query code.

So is it ok to add the global variable which would allow this DTS to delete recent records from the "test" version and it wont do anything to my live version?By the way I did check Microsoft's website for more details on DTS and variables. It looks as thought they're saying it is specific to only that dts, table and database. But it would be nice to confirm this with someone out there more experienced.

http://msdn2.microsoft.com/en-us/library/aa933470(SQL.80).aspxsqlsql

DTS package with parameters

Hi,
I want to create DTS package which can accept a parameter and export / import data which match the parameter value. is it possible? if yes , can you please tell me the steps.
thanks in advance.
Satya - do not refer me to SQLDTS.com , not much help there .
RamI am not clear on what you are trying to do...

What are the source and destination connections in both the import and export scenario.

Are you trying to conditionally import or export data based on some Parameter? How are you executing the package?

DTS packages can take input parameters and return paramters via Package Variables (inner and outer). You can use an Execute package task to pass parameters to a nested DTS package which contains your logic for import or export. Depending on the complexity of your process you could create one master package (to manage the process) and two "child" packages: one for Import and one for export.

See books online for more info on the Execute Package Task...

CPNsqlsql

DTS Package with Dynamic Properties running Analysis Services Processing

Hi,

I wasn't sure of the location of this as it spans quite a few areas.

I have created a DTS package with Dynamic Parameters. The package is going to process the Dimensions of a cube therefore I have a dynamic property :

DimFolder -> String -> myServer\myCube\DimFolder

Which sets the TreeKey of the Analysis Services Processing Task to set the DimFolder to the correct server.

When I click on the parameters task and run is it works fine. If I then click on all the individual tasks within the DTS they all work fine as well.

However, when I click the play button to run the whole package is one go, I get

"Need to run the object to perform this operations. Code execution exception: EXCEPTION_ACCESS_VIOLATION".

I can't understand how this can happen as clicking each individual task and running each individual task works fine.

Any suggestions...

Jayne

Hi Jayne,

You may be encountering the problem described below, so try setting the Workflow Poperties of the Dynamic Properties task to "Execute on Main Thread":

http://support.microsoft.com/default.aspx?scid=kb;en-us;282966

>>

DTS Package Fails with Dynamic Properties Task and OLAP Processing Task

View products that this article applies to.

Article ID

:

282966

Last Review

:

January 9, 2004

Revision

:

2.1

This article was previously published under Q282966

SYMPTOMS

When you process a Data Transformation Services (DTS) package that contains both a Dynamic Properties task and an OLAP Processing task, the DTS package may fail during execution of the Dynamic Properties task with the following error message:

1 task(s) failed during execution

When you double-click the failed Dynamic Properties Task, the following error dialog is displayed:

Code execution exception: EXCEPTION_ACCESS_VIOLATION

CAUSE

The Dynamic Property task is used to set properties on other tasks, and the Analysis Services/OLAP processing task is a Single Threaded Apartment (STA) component. When the Dynamic Property task tries to set the properties on the STA based component, the Dynamic Property task must run on the main thread to set the properties of the STA based task.

>>

|||

Also you may find that you have to use a scripting task rather than the dynamic properties task. Because of some binding issues I found when I was writing SSABI that I had to use a script task and walk the object model by-hand that the dynamic properties task returned errors , even when it was set to run on the main thread. You mileage may vary, but if the main thread setting doesn't work, try using a script task.

_-_-_ Dave