Solved

can a variable in SSIS be based on another variable

Posted on 2010-09-03
18
600 Views
Last Modified: 2013-11-10
I may be attempting to do the impossible with this approach but doing it this way if possible would be preferable. If it is not possible, any suggestions on alternative ideas would be greatly appreciated.

Using BIDS 2005, I am attempting to build an SSIS package that copies contents of a table from one database based on the table name to select from being identified from table data in a different database (simply expressed, if this table is missing from database 1, import it from database 2).

I have two OLE DB source Data Flow objects. They are connected to different databases on different servers. One of the databases is Sybase ASE 12.5 and the other is SQL Server 2000.

What I need to do is query tables in the SQL Server database to determine the name of a table that needs to be imported from the Sybase database. I want to use variables to make the process as flexible as possible so that later on it can be modified to loop through and import data from multiple tables.

On the SQL Server side, I have a variable (var1) based on an SQL query that correctly returns the name of the table that needs to be imported. When I attempt to use that variable (var1) as the where clause in the SQL statement of var2, I get syntax error messages from the Sybase OLE provider. Hard coding the name of the table (bypassing the use of var1) to do the import works fine.

Both variables are in the scope of the package.

I do not have the ability to create a second database in SQL Server for testing to use to eliminate Sybase from the equation. On the Sybase server, I do not have administrative access but rather permissions to an alias database setup by the vendor. Since I cannot connect directly to the Sybase instance, I have no means to simply copy the entire database containing the tables where the required data resides.

Expert help would be greatly appreciated!  
0
Comment
Question by:BradGeary
  • 7
  • 7
  • 4
18 Comments
 
LVL 16

Expert Comment

by:vdr1620
Comment Utility
Use SQL Command from Variable in your OLE DB Source

Now in Var1 Variable write an expression such as

"SELECT * FROM" + @var2

and finally select the Var1 Variable in the OLE DB source
0
 

Author Comment

by:BradGeary
Comment Utility
vdr1620

Thanks for your quick response. I copy/pasted your syntax substituting the name of the variable but still get this error message:

 TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [OLE Dialer Table Data [5510]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "ASE OLE DB Provider"  Hresult: 0x80040E14  Description: "[Native Error code: 156]
[DataDirect ADO Sybase Provider] Incorrect syntax near the keyword 'Select'.
".
Error at Data Flow Task [OLE Dialer Table Data [5510]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020204A (Microsoft.SqlServer.DTSPipelineWrap)
0
 
LVL 16

Expert Comment

by:vdr1620
Comment Utility
give a space after from which is missing in my above expression.. also, check your SQL statement in the Expression Evaluated value window using Evaluate Expression
Untitled.jpg
0
 

Author Comment

by:BradGeary
Comment Utility
Tried your suggestion and it evaluated OK (the first one did as well). Still getting the same error.
0
 
LVL 16

Expert Comment

by:vdr1620
Comment Utility
can you post your evaluated query ??
0
 

Author Comment

by:BradGeary
Comment Utility
The query using the first variable is "Select  *  From" +    @[User::TableName] which evaluates OK

The query for the  @[User::TableName]  variable is "(Select Min(Table_desc) as Tbl
From Table_stat
Where Table_Orig_D >= '7/8/2010' And
 Table_desc Not In (Select TableName
From DialerTableInfo))"  which evaluates OK and returns the expected result.
0
 
LVL 16

Expert Comment

by:vdr1620
Comment Utility
i m not familiar with sybase SQL..but in SQL Server you will need to alias the Subquery which i see is missing such as

SELECT * FROM
(
Select Min(Table_desc) as Tbl
From Table_stat
Where Table_Orig_D >= '7/8/2010' And
 Table_desc Not In (Select TableName
From DialerTableInfo)
)A


Try changing your exp in Variable 1 to

"Select  *  From ( " +@[User::TableName]+ " ) Derived"

TableName Variable

Select Min(Table_desc) as Tbl
From Table_stat
Where Table_Orig_D >= '7/8/2010' And
 Table_desc Not In (Select TableName
From DialerTableInfo)
0
 

Author Comment

by:BradGeary
Comment Utility
Using that syntax, the evaluated query now displays the text of the underlying variable:

Select  *  From ( (Select Min(Table_desc) as Tbl
From Table_stat
Where Table_Orig_D >= '7/8/2010' And
 Table_desc Not In (Select TableName
From DialerTableInfo)))  Derived

BUT, still getting the same error message when atempting to  preview.  Beginning to REALLY love Sybase SQL
0
 
LVL 16

Expert Comment

by:vdr1620
Comment Utility
I am not familiar with sybase..i would like you to check the syntax ..try using the same query as is in OLE DB source by using the SQL command option... Also please remove the open and End braces from TableName Variable as i have added them to the 1st variable expression

TableName Variable

Select Min(Table_desc) as Tbl
From Table_stat
Where Table_Orig_D >= '7/8/2010' And
 Table_desc Not In (Select TableName
From DialerTableInfo)

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 16

Expert Comment

by:vdr1620
Comment Utility
Checking for Sybase syntax.. i just noticed that you cannot use sub queries in the FROM clause and you actually do not need two variable it be done using a single variable

Ref:
http://www.experts-exchange.com/Database/Sybase/Q_20687570.html
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
Comment Utility
This is incorrect, queries and subqueries are very much available in Sybase ASE... but only from a certain version onwards.

BradGeary - what version Sybase ASE are you using?
0
 

Author Comment

by:BradGeary
Comment Utility
The Sybase instance is ASE 12.5.0.3
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
Comment Utility
Ah, there's your problem. ASE "derived tables" are only available starting in ASE 12.5.1.

You have a number of options. You can build your SQL statement in your front end code, that was ASE only ever sees an SQL statement of literal text, you do all your manipulation before you get to ASE.

Or you can do it in ASE, build a string and then use "execute immediate" on it:

declare @str varchar(2000)
select @str = "select * from table"
exec (@str)


0
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 200 total points
Comment Utility
Are you absolutely constrained to use that version of ASE, by the way? I'm sure you know anything ASE 12.5.x is now unsupported, but there is a world of difference in performance and stability between 12.5.0.3 and 12.5.4...
0
 

Author Comment

by:BradGeary
Comment Utility
There is no choice of the database as it is in use for a vendor application - all I can do is connect via ODBC or OLE through an alias datatbase defined by the vendor .

It is probably worth reiterating that I am attempting to query data from Sybase using info from SQL Server (the sub-query/derived tablle) to identify the name of the Sybase table to select from.
I have the table name variable from SQL Server defined and I was hoping to be able to use it in the Sybase select as the FROM statement. The longer I work with this it seems that Sybase is not seeing the SQL Server data as a valid data source.  
 
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
Comment Utility
It's not about whether Sybase sees SQL Server as a valid data source because the two aren't talking to each other, only in your code.

Try either of the approaches above - fetch what you need from SQL Server, use that to build an SQL statement, send that SQL statement to Sybase. You don't need the databases to talk to each other.
0
 
LVL 16

Assisted Solution

by:vdr1620
vdr1620 earned 100 total points
Comment Utility
You will need to do things differently here..you will need to use a Execute SQL task to get the data from TableInfo Table and then pass them as parameters to the next task or store it in a variable and then use it in a expression

execute the TableName Variable SQL in a execute SQL task and pass the values as parameters to the next task
0
 

Author Comment

by:BradGeary
Comment Utility
Apologies for the delay in updating and resolving. The advice provided enabled me to get the package working using the variables as required. I needed to include it inside a loop container so that it will process any tables identified as missing. I used a for each loop based on an ExecuteSQL over ADO recordset to populate a variable that is used as the For Each ADO Enumerator.  Thanks to the Experts for pointing me in the right direction.

I have increased the total points to 300. 200 go to Joe Woodhouse in recognition of his expert level knowledge of Sybase. 100 to vdr1620 - at the end, his advice matched Joe's but there was a bit of diversion in the redirect that it couldn't be done in Sybase. After the problem was restated his advice was back in line. Regardless, I am  greatly appreciative of both efforts.    
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now