Dynamic Connection string

  • Hi,

    I have Flat Source File having multiple records type.I want to split data into different flat Destinastion files.

    Each row have specific value to identifed the Destination File Name.But the number of files are two large and dynamically are entered new ones. So i can not hard code the destination files.

    Here is the in the diagram

    Flat File Source ---------------->ScriptComponent--------------->Dynamic File Destination

    I created a User defined variable uses component script which sets the dynamic file connection string but i execute the SSIS it does not take the value from the variable and store in the file location with the real name.

    Here is the code:

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    Dim var As IDTSVariables90

    Me.VariableDispenser.LockOneForWrite("User::CurrenFile", var)

    var("User::CurrenFile").Value = Row.substring(198,2)

    var.Unlock()

    End Sub

    and here is the expression which it should be update for each row.It Updated the variable but it not uses in the expression.

    ConnectionString = "c:\\test\\region_" + Row.Region + ".txt"

    IT JUST COPIED DATA INTO FILE NAME c:\test\region_.txt

    If Any one can help me i am very thanks for him.

  • enter data in sql tables and then take out for different destination

    flat files ---> sql server tables---> different files.

  • try without user::. just use your variable name.

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    'Initialise the variable

    Dim vars As IDTSVariables90

    'Lock a variable so that we can write to it

    Me.VariableDispenser.LockOneForWrite("varFoo", vars)

    'Write the value to the variable

    vars(0).Value = "Foo"

    'Release the lock

    vars.Unlock()

    End Sub

    hope this helps

  • It still not working.It creates only a single file named region_.txt

    It is not uses the user variable.

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    Dim var As IDTSVariables90

    Me.VariableDispenser.LockOneForWrite("CurrentRegion", var)

    var(0).Value = Row.Region

    var.Unlock()

    End Sub

  • Since SSIS moves data through the process in Buffer pages you can't set the output of a row to have a dynamic destination within the same buffer. All rows in a buffer will go to the same destination. If you need to output rows to different destinations then you somehow have to take all rows for that destination and move them together. @ ways for example are

    1) A conditional split task with logic to output records that go to the same destination together. Say you wanted to output files based on state then you would have 50 Conditions in your Conditional Split with 5 outputs. Each of these outputs would then map to a specific destination.

    2) Run your data flow in a Loop and bring only the needed records into the dataflow.

    a)SQL Task that would select the distinct values for the column in question

    b)Loop through the SQL return set by distinct value

    c)SQL query for data source stored in a variable

    d)Second SQL variable that is Evaluated as an expression The expression would bring

    your criteria and qury together.

    e)Your source adapter would get its source query from the second variable.

  • Sorry You cannot understand my problem.

    I have a large flat file source and i want to transfer data into multiple destination flat files.But destination file names come with in the text of each row data in the source file.

    So I read the data from source file and called a script component which identified the file name and setting the variable e.g "User::CurrentRegion" which is the part of conectionStringExpression Like this

    "C:\\test\\region_" +@[User::CurrentRegion] + ".txt"

    But when i execute the SSIS is setting up the values of the variable but it is not setting the expression of connection string of Destination File.

    I cannot find out the actual Reason for this problem.

    So at the end i brief my problem again i.e

    i read a row from source file and put into his destination file without using the condional splitting.

  • after reading your problem again today and reading paul's response, i see i misinterpretted your need. i agree with paul's response. paul states correctly:

    "Since SSIS moves data through the process in Buffer pages you can't set the output of a row to have a dynamic destination within the same buffer. All rows in a buffer will go to the same destination. If you need to output rows to different destinations then you somehow have to take all rows for that destination and move them together. @ ways for example are

    1) A conditional split task with logic to output records that go to the same destination together. Say you wanted to output files based on state then you would have 50 Conditions in your Conditional Split with 5 outputs. Each of these outputs would then map to a specific destination..."

    the conditional split can direct the individual rows in the buffer to different flows based on the contents of that row. then a script component in each resulting flow can dynamically generate the file name for the destination in that flow. see pic for visual representation

  • Third possible option is to write your destination using a custom script task. Then you could build everything you are doing in your current script into your destination script and possibly reduce the time. Not saying this is possible or even easy but might work.

  • sdevanny you are right but this seems to be static destination by using conditional splitting.But in my case i have to keep the destination generic because new destination might with will come in future so this technique is going fail there.

  • You have to Remember SSIS is an ETL enviroment which is designed to allow you to transform data through a known process. SSIS is not a programming enviroment that allows in process changes to be made. If you truely need something that dynamic then you should probably look at a C# service and not SSIS. This is not to say that some obstecales can't be overcome but the type of flexibility you believe should exist simply doesn't in the way you are trying.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply