August 24, 2010 at 7:30 am
I have a situation where I need to be able to transfer records from a table in an informix database into a SQL Server 2005 database. The application that dumps information into the infromix table does so every minute. I wanted to set something up that would pick up records out of the informix table that hadn't been picked up before the last 'run' and insert them into the sql server table. So the ssis job would have to run every couple of minutes to keep the sql server table up to date. There's a field in the informix table called msg_time that indicates when the record was inserted. I was hoping to use that to figure out which records to grab and then insert into sql server.
I have never used ssis and have just picked up the Apress book "Pro SQL Server 2005 Integration Services". Does this sound like something ssis can handle? I think so but like I said I've never used it so before I expend a ton of time reading up on it I'd like to hear from anyone who has used ssis. Also if you could suggest some parts of ssis I should pay attention to for what I'm looking to that would be great!
August 24, 2010 at 10:29 am
edowney10 (8/24/2010)
I have a situation where I need to be able to transfer records from a table in an informix database into a SQL Server 2005 database. The application that dumps information into the infromix table does so every minute. I wanted to set something up that would pick up records out of the informix table that hadn't been picked up before the last 'run' and insert them into the sql server table. So the ssis job would have to run every couple of minutes to keep the sql server table up to date. There's a field in the informix table called msg_time that indicates when the record was inserted. I was hoping to use that to figure out which records to grab and then insert into sql server.I have never used ssis and have just picked up the Apress book "Pro SQL Server 2005 Integration Services". Does this sound like something ssis can handle? I think so but like I said I've never used it so before I expend a ton of time reading up on it I'd like to hear from anyone who has used ssis. Also if you could suggest some parts of ssis I should pay attention to for what I'm looking to that would be great!
Regarding extraction of data, yes you can able to do it by storing the last 'run' date into some lookup table and write extract logic to pull incremental records from the source where msg_time is greater than the lookup value.
In order to achieve this load, you may need to concentrate on how the data flow task and setting up the connection information. I have not used Informix but you can get help using "google" if you have any questions about using the appropriate provider for the same.
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]
August 24, 2010 at 10:31 am
Yes SSIS can handle this. I am using SSIS currently for a similar task.
As Baskar suggested, use the date field as your control point. You may need to enter a timestamp value into a control table to speed things up, or get max date from your SQL table that has already been imported.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 24, 2010 at 10:36 am
I have the connections working for informix - so that's not a problem. I'm a c# developer and have done quite a bit of sql programming in my time but this ssis is whole other ball of wax. Has anyone seen an example of what I'm attempting to do?
August 24, 2010 at 10:40 am
edowney10 (8/24/2010)
I have the connections working for informix - so that's not a problem. I'm a c# developer and have done quite a bit of sql programming in my time but this ssis is whole other ball of wax. Has anyone seen an example of what I'm attempting to do?
In that case, you may need to get yourself familiar with SSIS before you try to do some coding... This would make sure you would not mess up with the tool 🙂
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]
August 24, 2010 at 10:43 am
The book you listed should be able to get you familiar with the basics.
Much of the tool is GUI driven. You would need to setup your connection, create a data source, and also you would need a variable.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply