Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic Connection string


Dynamic Connection string

Author
Message
yousaf_kn
yousaf_kn
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 24
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.
bang.prashant
bang.prashant
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 221
enter data in sql tables and then take out for different destination

flat files ---> sql server tables---> different files.
sdevanny
sdevanny
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 343
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
SAMARDEEP
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 63
This may help you

http://blogs.conchango.com/jamiethomson/archive/2005/02/28/1085.aspx
yousaf_kn
yousaf_kn
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 24
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
Paul M. Corley
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 13398
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
yousaf_kn
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 24
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.
Attachments
pic.JPG (12 views, 8.00 KB)
sdevanny
sdevanny
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 343
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
Attachments
splitpic.JPG (16 views, 132.00 KB)
Paul M. Corley
Paul M. Corley
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 13398
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
yousaf_kn
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 24
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search