September 9, 2011 at 1:48 pm
Hi,
I need to transfer data on a regular automated basis from a database running on a SQL Server 2005 instance to a database running on a SQL Server 2008 instance.
Can I do this using an SSISpackage , will there be any issues with SSIS running on 2005 pushing data to 2008 ?
Or Shall I consider using linked server(from sql server 2005 to sql server 2008) in a stored procedure and running the job.
Because of very limited time I dont have time to test the rnatives.
Please share your ideas, experiences!
Thanks,
Janki
September 9, 2011 at 7:38 pm
Yes you can transfer data from one server to another with an SSIS Package.
Would suggest creating the package with SQL Server 2008 BIDS and deploying to the SQL 2008 instance-
pulling data from the 2005 instance rather than pushing data to the 2008 instance.
We have several 2008 SSIS pkgs pulling data from sql 2000 & 2005 to 2008 - no issues.
September 9, 2011 at 9:52 pm
If you're pressed for time you may find the linked server path to be the one of least resistance. While you can do pretty much anything with SSIS, there's a lot of 'gotchas' that can come up that can really drag a project out. If you're experienced with SSIS already you probably know about them - if not, stay away from SQL Server connections, use OLE DB instead it's less error prone, and you really don't lose much performance.
The linked server options are not without their caveats, the biggest being the difficulty when it comes to troubleshooting - but in the end I've found them to actually be more reliable than SSIS. At my org we've built an entire data warehouse ETL pull using linked servers and it has only ever had a problem once.
We had also built the same data transfer scripts in SSIS and they ran into issues regularly. It always seemed to come down to a post on the MSDN forums which would result in 'oh, don't use THAT component'.
September 9, 2011 at 11:03 pm
SSIS Package and automation.
Please have a look into this : http://sqlschoolhouse.wordpress.com/2010/08/11/executing-ssis-package-as-scheduled-sql-server-job/
Cheers,
- Win.
" Have a great day "
September 10, 2011 at 7:09 am
prvmine (9/9/2011)
Yes you can transfer data from one server to another with an SSIS Package.Would suggest creating the package with SQL Server 2008 BIDS and deploying to the SQL 2008 instance-
pulling data from the 2005 instance rather than pushing data to the 2008 instance.We have several 2008 SSIS pkgs pulling data from sql 2000 & 2005 to 2008 - no issues.
Hi, Actually the SQL Server 2005 is the application database and the 2008 has a small database for limited public view. SQL Server 2008 server is used for all web applications which are for public views. for other applications data is fed from an oracle database server.
The 2005 server is used for all critical databases used for internal applications. The client wants the package should run in the 2005 server and push data into the 2008 server.
So I'll have to develop the package in 2005 only. Do you think it would be an issue pushing data from 2005 to 2008.The package will run daily.
September 10, 2011 at 7:22 am
Hi Kevin, Yes, because of time, I think I will do the linked server way first. I have used SSIS packages before but it were running between 2 sql server 2005 only or between 2008 and a file-server.
I have 1 more thing to consider here, there is a fileblob column (nText) in the 2005 database and in the sql server 2008 database I made the corresponding column a varbinary(max) column, not sure if ntext-->varbinary(max) transfer will create any issue, i havent dont it yet.
Let me try the linked server way..I will post if I run into issues!
September 12, 2011 at 12:44 pm
Hi, I am trying to create a linked server from SQL server 2005 to SQL Server 2008.
I created the DSN.
I created a user, same login id and password for both the servers.
When I am trying to create the link server, it gives error '***' is not a valid user or you do not have permission, Error 15007.
Also, in the error message it just gives the first 3 characters of the user, not the full user name.
Am I doing it the wrong way?
September 13, 2011 at 3:13 pm
prvmine (9/9/2011)
Yes you can transfer data from one server to another with an SSIS Package.Would suggest creating the package with SQL Server 2008 BIDS and deploying to the SQL 2008 instance-
pulling data from the 2005 instance rather than pushing data to the 2008 instance.
We have several 2008 SSIS pkgs pulling data from sql 2000 & 2005 to 2008 - no issues.
Hello
I am using SSIS to push the data from SQL 2005 to 2008.
Any idea how can I convert nText data to varbinary(max)
I first did a cast from ntext to nvarchar(max), then from nvarchar(max) to varbinary(max) .
It was done but when I try to display file using the .NET code, Acrobat gives error it is not a supported type file or has been damaged.
Thanks - Janki
September 13, 2011 at 3:18 pm
SJanki (9/13/2011)
I am using SSIS to push the data from SQL 2005 to 2008.Any idea how can I convert nText data to varbinary(max)
I first did a cast from ntext to nvarchar(max), then from nvarchar(max) to varbinary(max) .
It was done but when I try to display file using the .NET code, Acrobat gives error it is not a supported type file or has been damaged.
Thanks - Janki
The nText field works with Acrobat? usually you'd use an image field in the older versions to deal with external file types.
VARCHAR(Max) will NEVER work once it's in there. It's actually a VARCHAR() field. It'll break your headers. Why did you do the cast in between?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 13, 2011 at 5:57 pm
The nText field works with Acrobat? usually you'd use an image field in the older versions to deal with external file types.VARCHAR(Max) will NEVER work once it's in there. It's actually a VARCHAR() field. It'll break your headers. Why did you do the cast in between?[/quote]
Hi,
the older version, SQL Server 2005, was just storing the documents in nText type column, they do not have to display the file.
In my application I have to display the file but the source of the data is the data stored in the SQL Server 2005.
I need to convert the nText data to varbinary, so that I can display the image of the pdf/document.
I understand if the column in 2005 would be an Image type it would have been easier to map it to varbinary and thus display the file but The only thing I have is the PDF stored in an nText column in SQL Server 2005.
What can I do to get it as varbinary/image?
thank you!
September 13, 2011 at 6:06 pm
SJanki (9/13/2011)
Hi,the older version, SQL Server 2005, was just storing the documents in nText type column, they do not have to display the file.
In my application I have to display the file but the source of the data is the data stored in the SQL Server 2005.
I need to convert the nText data to varbinary, so that I can display the image of the pdf/document.
I understand if the column in 2005 would be an Image type it would have been easier to map it to varbinary and thus display the file but The only thing I have is the PDF stored in an nText column in SQL Server 2005.
What can I do to get it as varbinary/image?
thank you!
Um, if you can't get the nText as it is now to work with Acrobat, the data itself might have been destroyed in transfer. nText implies that it can be full text indexed, which changes the data itself.
However, the only way to directly deal with it is via creating a VarBinary(Max) column, and moving the data with an explicit conversion, then removing the old column. I'm afraid it won't allow you to do this any other way. I still wouldn't trust the data though if you can't get it to function from the nText in the first place. You may be needing to reload all those pdfs.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 13, 2011 at 6:21 pm
I still wouldn't trust the data though if you can't get it to function from the nText in the first place
Hi,
I did not understand what do you mean by-->
I still wouldn't trust the data though if you can't get it to function from the nText in the first place?
nText data, can not be displayed as an image right? I mean a pdf in Adobe or a document in MS word.
To display the documents stored in SQL Server, it should be stored using image or the varbinary(max) types.
Could you please elaborate. Thanks!
- Janki
September 14, 2011 at 11:14 am
SJanki (9/13/2011)
I still wouldn't trust the data though if you can't get it to function from the nText in the first place
Hi,
I did not understand what do you mean by-->
I still wouldn't trust the data though if you can't get it to function from the nText in the first place?
nText data, can not be displayed as an image right? I mean a pdf in Adobe or a document in MS word.
To display the documents stored in SQL Server, it should be stored using image or the varbinary(max) types.
Could you please elaborate. Thanks!
- Janki
Can the currently stored data in the nText field in 2k5 be used in Acrobat? If not, the data will not suddenly 'work' if you move it to a VarBinary, it's already been corrupted as far as Acrobat is concerned. If it does work (which would surprise me), then you'll be able to explicitly copy it to the new column.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 14, 2011 at 11:39 am
Can the currently stored data in the nText field in 2k5 be used in Acrobat? If not, the data will not suddenly 'work' if you move it to a VarBinary, it's already been corrupted as far as Acrobat is concerned. If it does work (which would surprise me), then you'll be able to explicitly copy it to the new column.
But nText is character data, so it cant open in Adobe Acrobat.... that is the reason I am trying to convert it to image/varbinary(max)
I am not sure if a file stored in sql server in nText type can be displayed. I'll have to check that.
September 14, 2011 at 11:43 am
Is it possible that in the ntext -> nvarchar conversion something got truncated? nvarchar has a max of 4000 characters, which is not an awful lot of space for most binary data.
Try checking the length of your ntext column to ensure that you're not losing data. DATALENGTH() should work for that.
There are ways to convert directly from ntext to varbinary as well:
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply