March 21, 2008 at 4:06 am
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
March 21, 2008 at 4:22 am
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.
[font="Verdana"]Markus Bohse[/font]
March 21, 2008 at 5:05 am
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.
March 21, 2008 at 7:27 am
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
March 23, 2008 at 12:22 am
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
March 23, 2008 at 8:32 am
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.
March 23, 2008 at 9:47 pm
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.
March 24, 2008 at 2:01 am
Pritesh2205
Please can you give an example of a script task i will be very grateful of you.
March 24, 2008 at 3:27 am
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
March 24, 2008 at 3:34 am
Thanks man you rock!!!
April 17, 2008 at 11:24 am
Grasshopper,
I created a package with same rules you mentioned. I created the FOREACH LOOP Container with a variable parameter. I then inserted the EXECUTE SQL TASK inside the FOREACH LOOP Container. Within the Execute SQL Task, I created the parameter mapping from the FOREACH LOOP Container variable parameter to a new parameter.
When executing the package, I get the error:
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "insert into excelfiles (ExcelFileName) values (?)" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
I can't see anything wrong.
Any sugguestions?
Thanks
Javier
April 17, 2008 at 11:38 am
what did you set in the result set property none or anything else ???
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
April 17, 2008 at 2:53 pm
The ResultSet property is set to: None.
I even tried to put the sql statement in the Expression as a "SqlStatementsource".
"insert into excelfiles (ExcelFileName) values" + @[User::NewParameterName]
Still no luck.
Thanks!
April 17, 2008 at 3:34 pm
I see my problem. The statement is incorrect. It should be:
"insert into excelfiles (ExcelFileName) values (" +"'"+ @[User::FileWeJustFound] + "'" + ")"
I forgot the include the "( )" and quotes around the variable. Simple things I overlooked.
Javier
December 16, 2011 at 1:20 pm
I need to load 5 flat files into a table with the corresponding file name in the last column. have used your process but i have some questions. im totally new to SSIS
in the script task i have added ur script but im not able to achieve the results. can you give me some direction on how to add the filename
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply