Solved

creating SSIS package to pull data from sybase

Posted on 2010-09-19
27
2,808 Views
Last Modified: 2013-11-10
Hi,
I need guidance in creating SSIS package on SQL 2008 64 bit where the data source is Sybase. I'm new to SSIS package and not sure how I can accomplish following that I tried:

1-check if new data with today's date is available
2-if available --> archive destination table into archive table -->truncate destination table --> insert data from source table
3- if data is not available, retry after 1 hour

I've installed and configured sysbase drivers and a simple test package works from sybase to sql however, I'm not sure how can I query the data to check availability? Also, I'm not able to configure linked server through which I might be able to query sybase table.

I'd be very thankful for detailed response.
0
Comment
Question by:Rainbow002
  • 13
  • 11
  • +2
27 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33711246
Hi,

Just for start up.
check out this link,

http://www.sqldev.org/sql-server-integration-services/import-data-from-sybase-adaptive-server-anywhere-9-into-sql-server-2008-64bit-2233.shtml

meanwhile u get many suggestions from experts ;-)
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 450 total points
ID: 33713490
1. Since you are using SSIS.. you do not need Linked servers..
2. Since your sample/test package works fine you don't need to worry about connections

--- All the statements below use SQL syntax..So change it to Sybase SQL

1. Now, to check if data is available .. use a Equivalent of Count (*) in Sybase.. So, in the source use equivalent of below SQL like

SELECT Count(*) FROM TableName WHERE Convert(Date,ColumnName) = convert(Date,GetDate())

connect the SOurce to RowCount to check the count... Now you will have the row count in a variable which you can use for further process

2. Use a precedence constraint LIKE VariableRowCount > 0---- connect it to next data flow task

3. In the second data flow task you can archive the table (move to a different table using OLE DB source and OLE DB destination)

4. use Execute SQL task to truncate the table (Non Archive Table)
5. Load data from sybase to SQL server

Now the main problem here is running it after an hour if the data in sybase is not available.. Which cannot be done directly in the package.. you will need to try something like below

1. Use a bit field in your table to set if the data is loaded into the table or not... If loaded then set the field to true else set the Field to false... you will need to check the field by adding an execute SQL task at the beginning of the process... Same as using the Count to check the current data from sybase... Then using another precedence container to check the value and move to the next step if the value evaluates to true...

Hope you understand the process, if not let me know

you can also use execute SQL task to check the data and return a True or false parameters using

SELECT RowCountValue = CASE WHEN COUNT(*) > 0 Then 'TRUE' ELSE 'FALSE' END FROM Tablename WHERE Convert(Date,ColumnName) = convert(Date,GetDate())

Then pass the value as a parameter..

Links below might help you
http://www.sqlis.com/post/The-Execute-SQL-Task.aspx
http://consultingblogs.emc.com/jamiethomson/archive/2005/05/29/SSIS_3A00_-Using-the-Rowcount-component.aspx





 

0
 
LVL 4

Expert Comment

by:timexist
ID: 33714243
SSIS Tutorial: SQL Server 2005 Integration Services Tutorial

http://www.accelebrate.com/sql_training/ssis_tutorial.htm

SQL Server Integration Services (SSIS) tutorial and example

http://www.mssqltips.com/tutorial.asp?id=200

Tutorial: SQL Server Integration Services (SSIS) best practices

http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1282913,00.html


have fun
0
 

Author Comment

by:Rainbow002
ID: 33716561
Hi vdr1620,

I'm trying to work through your suggestion. A small change in step 1, instead of checking new data with today's date, the step would be to actually check the result of a table which would be 1 or 0. If 1 then go to next step otherwise stop and retry. Excuse my laziness but for some reason it seems easier to work on this package on SQL 2000 i.e DTS and then convert to SSIS however, with your help I hope to accomplish this with SSIS...
0
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 50 total points
ID: 33716805
just change sql statement to anything you need , for example select f1 from mytable where ....
and set result set as single row, then set result set tab, with result name f1, and variable name MyVariable( suppose you defined this variable before)
and then just use an expression in precedence constraint to check the variable value, for example this expression:
@[User::MyVariable]==1


0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33717281
Once you start using SSIS you might feel that's it is other way round..

You can make using of precedence constraint along with an execute SQL task as i have suggested before..You will need to write a query to result a value of 1 or 0 and then pass the value by storing it in a variable which can be used to check the condition and move forward if Expression value evaluates to TRUE
0
 

Author Comment

by:Rainbow002
ID: 33717536
Ok. I'm trying to take it step by step and failed on the initial step. Here's what I did:  configured connection managers for Sybase (source) and SQL (destination), On Data Flow page, I  have sysbase ole db source and then Row Count from data flow transformations. When I executed the package it failed with following errors:
~~~~~~~~~~~~
Package Package
Validation has started
Task Data Flow Component
Validation has started
[SSIS.Pipeline] Information: Validation phase is beginning.
Progress: Validating - 0 percent complete

[Source for IMIS dataextracts de_itc_user1 [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "CON.datasource.user1" failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

[SSIS.Pipeline] Error: component "Source for CON datasource user1" (1) failed validation and returned error code 0xC020801C.

Progress: Validating - 50 percent complete
[SSIS.Pipeline] Error: One or more component failed validation.

Error: There were errors during task validation.

Validation is completed
[Connection manager "CON.datasource.user1"] Error: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR.  The requested OLE DB provider Sybase.ASEOLEDBProvider.2 is not registered -- perhaps no 64-bit provider is available.  Error code: 0x00000000.
An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".
Validation is completed
~~~~~~~~~~~~~~~~~~
please help!
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33717638
I thought you said that the basic package that you have tested worked fine ? Did it work fine.. If Yes, then use the same connection manager .. If not, make sure you are using the correct driver 64-bit or 32 Bit accordingly to connect to sybase.. If you have probleem connecting using a 64 bit driver, then you will need to install 32 bit driver and use ODBC 32-bit manager to create a connection ..It is located on C:\Windows\SysWOW64\odbcad32.exe
0
 

Author Comment

by:Rainbow002
ID: 33723939
Apparently the connection manager worked when configured through import/export wizard. I configured ODBC driver for 32-bit but when I execute the 1st step it fails with error:

[Execute SQL Task] Error: Failed to acquire connection "sybase.user1". Connection may not be configured correctly or you may not have the right permissions on this connection.

What am I doing wrong here? the password for username was also specified and tests fine when performing "test connection" from the connection manager...?
0
 

Author Comment

by:Rainbow002
ID: 33726352
I doubt if password is being passed at the run time?
0
 

Author Comment

by:Rainbow002
ID: 33726866
Some progress: I was getting the same connection validation error from SSIS and SSMS after saving the package. I saw a tab for “connection managers” in the window when trying to run the package from SSMS. Looked at the source connection which had all the connection info EXCEPT password then I put in the password in there and clicked on execute, it worked!

But why doesn’t it work from within SSIS?
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33727045
ok.. are you using any package configurations ..If that's the case then you will need to Edit the Connection String in the Configuration and Add password after username like below

User= aaaa,Password= bbb,etc...
0
 

Author Comment

by:Rainbow002
ID: 33727121
I'm not aware of any special configuration inside SSIS. was simply trying through connection managers.

So far, I have 2 execute SQL tasks (1. drop destination table 2. create destination table and a data flow task (copy data from source table to destination table) under control flow tab. Now, can you please walk me through in order to perform checking to see if data is available at source and if so, then go to next step? Greatly appreciate your help and sorry if i'm asking for too much :(
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 16

Expert Comment

by:vdr1620
ID: 33729219
I have never worked against a Sybase database nor have any to test and see how to resolve connection Problems..

Since you are using Windows 2008 64 bit, i would again suggest you to check the Driver being used to connect to the database and also use the Right ODBC Manager. Oracle had a bug connecting to 64 bit machine ..it used to work fine when you test the connection but fail when you run the package it was because of the path.. Most of the files related to SSIS are 32-bit and reside in Program Files (x86) Folder and Oracle connection was not allowing brackets in the path which was fixed in later versions.. I am not sure if Sybase has similar problem....

The below link is around the same issue,might help you to fix the problem.. Once the Connection problem is fixed the rest of it can be achieved using the method described above.. If you did not completely understand the procedure ..reply back ..I will try to help you out

http://www.experts-exchange.com/Database/Sybase/Q_23944427.html
http://venkattechnicalblog.blogspot.com/2008/07/sybase-to-sql-server-using-sql-server.html
0
 

Author Comment

by:Rainbow002
ID: 33733595
I'm not even able to get pass step 1 yet. This is what I'm trynig to follow but so far no luck: http://www.sqlis.com/sqlis/post/The-Execute-SQL-Task.aspx

I guess I'm not puzzled by the variable...
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33734941
Post some screen shots and the error you are getting
0
 

Author Comment

by:Rainbow002
ID: 33735267
Thanks for your continued support vdr1620. I'm not able to pass the variable thing to work as you can see in the screenshot attached.

Here's the SQL statement in step 1:

SELECT Count (available) as count_result
FROM dbo.my_table
WHERE Convert(Date,availability_date) = convert(Date,GetDate()) and available = ?

in variable I specified the value for count_result to 17 which I know what it should be...

somehow it's not recognizing the column alias "count_result" in where cluase so I changed it to actual column name...

I included all the configs and error please have a look.
SSIS-execTaskGeneral.jpg
SSIS-execTaskResultSet.jpg
SSIS-error2.jpg
SSIS-execTask-ParameterMapping.jpg
SSIS-variable.jpg
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33737446
Why do you want to pass the value as a parameter when there is only one value..you can directly mention the value in the sql as below... Then you can use a Result set variable to store value in the output,so that you can later use it in a precedence constraint..you can remove the parameter settings

SELECT Count (available) as count_result
FROM dbo.my_table
WHERE Convert(Date,availability_date) = convert(Date,GetDate()) and available = 17

0
 

Author Comment

by:Rainbow002
ID: 33738104
Thanks in this particular case I know what the ruturn value would be so I'll use your statement. Now, the only way to run the package is when running from Execute Package Utility where I can either use configuration file or edit source connection to add password otherwise I cannot run the package from inside SSIS as it doesn't retrieve the password. I'm trying to search for this solution also but main concern is if I can get the package to work however.

Now using  your method and I tried few different ones when I click execute from "Execute Package Utility", it validates all the tasks but hangs...I have to then kill the process in order to retry...
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33744646
which part of the workflow hangs? can you be more detailed?

If you are usinf configurations you will definetly need to edit the Connection String and add a password.. It will not add the password while creating configurations..you can also pass the password using DTUTIL in the job directly
0
 

Author Comment

by:Rainbow002
ID: 33745246
I have edited the config file to add the password that's when I'm able to get pass login issue when using DTExecUI. Please see screenshot attached. To further isolate the problem I created similar test package on my local SQL server and it works fine. I wonder if the issue is related to the Sybase drivers but I'm able to build quiry from execute SQL task and see the source data but I don't know what could be going wrong...
SSIS-hangs.jpg
0
 

Author Comment

by:Rainbow002
ID: 33755017
So now it's the expression that I'm still not able to get to work the package with. In the precedence, I chose expression and then to make it very simple and cause it to fail, I put the expression as 1>2 but when I run the package it just hangs like above screenshot. If I disable this task with expression the package runs fine. Still trying to figure out...
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33755651
Ok.. Use the below Expression

@VariableName >=1 --- the VariableName that store the Resultset.. Connect this Precedence constraint to next task

using 1> 2 .. i believe its going in an infinite loop for some reason and unable to evaluate the expression correctly and thus hangs
0
 

Author Comment

by:Rainbow002
ID: 33756021
Thanks a lot for your help. I resolved the issue...So to resolve the issue I had to configure ADO.NET for odbc and odbc 32bit is where the connection to sybase was added. Now the simple test works with variable.

Thread has gone pretty long. Do you prefer, I close this question and open up another one for further steps? or is it okay till I complete the package hopefully next week?
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33756430
Anything's fine.. I am here to help you out.. if you open up another question just link both the posts
0
 

Author Closing Comment

by:Rainbow002
ID: 33780711
Many thanks for your help vdr1620. I'll be on leave for couple of weeks and will pick back up on this package when I return. I have a pathway that you've provided but if I need additional help, I'll post question here.
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33781372
Ok..sure
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

757 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

19 Experts available now in Live!

Get 1:1 Help Now