Dynamic passing Delimiter???? - SQL Server Forums

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)

Dynamic passing Delimiter????

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-07-10 : 03:06:38

Hi All,

It is possible to pass dynamic delimiter to SSIS package for import

Suppose i want to import text file to DB and my text file having values like
1;Test1;DB1
2|Test1|DB1
3|Test1|DB1
4|Test1|DB1

everything is working fine when delimiter is ; but now we are getting delimiter ; as well as |

so my point is wheather it is possible for me to pass dynamic delimiter

suppose i m getting rows in text more of | then i pass | to SSIS and more rows processed and vice versa for ;

please help me out

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-07-10 : 04:32:13

No, sort your original file so the delimiter is constant. I do not see why you would have a different delimiter in the same file.

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-07-10 : 04:54:58

Sometimes we are getting out of 25 rows one row has delimiter as | and rest of row getting ;
In that case through application we can set delimiter as | and process that row

for that what i defined one veriable @Delimiter and set it under "Flat File Connection Manager Editor">> Column delimiter
as @Delimiter

but when from command prompt when I entered value like

C:\Test Project\SSIS Packages\Integration Services Project1\bin>DTExec /FILE delimiterTest.dtsx /SET \Package.Variables[Path].Value;C:\DelimiterTestFile.txt /SET \Package.Variables[Delimiter].Value;Vertical Bar {|}

but here I am getting error like

'}' is not recognized as an internal or external command,
operable program or batch file.

What else I can do to resolve this

T.I.A

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-07-10 : 05:54:43

Where are you getting the file from? Do they not have a standard set for delimiting the file?

The best place to sort an issue like this is the source of your file.

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-07-10 : 08:47:33

Hi

Actually these source file are getting from client and we dont know what exactly delimiter is so we dont want it to hard core so i m doing all these stuff

I am running my front end application along with SSIS and passing @Delimiter variable to package but it will not process any rows :(

Kinly tell me where I m getting wrong

T.I.A

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-07-10 : 09:01:50

It might be easier to split the file based on delimiter so you don't have to pass anything.

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-07-10 : 09:15:48

Can you please elaborate..

my text file that client has send me contains all delimiter as ;
or some times |

so by looking this can you please elaborate

T.I.A

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-07-10 : 10:00:21

Try this method:
http://www.sql-server-performance.com/articles/dba/import_text_files_ssis_p1.aspx

Here's your Script Component code:


Public Class ScriptMain
    Inherits UserComponent


    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        Dim strRow As String
        Dim strColSeperator As String
        Dim rowValues As String()
        strRow = Row.Line.ToString()
        If strRow.Contains(";") Then
            strColSeperator = (";")
        ElseIf strRow.Contains("|") Then
            strColSeperator = "|"
        End If

        rowValues = Row.Line.Split(CChar(strColSeperator))
        Row.Code = rowValues.GetValue(0).ToString()
        Row.Description = rowValues.GetValue(1).ToString()

    End Sub


End Class

I tested it and works fine. Good luck!

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-07-10 : 11:05:35

Hi

I used this link thanks i m close to it but i m getting error while processing

like

[Script Component [459]] Error: Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)

Actually i have four columns that need to import from this text file

i wrote like

Dim strRow As String
Dim strColSeperator As String
Dim rowValues As String()
strRow = Row.Line.ToString()
If strRow.Contains(",") Then
strColSeperator = (",")
ElseIf strRow.Contains(";") Then
strColSeperator = ";"
End If

rowValues = Row.Line.Split(CChar(strColSeperator))
Row.Charge = rowValues.GetValue(0).ToString()
Row.Code = rowValues.GetValue(1).ToString()
Row.Description = rowValues.GetValue(2).ToString()
Row.Fee = rowValues.GetValue(3).ToString()

my table structure is

CREATE TABLE [dbo].[Charge](
[ID] [numeric](3, 0) NOT NULL,
[Charge] [varchar](10) COLLATE Latin1_General_BIN NOT NULL,
[Code] [varchar](15) COLLATE Latin1_General_BIN NOT NULL,
[Description] [varchar](50) COLLATE Latin1_General_BIN NULL,
[Fee] [money] NULL
) ON [PRIMARY]

one column i need to add externally i.e. ID to this text file that i managed by derived column

but what @ that error says???

T.I.A

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-07-10 : 11:19:44

Got it thanks for ur kind help

:)

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-07-10 : 11:27:25

Well great job and a bit too late; but here's what I came up anyways:


    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        Dim strRow As String
        Dim strColSeperator As String
        Dim rowValues As String()
        strRow = Row.Line.ToString()
        If strRow.Contains(";") Then
            strColSeperator = (";")
        ElseIf strRow.Contains("|") Then
            strColSeperator = "|"
        End If

        rowValues = Row.Line.Split(CChar(strColSeperator))
        Row.Charge = rowValues.GetValue(0).ToString()
        Row.Code = rowValues.GetValue(1).ToString()
        Row.Description = rowValues.GetValue(2).ToString()
        Row.Fee = CDec(rowValues.GetValue(3).ToString())

    End Sub

dpayonk
Starting Member

3 Posts

Posted - 2008-07-23 : 18:59:32

Pertinent to This Question, I would like to set the row delimiter column dynamically through expressions, but am not coming across the right syntax. I am not sure how to assign the data in the row delimiter and even if this can get done. I would like to avoid the Script Task as the performance is of critical nature. Basically, I can receive data from Unix or Windows Systems and want to be able dynamically configure based off a variable.

Dennis Payonk

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-07-24 : 08:09:38

How much data are we talking about? What syntax have you tried and how exactly are you setting it up? I'd be interested to know if there's a solution besides Script Task.

dpayonk
Starting Member

3 Posts

Posted - 2008-08-08 : 16:16:56

I am talking about tens of different files ranging from a megabyte to 200 megabytes. Basically, our ETL process needs to support Unix and Windows types files. Obviously, we could just use line feeds and try and do a replace in a view once we get the data staged, but there are expressions available in Flat File Connection string to make this change dynamically.

Dennis Payonk

dpayonk
Starting Member

3 Posts

Posted - 2008-08-08 : 16:17:55

quote:
Originally posted by dpayonk

I am talking about tens of different files ranging from a megabyte to 200 megabytes. Basically, our ETL process needs to support Unix and Windows types files. Obviously, we could just use line feeds and try and do a replace in a view once we get the data staged, but there are expressions available in Flat File Connection string to make this change dynamically.

Dennis Payonk

I am using the syntax char(13)+char(10)

Dennis Payonk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources