Extracting information from a .jpg file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Extracting information from a .jpg file

Author

Topic

Rupa
Posting Yak Master

123 Posts

Posted - 2008-05-21 : 10:24:14

Hey all

Is it possible to extract length(size) from an image?

Many thanks,

Rupa

tmitch
Yak Posting Veteran

60 Posts

Posted - 2008-05-25 : 23:07:13

You can - you'll need to use a Script Task or Script Component and drop into the System.IO namespace to pull out file information.

---------------------

Tim Mitchell
www.BucketOfBits.com

Rupa
Posting Yak Master

123 Posts

Posted - 2008-05-26 : 11:06:42

Many thanks Tim...Could you please tell me how?? Any piece of code will be helpful.

Many thanks,

Rupa

tmitch
Yak Posting Veteran

60 Posts

Posted - 2008-05-26 : 21:02:56

In your Script Task/component, make sure to add the line:

Imports System.IO

at the top of the file (you'll se a number of other Imports statements - just place this line anywhere in that area).

In your code, use something like this to grab the file size information:


Dim f as FileInfo = new System.IO.FileInfo("c:\my_file.jpg")
Dim fileSize as Integer = f.Length()

Hope this helps...
Tim

---------------------

Tim Mitchell
www.BucketOfBits.com

Rupa
Posting Yak Master

123 Posts

Posted - 2008-05-27 : 05:19:42

Many thanks for that Tim..I have got that script component within a Foreachloop container as I want the size for all the files within a specific folder. I've tested it on its own and there are no errors.

How would I put that information into a file?? Any pointers will be highly appreciated. I thought of flat file destination but that option is not available in the control flow??

Thank you so much for your help Tim.

Much appreciated!!

Rupa

Rupa
Posting Yak Master

123 Posts

Posted - 2008-05-27 : 05:27:20

Sorry Tim..ignore that message..I'm gonna use script component in a Data Flow Task..any problems, I'll seek your guidance :-)

Many thanks again,

Rupa

Rupa
Posting Yak Master

123 Posts

Posted - 2008-05-27 : 11:24:10

Hey Tim

I have managed to make it work but I also need three more fields from a table..So I want:

IdNo, Length, Path, Type

I have got the length(size), thanks to you..

and I have the other three using SQL commmand. Can I merge these two and create one line like:

17, 34844, C:\LC\Import\DataImages\17.jpg, 1

Can I have the sql statement within the same public class using script component?

Many thanks,

Rupa

tmitch
Yak Posting Veteran

60 Posts

Posted - 2008-05-27 : 21:23:00

Rupa,

If you are writing your SQL script from within the data flow, you can easily incorporate these values (either statically or dynamically) into your INSERT/UPDATE script.

If you are sending the data out through the Output, you can similarly create output columns (see the Inputs and Outputs tab on your script Properties page) and send these other elements down the pipe.

Hope this helps... let me know if you need any more info.

---------------------

Tim Mitchell
www.BucketOfBits.com

Rupa
Posting Yak Master

123 Posts

Posted - 2008-05-28 : 05:39:11

Thanks Tim,

I need help with script writing..sorry! So far, I have:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO
Imports System.Data.SqlClient

Public Class ScriptMain
Inherits UserComponent

Private connMgr As IDTSConnectionManager90
Private sqlConn As SqlConnection
Private sqlCmd As SqlCommand

Public Overrides Sub CreateNewOutputRows()
Dim f As FileInfo = New System.IO.FileInfo("C:\LC\Import\DataImages\17.jpg")
Dim fileSize As Integer = CInt(f.Length())
Dim strSQL As String

sqlCmd = New SqlCommand(strSQL, sqlConn)

strSQL = "SELECT IdNo, 'C:\LC\Import\DataImages\' + cast(IdNo as varchar(5)) + '.jpg' AS Path, '1' AS Type FROM(tbl_c1)"

Output0Buffer.AddRow()
Output0Buffer.Output1 = fileSize
Output0Buffer.OtherData = strSQL

End Sub

End Class

When I run this, it inserts the file size correctly but it doesn't add the data from the table..it inserts:

"SELECT IdNo, 'C:\LC\Import\DataImages\' + cast(IdNo as varchar(5)) + '.jpg' AS Path, '1' AS Type FROM(tbl_c1)"

I know I need to add a cmdExecute somewhere?? I'm not really good at this...could you please guide me??

Output0 is the output which has the following two columns:

Output1
OtherData

I still want IdNo, Length(Output1), Path and Type as four separate columns in the file destination!!

Thank you so much,

Rupa

Rupa
Posting Yak Master

123 Posts

Posted - 2008-05-28 : 09:33:28

I now have:


' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO
Imports System.Data.SqlClient



Public Class ScriptMain
    Inherits UserComponent

    Private connMgr As IDTSConnectionManager90
    Private sqlConn As SqlConnection
    Dim sqlReader As SqlDataReader


    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
        MyBase.AcquireConnections(Nothing)

        connMgr = Me.Connections.MyADONETConnection
        sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

    End Sub
    Public Overrides Sub PreExecute()

        Dim cmd As New SqlCommand("SELECT IdNo, 'C:\LC\Import\DataImages\' + cast(IdNo as varchar(5)) + '.jpg' AS Path, '1'  AS Type FROM(tbl_c1)", sqlConn)
        sqlReader = cmd.ExecuteReader

    End Sub
    Public Overrides Sub CreateNewOutputRows()
        Dim f As FileInfo = New System.IO.FileInfo("C:\LC\Import\DataImages\17.jpg")
        Dim fileSize As Integer = CInt(f.Length())

        'Output0Buffer.AddRow()
        'Output0Buffer.Output1 = fileSize

        Do While sqlReader.Read
            With Output0Buffer
                .AddRow()
                .OtherData = sqlReader.GetString(1)
                .Output1 = fileSize
            End With
        Loop
    End Sub


    Public Overrides Sub PostExecute()

        sqlReader.Close()

    End Sub

    Public Overrides Sub ReleaseConnections()

        connMgr.ReleaseConnection(sqlConn)

    End Sub

End Class

But it now gives me the following error:

Incorrect syntax near ')'.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at ScriptComponent_82a143299e11404ab4c5e36bb3b06b7d.ScriptMain.PreExecute() in dts://Scripts/ScriptComponent_82a143299e11404ab4c5e36bb3b06b7d/ScriptMain:line 33
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PreExecute()

I can't see anything wrong??? Any help will be highly appreciated!!!

Many thanks,

Rupa

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-28 : 09:49:14

May be this:

quote:

Dim cmd As New SqlCommand("SELECT IdNo, 'C:\LC\Import\DataImages\' + cast(IdNo as varchar(5)) + '.jpg' AS Path, '1'  AS Type FROM(tbl_c1)", sqlConn)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Rupa
Posting Yak Master

123 Posts

Posted - 2008-05-28 : 09:54:54

Thx Harsh :-) You come to my rescue again..Got a headache from this so not concentrating..

I've only got one problem though..it only has the following values in the file:

34344,C:\LC\Import\DataImages\17.jpg

It doesn't have the IdNo and type

I'll try and work on it..Meanwhile, if anyone's got suggestions, please do post them..

Many thanks,

Rupa

Rupa
Posting Yak Master

123 Posts

Posted - 2008-05-28 : 11:13:16

I have made it work but I want to get the size for all the files in that folder not just one.

I've added a Foreachloop container and placed the Data Flow Task in there but it still does the following:

34401, 17, C:\.....\17.jpg, 1
34401, 18, C:\...\18.jpg, 1

It is inserting 17.jpg's size to the next row when 18.jpg's size is: 32105 Any ideas??

Thanks,

Rupa

Rupa
Posting Yak Master

123 Posts

Posted - 2008-05-30 : 04:55:44

I've got it

If anyone wants to know, here it is:


For i As Integer = 0 To files.Length - 1              
   fileSize = files(i).Length             
      sqlReader.Read()             
      Output0Buffer.AddRow()             
      Output0Buffer.Output1 = CInt(fileSize)             
      Output0Buffer.IdNo = sqlReader.GetString(0) 
      Output0Buffer.Path = sqlReader.GetString(1)
      Output0Buffer.Type = sqlReader.GetString(2)         
Next

Thanks all for your help!!

Rupa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources