Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 626
  • Last Modified:

can a variable in SSIS be based on another variable

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
BradGeary
Asked:
BradGeary
  • 7
  • 7
  • 4
2 Solutions
 
vdr1620Commented:
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
 
BradGearyAuthor Commented:
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
 
vdr1620Commented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
BradGearyAuthor Commented:
Tried your suggestion and it evaluated OK (the first one did as well). Still getting the same error.
0
 
vdr1620Commented:
can you post your evaluated query ??
0
 
BradGearyAuthor Commented:
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
 
vdr1620Commented:
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
 
BradGearyAuthor Commented:
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
 
vdr1620Commented:
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
 
vdr1620Commented:
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
 
Joe WoodhousePrincipal ConsultantCommented:
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
 
BradGearyAuthor Commented:
The Sybase instance is ASE 12.5.0.3
0
 
Joe WoodhousePrincipal ConsultantCommented:
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
 
Joe WoodhousePrincipal ConsultantCommented:
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
 
BradGearyAuthor Commented:
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
 
Joe WoodhousePrincipal ConsultantCommented:
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
 
vdr1620Commented:
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
 
BradGearyAuthor Commented:
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 7
  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now