SSIS Flat File Processing Help

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » SSIS Flat File Processing Help

15 posts, Page 1 of 2

1 2 »»

SSIS Flat File Processing Help

Rate Topic

Display Mode

Topic Options

Author

Message

darkman2020

Posted Friday, March 21, 2008 4:06 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 20, 2011 12:00 AM
Points: 16, Visits: 72

Please i need help in creating an ssis package to read multiple files in a Directory ex:

1. I need to iterate through a list of files in *.txt format but i need to add the filename that has been read into a database.

Please Can someone help me you can e-mail me at pcmaxgh@yahoo.com

Post #472724

MarkusB

Posted Friday, March 21, 2008 4:22 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 4,222, Visits: 3,881

About how to loop though all files in directory you find a good example at http://www.sqlis.com/55.aspx.
About writing the filenames to a database simply load the names into a variable and then insert it to your DB.

Markus Bohse

Post #472727

pritesh2205

Posted Friday, March 21, 2008 5:05 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 3:36 PM
Points: 38, Visits: 392

Heres what you can do :
1. use a for each loop container, in the "collection" tab ,use ForEach File Enumerator" option.
2. On the same tab you will have "expressions" , within that assign a user defined variable to FileNameRetrival property.
3. Use a execute sql task to intsert the file name stored in this variable into the database.
you can do this by passing the user defined variable as parameter in the sql query.

Post #472733

Sri8143

Posted Friday, March 21, 2008 7:27 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 12:59 AM
Points: 358, Visits: 2,119

hi...i am unable to understand where can i pass the variable in the execute sql task..

i shall set the for each loop and give the properties in that create a variable and now i shall include dataflow task in that but where should i give the variable in the query and where should i connect execute sql task and whats the main use of that please let me know i have used other way to display the path name but please let meknow how this works

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

Post #472779

pritesh2205

Posted Sunday, March 23, 2008 12:22 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 3:36 PM
Points: 38, Visits: 392

Hi,
I assume that you are able to set up the for each loop container successfully and are able to read the file names into variable. I have attached a wordpad file with some screenshots which shows how your for each loop looks like and where will the "EXECUTE SQL " task should go.
For pasing the filename variable to the execute sql task you need to do the following:
1. configure the "general" tab of the execute sql task with the connection property and the sql statement property.
2 the sql statement will look like : INSERT INTO tblFilename (col_filename) VALUES (?)
note : the '?' represents the parameter value that will be passed into the sql statement, in your case this will be the filename
3 configure the "parameter mapping" tab and add the variable that stores the filename from the for each loop.
note : the sequence in which you add the variables here map to the sequence of '?' in the sql statement

You are done , and shoud be able to insert the filename into the table that you want to.

you can get more info about how to pass parameters into the execute sql task inthe below mentioned link :
http://msdn2.microsoft.com/en-us/library/ms141003.aspx

Also , i have attached the sceenshots to help you understand the process. Hope this helps.

Pritesh
MCTS

Post Attachments

screenshots.zip (146 views, 387.35 KB)

Post #473288

darkman2020

Posted Sunday, March 23, 2008 8:32 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 20, 2011 12:00 AM
Points: 16, Visits: 72

ok thanks to all of you who responded. However; what i want to know is

After i insert the file name and read the content of the file into my database:
How do i make sure that the next time the package is rand that the same files are not read.

only the fileName that are not in my table of filenames should be read.

Post #473304

pritesh2205

Posted Sunday, March 23, 2008 9:47 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 3:36 PM
Points: 38, Visits: 392

There can be a number of ways to achieve this, the basic idea should be to lookup the incoming filename against the ones that are stored in the table and accordingly configure the execute sql task to fire.
Another way can be to use a script component , instead of the execute SQL task as this will make your work easy.
you can write a code in the script component to query the table to check if the incoming filename exists , if it does then do nothing , else fire an insert statement to write the filename in the table.

Post #473341

darkman2020

Posted Monday, March 24, 2008 2:01 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 20, 2011 12:00 AM
Points: 16, Visits: 72

Pritesh2205

Please can you give an example of a script task i will be very grateful of you.

Post #473374

pritesh2205

Posted Monday, March 24, 2008 3:27 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 3:36 PM
Points: 38, Visits: 392

You can use the below written script . Also , you can use the SQL Query in the execute sql task as well , i didnt have the time to test it with the execute sql task . But the code works well in the script task.

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer
Imports System.Data.OleDb

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()
'
' Add your code here

'Access the variable which holds the filename and store it into a local variable for further use
Dim fileName As String = CStr(Dts.Variables("Variable").Value)

'Create a new connection
Dim conn As New OleDbConnection
Dim oledbCommand As New OleDbCommand

'Create a new connection string, it is hard coded here , you can make it variable as well
Dim queryString As String
conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SSIS_Lab;Data Source=PNEITS1C11066D\SQL2005"

conn.Open()

'Write the query and pass the filename as a variable
queryString = "IF NOT EXISTS (SELECT filename FROM Table_1 WHERE filename = '" & fileName & "')" & _
" INSERT INTO Table_1 VALUES ('" & fileName & "')"
oledbCommand = New OleDbCommand(queryString, conn)
oledbCommand.ExecuteNonQuery()

'Close the connection
conn.Close()

Dts.TaskResult = Dts.Results.Success
End Sub

End Class

Post #473386

darkman2020

Posted Monday, March 24, 2008 3:34 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 20, 2011 12:00 AM
Points: 16, Visits: 72

Thanks man you rock!!!

Post #473387

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions