Load images into Sql Server 2005

Hi There

I have not had much luck finding info in BOL for tsql or SSIS that tells one how to load images on the file system into sql server 2005.

All i have really been able to find is that IMAGE data type will not be used in future and that one should use a varbinary(max) data type.

I am thinking of using a for each file loop in SSIS, but then how do i load the images (.tif) into a sql server database table ? Perhaps i need to use a sql task with the filepath , or an active x script.

Anyway if anyone knows how i can load images from the file system into sql server 2005, please let me know.

Thanx

Hi,

U can use Activex Script. But it is better to use Script task.

|||

You should look at the Import Column component. http://msdn2.microsoft.com/en-us/library/ms141262.aspx

All you need is a source file or table, with a column containing the filenames you wish to insert. SSIS will then load each named file into the data flow, from whence you can insert it to the database as normal.

Choice of the best data type for storing images would be better discussed on the SQL Server engine forum.

Donald

|||

Hi Donald

Would a for each file loop not work better, as a source file or table is not realistic considering there are over 9 million flat files.

I am not sure i am following you, import column requires a data source, if i point a flat file source to a image file it gets very confused with randon columns and junk data. I am not sure how to feed the flat file image to the import column task?

I have figured out another way to do it. But i am also interested in your suggestion.

Thanx

|||

That is indeed a lot of files. Actually it is so many that you may want to contact me offline to discuss options. DonaldDotFarmerAtMicrosoftDotCom will get me.

One way to generate a suitable file as a source, may be to pipe a dir command to file and then parse the result using SSIS - either using a flat file source, or a script component. From there it would be relatively simple to implement your scenario.

Donald

|||

Thanx Donald

I am gonna try a couple of things and i will get back to you.

|||

The TextCopy utility is a great way to load binary data into BLOB fields. I am calling the util from a .bat file. Its another option anyway....

Check out this article

http://www.mssqlcity.com/Articles/KnowHow/Textcopy.htm

|||

Dietz wrote:

Hi There

I have not had much luck finding info in BOL for tsql or SSIS that tells one how to load images on the file system into sql server 2005.

All i have really been able to find is that IMAGE data type will not be used in future and that one should use a varbinary(max) data type.

I am thinking of using a for each file loop in SSIS, but then how do i load the images (.tif) into a sql server database table ? Perhaps i need to use a sql task with the filepath , or an active x script.

Anyway if anyone knows how i can load images from the file system into sql server 2005, please let me know.

Thanx

the t-sql OPENROWSET BULK statement is designed to import files into sql server: http://msdn2.microsoft.com/en-us/library/ms190312.aspx

this statement can then be used in an execute sql task.

i suggest that you consult with the t-sql forum if you need assistance with this statement.

List NT-User-Names in Active Directory (for SQL-Logins creation)

Hi there
I got - maybe a simple problem. A customer runs Active Directory Structure,
I've to implement a Application internal Security. That for, I have to list
all NT-Accounts of the Domain for the Adminstrator to select as a SQL Login.
In the SQL-Enterprise Mana
ger, if you add a new Login a dialog box appears, in which you can choose th
e Domain and the listed -NT-User (Group or User). Exactly this Dialog box I'
ve to rebuilt in the App. To find the Domain wasn't any problem, but to list
all Users and Groups, I do
n't figured out yet. Does anybody know how-to? Or is there a SP or FN wich I
didn't know ?
Many thanks for your helpSetup a linked server and use openquery, see if this link helps -
http://msdn.microsoft.com/library/d...r />
_94fn.asp
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Bruno" <anonymous@.discussions.microsoft.com> wrote in message
news:77CAFCCF-5A89-4757-8310-2A004C58D60A@.microsoft.com...
> Hi there
> I got - maybe a simple problem. A customer runs Active Directory
Structure, I've to implement a Application internal Security. That for, I
have to list all NT-Accounts of the Domain for the Adminstrator to select as
a SQL Login. In the SQL-Enterprise Manager, if you add a new Login a dialog
box appears, in which you can choose the Domain and the listed -NT-User
(Group or User). Exactly this Dialog box I've to rebuilt in the App. To find
the Domain wasn't any problem, but to list all Users and Groups, I don't
figured out yet. Does anybody know how-to? Or is there a SP or FN wich I
didn't know ?
> Many thanks for your help|||Dear Ray
Many thanks for your hint. It was not exactly what I searched for (cause OU'
s are here very dynamic, and I've to have all Users) but it gives the necess
ary hint to succeeed in my investigations.
Best Regards,
Bruno

Lisence for replication

Hello there
I have two servers who needs to be updated From master to slave.
For this i need to use the replication
I've heared that i need Enterprise on both sides and it coast approx 19000$
Do i need them on both sides? or i can use the standart
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
I think you are thinking about Log Shipping. There is a wizard which allows
you to do this in the Enterprise Edition, but you are free to roll your own.
Replication is a free component which ships in most versions of SQL Server.
In most other RDBMS's it is another component which you have to pay for.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%231zdmsWCGHA.3920@.tk2msftngp13.phx.gbl...
> Hello there
> I have two servers who needs to be updated From master to slave.
> For this i need to use the replication
> I've heared that i need Enterprise on both sides and it coast approx
> 19000$
> Do i need them on both sides? or i can use the standart
> --
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>
|||Whell Hilary
If on my SQL server, which is standart edition I can use replication, is
that means that it is leagal?
and if not. and from Enterprise edition I can push replication to subscriber
who has standart edition. does the subscriber have to be enterprise edition?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#UjdR8WCGHA.1312@.TK2MSFTNGP09.phx.gbl...
> I think you are thinking about Log Shipping. There is a wizard which
allows
> you to do this in the Enterprise Edition, but you are free to roll your
own.
> Replication is a free component which ships in most versions of SQL
Server.
> In most other RDBMS's it is another component which you have to pay for.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:%231zdmsWCGHA.3920@.tk2msftngp13.phx.gbl...
>
|||As long as you have the per processor license or you have CALs in place this
should be ok. You do not need a special license to connect from Standard to
Enterprise.
Does this answer your question?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:e8$1KcZCGHA.4040@.TK2MSFTNGP12.phx.gbl...
> Whell Hilary
> If on my SQL server, which is standart edition I can use replication, is
> that means that it is leagal?
> and if not. and from Enterprise edition I can push replication to
> subscriber
> who has standart edition. does the subscriber have to be enterprise
> edition?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:#UjdR8WCGHA.1312@.TK2MSFTNGP09.phx.gbl...
> allows
> own.
> Server.
>