DTS question

How do I use a global rowset variable in a SQL statement where a value should be IN ( ? )
where ? is mapped to the global rowset variableI have two different data sources.

I need to pull from source (1) to Souce (2).

The data pulled from souce(1) needs to be based on values in a table in source(2).

Select fields
from source(1)
where field1 in ( Table in source 2)

Is this possible with DTS?|||try this link
[BOL] Using Global Variables with DTS Packages
and

[BOL] Execute SQL Task look lower into the doc for "Populating a Single Global Variable with an Entire Rowset"|||I have the global variable (rowset) populated, but how do I use it in my where condition?

where field in ( ? ) I get an error when I map the parm to the global rowset variable.

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 problems

I am working on a DTSPackage and have had nothing but bad luck.

1) the query is below. I want to sub out @.GUID with a GlobalGUID variable I set using

dynamic property. All I get when I try to set

GUID = ?

instead of GUID = @.GUID

when I hit the Parameters button all I get is this error

MicrosoftOLE DBProvider for SQL Server

Error Description:Syntax error or access violation

What can be wrong here? If I go the package properties I can

see the GlobalVariable I created.

Thanks for any leads.

Bob

>>>>>>>>>>>>>>>>>>>>>>>>>>

DECLARE @.CurrentPeriod CHAR(4)
DECLARE @.GUID VARCHAR(50)

exec usp_Get_Period_UserPeriod @.CurrentPeriod OUTPUT

SELECT ProductCode AS [Code], ProductName AS [Short], ProductDescription AS [Long],
case CHARINDEX(',',SORT,1) when 1 then space(10) else LEFT(SORT,CHARINDEX(',',SORT,1)-1) end+SPACE(10) AS [Sort],
case when CHARINDEX(',,',SORT)>0 then space(10) else
SUBSTRING(SORT,CHARINDEX(',',SORT,1)+1,
CHARINDEX(',',SORT,CHARINDEX(',',SORT,1)+1)-CHARINDEX(',',SORT,1)-1) end +SPACE(10) AS [SortALN],
case RIGHT(SORT,1) when ',' then space(10) else
REVERSE(case CHARINDEX(',',REVERSE(SORT),1) when 1 then space(2) else LEFT(REVERSE(SORT),CHARINDEX(',',REVERSE(SORT),1)-1) end) end +SPACE(10) AS [SortALI]
FROM PRODUCTMASTER WHERE ProductCode IN
(SELECT PRODUCTCODE FROM DRPRODUCT WHERE GUID = @.GUID) AND PERIOD = @.CurrentPeriod ORDER BY SORT

Try the link below for a work around. Hope this helps.

http://www.sqldts.com/default.aspx?234

DTS Package - variable Table Name

Hi,

I have created a DTS Package which copies data from a Access Database table to a SQL Server table. The DTS Package is scheduled to execute every 15 minutes. Everyday the source table name will change. Is there a way to reset the source table name everyday without changing it through DTS wizard.

Thanks,

Vivek Gupta

Yes it is possible, you can try to read data table name from input parameters, and pass your parameters on each DTS run from you SQL Job agent.

Thanks

DTS Migration

I try to migrate DTS package to SSIS.

Within one DTS package AAA, it calls another DTS package BBB with inner variable.

I successfully migrate the BBB DTS package.

For my AAA package, I replace the DTS_Execute_Task_2000 with Execute Package task. But how do we pass the inner variable parameter ?

There is a Execute DTS2000 Package Task that allows you to do this.

-Jamie

|||Is there a way not to use DTS2000 Package ?|||

You could call dtsrun.exe from the Execute Process Task. Not sure why you'd want to tho.

-Jamie

DTS mapping global variable to column

Hi
I have doubt in DTS -i want 2 export text file into Database - which i can able to do it while running DTS it will prompt inputbox to enter the datetime of transferring which i should update in the datetime column of the table,which i will store this user input value in globalvariables- so how should i map to column.
i placed text object (source) and connection object and active x script object

thanks in advance
HrMay search SQL DTS (http://www.sqldts.com) website.

DTS Global Variables

Hello,

In the properties for a DTS package, I have a string variable. I need to execute an update statement based on this variable. I was going to use Execute SQL, but I didn't know now to get a value from the variables section.

I store the value there because I will be using dtsrun or the DTS library to execute the package, which I can change this value upon execution. How do I execute an update based on the value?

BrianAre you saying that you need to use this string variable in your WHERE clause? If so, you should be able to use a question mark (?) where you need to use that string value, then use the Parameters button to tell DTS which global variable to use for the parameter.

Terri|||Hey,

That was correct. THanks.

Brian|||Cool! I have found DTS to be one of the most powerful yet frustrating tools to use. Could they make it any more obtuse? Took me 2 years to realize there was such thing as global variables, which then opened up a whole new world.

Anyway, Marcin Policht has written a nice series on SQL Server 2000 DTS. There are 12 parts to it, and part 5 is:SQL Server 2000 DTS Part 5 - DTS Designer Tasks and Global Variables. Definitely worth bookmarking.

Terri

DTS Global Variable Variant Type Mismatch

I am trying to declare a global variable in a DTS package for passing the recordset to the next stage Active Script . After declaration of the Global Variable and selecting datatype of the variable as OTHER ( Variant ) , when I try to save the DTS Package changes , it throws a Type Mismatch Error .

:confused: Please help me out .

Thanx
Arnie .You have to execute the package or otherwise cause it to set the type of the variable to what it's going to use (for recordsets it is 'dispatch' but you can't set it manually). Once the type changes, you can save it.

DTS global variable question

Hello,
I am having difficulty with global variables in DTS. Specifically
I would like to instantiate a global variable with a value obtained
from a SQL database. I have created the global variable through the
package properties section. Now i need to instantiate it with a value
from the database. Should i use an ActiveX Script Task to accomplish
this? If so can someone send me a link that shows the best way of
doing this?

Thanks,
BillyAn ActiveX script task will do the job but you could also use a
DynamicProperties task if you are using SQL 2000. This enables you to set
properties at run time without writing code.

See http://www.sqldts.com/default.aspx?205 for an ActiveX script for an
example as sell as other useful DTS info.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Billy Cormic" <billy_cormic@.hotmail.com> wrote in message
news:dd2f7565.0409261325.4dda82fd@.posting.google.c om...
> Hello,
> I am having difficulty with global variables in DTS. Specifically
> I would like to instantiate a global variable with a value obtained
> from a SQL database. I have created the global variable through the
> package properties section. Now i need to instantiate it with a value
> from the database. Should i use an ActiveX Script Task to accomplish
> this? If so can someone send me a link that shows the best way of
> doing this?
> Thanks,
> Billy

DTS global variable problems...

Hey all,

I've recently been attempting a transform data task with a custom query for the source. Using the query, i've attempted to use global params, but it only ever seems to work if there is only one item in the global var. If I return an entire resultset, I get a "EXCEPTION_ACCESS_VIOLATION" instead. I'm trying to use it like "SELECT * FROM whatever WHERE ID IN(?)"

I've pondered this problem for quite some time now and I am wondering if there is a workaround for it. I know it would take much too long to do the same thing in activeX with a transform, so I would rather do it this way if I could.

Thanks in advance,
-KilkaHmmm, I'm not sure why that shouldn't work.... how are you setting your global variable??

An alternative way would be to use an ActiveX task to set the query value (eg. instead of setting a global variable set the query for the transform data task.)|||I've created them when I specify the IN (?) parameter under the parameters button. I then fill them with some ID's from several xls spreadsheets. I know that this procedure is working correctly because I wrote some activeX to spit out the contents of the var, and the size of the var. Everything there is right on the money. Also, when I specified the global variables, I set them as type <empty>, which is what I think im supposed to do according to msdn. I'm thinking that maybe this has something to do with the IN and it's param, as I'm doing this for 1500 items or so.

Thanks rokslide,
-Kilka|||errghhh, you might be running into a length problem... I suspect that there is a maximum length on the sql query for the transform data task...

Could you put the id's into a temp table and then do a join or something?

Can you test it without using so may items? perhaps only 10 or something...
that way you can try and eliminate possible problems...|||No, I've tried that already too. I've reduced the resultset so it only has two elements and I still get the same problem. I've verified that there were only two elements with the activeX script.

-Kilka|||Also,

Just thought that I should mention my global variables both appear as type "Dispatch" under the package properties. I was also thinking that perhaps I should try using another statement other than IN(?) in my data transformation. Is there anything like IN that would give me the same results ?|||apart from using multiple or statements, no, not really unless you create a temp table and join on it...

if you don't try and set the sql using a global variable what happens. does it still fail or does it work, I'm beginning to wonder if the problem isn't somewhere else...|||Ok,

So i've done some more playing around with this, still to no avail. Deleting the global var should and does give me "Global Variable 'chid2' not found'". If I create the variable but don't set it, I get "Invalid character value for cast specification". Finally if I set the var, even if the resultset has just one element, I still get the EXCEPTION_ACCESS_VIOLATION.

This is the error from the log

Step 'Copy Data from Results to Results Step' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Provider generated code execution exception: EXCEPTION_ACCESS_VIOLATION
Step Error code: 80040005
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:700

I've looked up this error and made sure that the Transform Data Task is executing on the main thread. I think I've done this right. When I put the param in this task, and it's already been filled, I get "No value given for one or more parameters" when attempting to do a preview.

Also, i've noticed that specifying one param, such as id=? in my source query works fine, it's just a rowset that does not work.|||also, it's just come to my attention that I get the same error when running test under the Tranformations tab of the Transform Data Task properties. The type of tranformation is a copy column.

Thanks in advance,
-Kilka|||So it may not be the querying at all but sme problem with the transform itself. Perhaps columns of the wrong data types or something affecting things?

If you completely replace the whole global variable bit and replace it (temporarily) with a static value and then try and execute it what happens?|||I've already tried that with an IN(1,2,3,4,5,5) in my source query. That seems to have worked fine.

I'd rather be doing as little processing in activeX as possible, but it's starting to look like I'll have to. I can just the global vars fine with activeX, but the problem is going to be performance. If anyone can think of anything else, I would be glad to hear it.

I was also thinking about constructing my string in a activeX object before this transformation, but I'm not sure if I'm allowed to use exec in a source like that because DTS won't know what columns I'm selecting much less moving.

Come to think of it, do you think the problem might be that there are no "," between elements of the global var. I think I'm going to try that next.

Thanks again rokslide,
-Kilka|||here's a thought that may or may not work....

what happens if you add an execute sql stask to your dts package and get it to try and execute the sql statement that is in your data transformation task?

does it still have a problem?? if not you could slip the execute sql task n before your transformation and get it to populate a temp table, then you can have your transformation execute a query that joins to the temp table to determine it's records...

just a thought...|||Yeah, it turns out I'm not in a position to do that. I can't create a temp table. I think my solution will be to cycle through the records using an activeX transformation and only copy the ones I need. I know this is a slow and stupid way to do it...single threaded too :( However, thanks for the help.

Cheers,
-kilka|||So now, I've taken to using a string to use my IN clause. however, there is some wierdness.

Assuming 445 is legit:
If I have 0,0,0,0,445 in the IN clause, I get the correct resultset
If I have 445,0, I get nothing
If I have 0,1,0,0,445 in the IN clause, I get nothing.
If I have 445 in the IN clause, it works fine..

Can anyone shed any light on this problem ?

-Kilka|||Can you post the entire sql statement??|||So I figured it out, got things to work dynamically.

So upon further investigation i've discovered that every data transformation object has a datapump task associated with it. The datapump task can have the source sql statement altered under the disconnected edit tool. I also found out that this variable can be edited using the following VB code:

' 205 (Change SourceSQLStatement)
Option Explicit

Function Main()
Dim oPkg, oDataPump, sSQLStatement

' Build new SQL Statement

sSQLStatement = "select * from <table> where ID IN(" & DTSGlobalVariables("instring2").value & ") OR ID IN (" & DTSGlobalVariables("instring3").value & ")"

' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

' Assign SQL Statement to Source of DataPump
oDataPump.SourceSQLStatement = sSQLStatement

' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing

Main = DTSTaskExecResult_Success
End Function

I would run this after I built the instring global variables. I then run the activeX task and finally the transformation task. I'm going to post a big howto at some point when I get time as well.

At present however, I'm now stuck with the fact that I can't go:
DTSDestination("CellPhone") = Left(DTSSource("CellPhone"),Len(DTSSource("CellPhone"))-4)

in the transformation. When I hit parse, it works, however when I hit test/execute the package, I get "Invalid procedure call or argument:'Left'" I know for a fact is the second argument that's causing the problem, but can't figure out why this wouldn't be allowed.

Thanks,
-Kilka|||Hi there,

Actually that was kinda what I was meaning, or something similar,... with your lastest problem you need to check for cellphone values that are less then 4 long. if a value is less then 4 (and possibly 4 as well) your call will fail.

Cheers,
Roko