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.

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.

Load dts2000 fails

Sometime ago i had to edit a dts package in ssis. To do that i've instaled the sql2005 dts. It worked fine at the time. But now i'm trying to open a dts in ssis after installing sp2 and it doesn't work. Every time i try to open a dts package i got the following error.

Error HRESULT E_FAIL has been returned from a call to a COM component. (Microsoft Visual Studio)

Program Location:

at DTS.CDTSLegacyDesignerClass.ShowDesigner()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.GeneralView.btnEdit_Click(Object sender, EventArgs args)

I dont understand why this is happening. Just one thing i use to have windows server 2003 now i've xp sp2. I don′t know if this can be important but.

Thanks in advance.

I believe you can't open/edit DTS files in SSIS. You can execute DTS files from SSIS though.|||Of course you can edit them. But it's not even opening the packages, that's the problem. When you put the execute 2000 dts package task you can click the edit buton to see the dts. But i got the error i've wirted above.|||It's hard to tell for sure from the error, but it's possible that the DTS 2000 designer installation is corrupt. Have you tried installing the DTS Designer Components from the SQL 2005 Feature Pack download site?|||

Not sure, but try removing the curent feature pack and installing the Feb 2007 feature pack, as that may have been updated for SP2 - http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

I didn't think it changed but at any rate the re-install may help.

|||yeah i've installed the latest version of the pack but it still doesn't work. I really would like to know if there is somebody using winXPsp2 and have this component working. Because i use to have the 2003server and it was with the sp2 of sql2005 and it was working fine. So i really would like to know if it is the OS.

thanks|||

We do have an issue related to the use of teh DTS Designer with different versions of the common control dll found on different OS's. Since we've ruled out a corrupt install of the designer components, then I think it's worthwhile to spend the time to diagnose for the problem I mentioned. See this KB:

http://support.microsoft.com/default.aspx/kb/917406/en-us

|||That was a good idea but it didn't worked. I'm going to try some new approaches. I will keep everybody up to date. I only hope that this just work like it did before.

Thanks

Load Data using ADO.NET in SSIS

Hi,

How to extract and Load Data using ADO.NET in SSIS.i hope to extract data we have DataReader source .but how to load (Insert) data with ADO.NET ?.and is ADO.Net quicker than OLEDB ?

Thanks

Jegan.T

Where are you loading it to? SqlServer and OleDB Destinations will be the easiest, and most performant since they will do a bulk insert.|||

Sean,

Thanks for the Informations.But still as a part of our analysis .we need to load in to Oracle Database. can i know how to load data using ado.net .

Thanks

Jegan.T

|||

Jegan,

you can use .NET, Ole DB or ODBC providers to load data in to Oracle Databases, and these are all possible via ADO.NET.

Both Microsoft and Oracle, as well as other 3rd party vendors have Ole DB, .NET and ODBC connectors for Oracle databases. It all depends on your requirements on what version of Oracle and what features you'd like in your connection.

We recommend our customers to use Oracle's OleDB provider, and ODBC providers could be a bit slower, because SSIS uses the .NET-ODBC bridge, not native ODBC connections for data source components.

Another option could be using the native ODBC provider inside the ExecSQL Task, load the recordset, and use it in the data flow task, which could be cumbersome to do.

I think your best bet would be to use Oracle's OleDB provider.

|||Deniz Thanks.

load all data without knowing old one was load in the previous time?

I just have done the SSIS example in the tutorial document included when install SQL 2005 ENT. I have a problem that whenever I test to run, the service load all data from source with out noticing about the data (I mean it load all the data to the destination), I do it several time and it continue to load all without checking. That mean the data is dublicated when the schedule run?

I think there should be a paramete or something like that to help the engine just load the new data to the destination. Could you help please?

Thank

Hi Cao Van,

Ofcourse when you run the package every time it means you are inserting duplicate rows. To avoid this, I have two familiar methods

1) Truncate the destination table, everytime when you load the data from source and populate the detination table. (This is always time consuming process)

2) Use Look up concept to filter out the duplicates.

Thanks

Subhash Subramanyam

|||

In other words, you are the responsible of putting enough logic to avoid loading duplicate rows in your destination. Subhassh suggestions are very valid. For the first one you can use an execute sql task in control flow, right before the data flow to perform the TRUNCATE. For the second option, make sure you search the forum; there have been many discussions around that, actually in the first page of the forum, there is a sticky post that talks about it.

sql

List Server Roles

I am currently working on a SSIS configuration database. However, I am having trouble writing a script to get the the users that have each server role permissions. I'm pretty sure that I need to use sp_helpsrvrole, but am not sure where to go from here. If anybody has any ideas or has seen a script on the internet to get this then that would be a tremendous help.

Thanks in advance,

Kyle

Please try:

exec sp_helpsrvrolemember

this should get the server role membership information you are looking for. Let us know if you have any further questions.

-Raul Garcia

SDE/T

SQL Server Engine

List of All SSIS packages stored in MSDB

Hi,

I am writing one program in vb.Net where I need to bind a treeview with all the SSIS packages stored in SQL server MSDB database.

Is it possible to get all the SSIS packages by using T-SQL query or Object model ?

Thanks

Reference assembly Microsoft.SqlServer.ManagedDts.dll and create Application object (in Microsoft.SqlServer.Dts.Runtime namespace). To get list of packages use Application.GetPackageInfos method:

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.getpackageinfos(SQL.90).aspx|||

Alternatively the following query returns the list of stored packages from MSDB database:

SELECT * FROM msdb.dbo.sysdtspackages90

Thanks,
Loonysan

List of All SSIS packages stored in MSDB

Hi,

I am writing one program in vb.Net where I need to bind a treeview with all the SSIS packages stored in SQL server MSDB database.

Is it possible to get all the SSIS packages by using T-SQL query or Object model ?

Thanks

Reference assembly Microsoft.SqlServer.ManagedDts.dll and create Application object (in Microsoft.SqlServer.Dts.Runtime namespace). To get list of packages use Application.GetPackageInfos method:

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.getpackageinfos(SQL.90).aspx|||

Alternatively the following query returns the list of stored packages from MSDB database:

SELECT * FROM msdb.dbo.sysdtspackages90

Thanks,
Loonysan