December 3, 2007 at 6:52 am
Hi ,
This is my first post in this site, i have a problem in a package which am creating using ssis. Actually i want to import multiple flat files from a specified folder to a MS Access database file ie each file should have separate table in the database.I just started by creating a for-each loop container in the control flow part of the package and added my flat file source and oledb destination in the data flow. I am using a variable to fetch the flat file name in each execution of the loop and associate that variable to the flat file source.The flat file source is connected to a Data Conversion flow item in which am using to convert some data to the destination type , am also added a derived column (data flow) item to generate some more columns using expressions from the input columns and this is connected to the oledb destinaton.So the dataflow diagram is like one given below
Flat File Source -> Data Conversion -> Derived Column -> OLEDB Destination.
I am using "table name or view name variable" in OLEDB destination to create dynamic tables.
I am using a Script task inside the for each container to assign the name of the dynamic table which programmitacaly generating from the input file name.Please find the code below.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
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()
Dim strtablename As String
strtablename = Dts.Variables("VarTableName").Value.ToString()
Dts.Variables("VarTableQuery").Value = "select * into " & strtablename & " from structure "
MsgBox(Dts.Variables("VarTableQuery").Value.ToString)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Note: I have assigned the name of the physical table
(i have just created this blank table in the mdb file) "structure" to the "VarTableQuery" variable which is given as table name variable in the design mode (OLEDB destination Editor).
while executing this package am getting an Error Message, please find the error Given Below.
[OLE DB Destination [463]] Error: An OLE DB error has occurred. Error code: 0x80040E37.
[OLE DB Destination [463]] Error: Opening a rowset for "select * into ATH00888 from structure " failed. Check that the object exists in the database.
Please help me to solve this problem, i know am not an expert in SSIS because i have only little experience with this product.
I hope you people can help me to sort out this problem.
Regards,
Saji Krishnan
December 3, 2007 at 8:48 am
Check if OLE DB Destination has valid connection to destination server and are those tables exists on the destination database.
December 3, 2007 at 10:16 pm
Hi Marek,
The connection is valid and the "structure" table exists in the .mdb database file. Actualy i am getting data imported when i am using the table directly in the OLEDB destination Editor. But in that case the data from all flat files are coming to the single table.
Regards,
Saji Krishnan
December 4, 2007 at 4:21 am
And ATH00888 exists too?
December 4, 2007 at 7:00 am
Hi,
This is the name of the dynamic table to be created in the destination (.mdb file), it is generating from actual flat file path, am using the following expression to genarate this.
SUBSTRING(@VarFileName,20,8) this expression is given in the expression property of the variable @varTableName
Actualy am getting the full path + file name of the inputfile into the variable @varFileName becuase am using this variable in the for each loop container and using the above expression am able to get the actual filename (ie name of the table to be created in the .mdb file).
Regards,
Saji Krishnan
December 4, 2007 at 9:18 am
ATH00888 and structure tables has the same structure and even column names are on the same positions in both tables?
December 5, 2007 at 1:17 am
Hi,
Yes, as i mentioned ATH0888 table has to be created in runtime using the following query
"select * into " & strtablename & " from structure " (here strtablename holds "ATH0888")
and this process will repeat for each flat file in the source folder using for each container loop.
now my problem is that this query is not working in runtime.
Regards,
Saji Krishnan
December 5, 2007 at 2:33 am
Yes, right, this table is create by statement "select * into...". I why this error occur. Your select query for the destination does not return rowset at all.
Destination query must be in form "select ... from ...". So you must rethink the way your package works.
December 5, 2007 at 2:34 am
Powichrowski Marek (12/5/2007)
Yes, right, this table is create by statement "select * into...". I why this error occur. Your select query for the destination does not return rowset at all.Destination query must be in form "select ... from ...". So you must rethink the way your package works.
In other words: destination table must exist before using it by destination data flow component.
December 5, 2007 at 9:10 am
Hi,
Can i use "create table" query in place of "select * into", becasue i just want create a table in runtime. Please let me know if you have any solution for this.
I cant understand how the "table or view name variable" option works on OLEDB Destination. becuase in the documentation it says we can create tables dynamically but i dont know practically how we can.
I hope you can help me on this.
Regards,
Saji Krishnan
December 5, 2007 at 9:47 am
Use script component (transformation type) to create this table "on the fly". Only one task for this component will be executing SqlCommand which creates this table. Output path from this component should be directed to destination component. Destination query will be "select * from ".
June 25, 2008 at 1:23 pm
Hello Saji,
Did you ever find a solution to this problem? I have the exact same situation which I'm having trouble with. If you have figured it out, I would appreciate any help you could give.
Thank you,
Gerald
March 12, 2009 at 12:05 am
hi saji,
I got stuck in the similar issue of creating tables dynamically based on csv file input.Is it possible to add file names dynamically to OLEDB destination or not.Because we have to map the column names from the csv file to the table which we going to be created..Any ideas share mates
vijay
June 12, 2009 at 7:09 am
Any solution for this?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply