Dynamic Connection string

Question

Post reply

Dynamic Connection string

yousaf_kn

SSC Journeyman

Points: 94
More actions
September 11, 2008 at 3:53 pm

#123557

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.

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

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

bang.prashant Ten Centuries Points: 1100 More actions · Answer 1

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

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

sdevanny Say Hey Kid Points: 698 More actions · Answer 2

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

SAMARDEEP Right there with Babe Points: 727 More actions · Answer 3

This may help you

http://blogs.conchango.com/jamiethomson/archive/2005/02/28/1085.aspx

yousaf_kn SSC Journeyman Points: 94 More actions · Answer 4

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

Paul M. Corley Old Hand Points: 365 More actions · Answer 5

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.

yousaf_kn SSC Journeyman Points: 94 More actions · Answer 6

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.

sdevanny Say Hey Kid Points: 698 More actions · Answer 7

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

Paul M. Corley Old Hand Points: 365 More actions · Answer 8

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.

yousaf_kn SSC Journeyman Points: 94 More actions · Answer 9

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.

Paul M. Corley Old Hand Points: 365 More actions · Answer 10

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.