Solved

can a variable in SSIS be based on another variable

Posted on 2010-09-03
18
608 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
ID: 33597675
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
ID: 33597912
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
ID: 33598014
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:BradGeary
ID: 33598078
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
ID: 33598098
can you post your evaluated query ??
0
 

Author Comment

by:BradGeary
ID: 33598158
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
ID: 33598298
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
ID: 33598391
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
ID: 33598533
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 33598557
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
ID: 33601853
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
ID: 33617243
The Sybase instance is ASE 12.5.0.3
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 33617503
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
ID: 33617509
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
ID: 33617695
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
ID: 33617752
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
ID: 33617758
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
ID: 33674572
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

790 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