October 7, 2010 at 10:48 am
Hi
I have a table which stores the customer information.
tableCustomer (Customerid int,Companyid int,Fname Varchar(60) null ,Lastname Varchar(60) null ,MName Varchar(5) null ,Dob datetime.......crdate datetime,isactive bit )
I will be getting a file from different clients with different type of ways for eg
ClientA will give Customerid,Companyid,Fname,lName,MName... isactive
ClientB will give Customerid,Companyid,Fname,lName,........ isactive
here ClientB will not be giving Mname
What i have to do is i have to take that client file from the path and i have to insert the datas into tableCustomer it is done either by SSIS or by job or by some other possible way (still not decided) This is one case there might be some cases where client will not give all the datas.how to over come this
Thanks
Parthi
Thanks
Parthi
October 7, 2010 at 1:24 pm
Hello,
I had a similar situation. I used a SSIS package with a VB component script that did the job.
Below is the code. But in order to work, your files should have a header line with the name of the columns.
The input of the component script is only one field (a buffer) holding each record of the input file. The output are the individual fields. The component script splits the buffer into each fields.
Not sure if this suits your needs but here is the code.
Public Class ScriptMain
Inherits UserComponent
Dim header_line_flag As Boolean = True
Dim idx_customerid As Integer = 0
Dim idx_companyid As Integer = 0
Dim idx_fname As Integer = 0
Dim idx_iname As Integer = 0
Dim idx_mname As Integer = 0
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
Dim line As String
Dim cols As String() = Nothing
Dim c As String
Dim idx_col As Integer = 0
line = Row.Col0
cols = line.Split(",")
If header_line_flag Then
Header_line_flag = False
For Each c In cols
idx_col = idx_col + 1
Select Case c
Case "Customerid"
idx_customerid = idx_col
Case "Companyid"
idx_companyid= idx_col
Case "Fname"
idx_fname = idx_col
Case "lName"
idx_iname = idx_col
Case "MName"
idx_mname = idx_col
Case Else
MsgBox("col " + c + " not found")
End Select
Next
Else
For Each c In cols
idx_col = idx_col + 1
Select Case idx_col
Case idx_CustomerId
Row.OutCustomerId = c
Case idx_CompanyId
Row.OutCompanyId = c
Case idx_Fname
Row.OutFname = c
Case idx_IName
Row.OutIName = c
Case idx_MName
Row.MName = c
End Select
Next
End If
End Sub
Laurent
Laurent
October 7, 2010 at 3:13 pm
Hi
Thanks for your response but i need in pure SQL/SSIS not in vb code.
Thanks
Parthi
Thanks
Parthi
October 8, 2010 at 10:39 am
is this a problem for you to add a component script into your SSIS flow ? That's the way it is setup in my instance.
Laurent
October 8, 2010 at 10:53 am
lcohen-831390 (10/8/2010)
is this a problem for you to add a component script into your SSIS flow ? That's the way it is setup in my instance.
Hi
My aim to just to insert data to my table in the form of sql .if it is vb code,i dont know vb 😛 even if i get error i dont know to handle, thats why i dont want to go with vb code.it would be better in T-sql or in ssis. i think you got my point :smooooth:
Thanks
Parthi
Thanks
Parthi
October 8, 2010 at 12:18 pm
hi
Any one got the solution for the above it is urgent i am wating to do this. still now not able to start my work :w00t::w00t::w00t:
Thanks
Parthi
October 8, 2010 at 1:54 pm
so u dont want to insert that record from client B rite?
October 8, 2010 at 2:21 pm
kevin4u06 (10/8/2010)
so u dont want to insert that record from client B rite?
Hi
Not for ClientB alone ClientA,ClientC... and so on all the clients can not have same fields some fields may come for ClientA and not for B and C.
it can be said as dynamic input but static table :-D:-D:-D:-D.
Thanks
Parthi
Thanks
Parthi
October 8, 2010 at 3:11 pm
it will be my last try... I adapted my ssis vb component script to T-SQL.
This requires two T-SQL functions that I also give you in case you don't have them : one to split a line into columns based on a delimiter, the other to read from a flat file.
the way I understood your specs is that your customers give you some input file but the files they give you might not contain all the columns.
The code I provide will only work if the first record in each file contain column headers. I hope this is the case.
The beauty of this code is that the columns could be in any order in the input file, it always put them in the order they need to be in your destination table
--
-- The code of the main T-SQL program
--
set NOCOUNT ON
-- Buffer for each line from the input file
-- set the max length appropriately
Declare @LineBuffer varchar(1000)
-- Buffer for each column in each line
-- set the max length appropriately
Declare @Col as varchar(200)
-- Variables to hold the column name position in the file
Declare @idx_Customerid int
Declare @idx_Companyid Int
Declare @idx_fname Int
Declare @idx_iname Int
Declare @idx_mname Int
-- Variables to hold output columns
-- set the max length appropriately
Declare @Out_CustomerID varchar(50)
Declare @Out_CompanyId varchar(50)
Declare @Out_FName varchar(100)
Declare @Out_IName varchar(100)
Declare @Out_MName varchar(100)
-- work variables
Declare @idx_col int
Declare @HeaderFlag bit
Set @HeaderFlag = 1
-- read the input file and store the line into the buffer
Declare CustomerFileCursor CURSOR FOR
Select line from
Dbo.uftReadfileAsTable('c:\tmp','customerfile.txt')
open CustomerFileCursor
fetch next from CustomerFileCursor
into @LineBuffer
while @@FETCH_STATUS=0
begin
set @idx_col = 0
set @Out_CustomerId = ''
set @Out_CompanyId = ''
set @Out_FName = ''
set @Out_IName = ''
set @Out_MName = ''
-- processing the header line, we identify the position of each column
if @headerFlag=1
begin
set @headerFlag=0
-- split the header columns value delimited by ','
Declare ColCursor CURSOR for
Select ReturnString from
dbo.uftSplitString(@LineBuffer,',')
Open ColCursor
fetch next from ColCursor
into @Col
-- browse the header column values
while @@FETCH_STATUS=0
begin
set @idx_col = @idx_col + 1
if @col='CustomerId' set @idx_Customerid=@idx_Col
if @col='CompanyId' set @idx_CompanyId=@idx_Col
if @col='FName' set @idx_FName=@idx_Col
if @col='IName' set @idx_IName=@idx_Col
if @col='MName' set @idx_MName=@idx_Col
-- add other header columns here
fetch next from ColCursor
into @Col
end
end
-- processing the data line, we identify the position of each data column
else
begin
Declare ColCursor CURSOR for
Select ReturnString from
dbo.uftSplitString(@LineBuffer,',')
Open ColCursor
fetch next from ColCursor
into @Col
while @@FETCH_STATUS=0
begin
set @idx_col = @idx_col + 1
if @idx_col=@idx_CustomerId set @Out_CustomerId=@Col
if @idx_col=@idx_CompanyId set @Out_CompanyId=@Col
if @idx_col= @idx_FName set @Out_FName=@Col
if @idx_col=@idx_IName set @Out_IName=@Col
if @idx_col=@idx_MName set @Out_MName=@Col
-- add other data columns here ..
fetch next from ColCursor
into @Col
end
insert into dbo.tmp_customer_table
(customerid,
companyid,
fname,
iname,
mname)
select @out_CustomerId
,@out_CompanyId
,@out_FName
,@out_IName
,@out_MName
end
-- close the column cursor to prepare for the split of next line
close ColCursor
deallocate ColCursor
fetch next from CustomerFileCursor
into @LineBuffer
end
-- clean-up the customer file cursor
close CustomerFileCursor
deallocate CustomerFileCursor
-- END OF T-SQL Program
----
---- The code of the split function
----
Create FUNCTION [dbo].[uftSplitString]
(
@String VARCHAR(1000),
@Delim Char(1)
)
RETURNS
@SplitTable TABLE
(
ReturnString varchar(500)
) as
Begin
declare @pos int
declare @piece varchar(500)
-- Need to tack a delimiter onto the end of the input string if one doesn''t exist
if right(rtrim(@string),1) <> @Delim
set @string = @string + @Delim
set @pos = patindex('%'+@Delim+'%' , @string)
while @pos <> 0
begin
set @piece = left(@string, @pos - 1)
-- You have a piece of data, so insert it, print it, do whatever you want to with it.
insert into @SplitTable(ReturnString) select @piece
set @string = stuff(@string, 1, @pos, '')
set @pos = patindex('%'+@Delim+'%' , @string)
end
return
end
---
--- The code for the read from file
---
Create FUNCTION [dbo].[uftReadfileAsTable]
(
@Path VARCHAR(255),
@Filename VARCHAR(100)
)
RETURNS
@File TABLE
(
[LineNo] int identity(1,1),
line varchar(8000))
AS
BEGIN
DECLARE @objFileSystem int
,@objTextStream int,
@objErrorObject int,
@strErrorMessage Varchar(1000),
@Command varchar(1000),
@hr int,
@String VARCHAR(8000),
@YesOrNo INT
select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT
if @HR=0 Select @objErrorObject=@objFileSystem, @strErrorMessage='Opening file "'+@path+'\'+@filename+'"',@command=@path+'\'+@filename
if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'OpenTextFile'
, @objTextStream OUT, @command,1,false,0--for reading, FormatASCII
WHILE @hr=0
BEGIN
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='finding out if there is more to read in "'+@filename+'"'
if @HR=0 execute @hr = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT
IF @YesOrNo<>0 break
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='reading from the output file "'+@filename+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Readline', @String OUTPUT
INSERT INTO @file(line) SELECT @String
END
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='closing the output file "'+@filename+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'
if @hr<>0
begin
Declare
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int
EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
insert into @File(line) select @strErrorMessage
end
EXECUTE sp_OADestroy @objTextStream
-- Fill the table variable with the rows for your result set
RETURN
END
Laurent
October 8, 2010 at 3:24 pm
Hi Parti,
My best suggestion in that case just use the import/expoert wizard f you are using sql 2005 and above.
Select the file path --> map columns--> check datatypes-->finish
Go simple!
I think it is helpful.
or load table to temp table
Then
Update tablename
Set A.column1=temp.column1
where(condition)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply