Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Dynamic Connection string Expand / Collapse
Author
Message
Posted Thursday, September 11, 2008 3:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 18, 2009 3:34 PM
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.
Post #568181
Posted Thursday, September 11, 2008 4:21 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 10, 2008 5:34 PM
Points: 54, Visits: 221
enter data in sql tables and then take out for different destination

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

Post #568184
Posted Thursday, September 11, 2008 4:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:03 PM
Points: 32, Visits: 307
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
Post #568188
Posted Friday, September 12, 2008 4:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 15, 2012 3:02 AM
Points: 31, Visits: 63
This may help you

http://blogs.conchango.com/jamiethomson/archive/2005/02/28/1085.aspx
Post #568372
Posted Friday, September 12, 2008 7:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 18, 2009 3:34 PM
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
Post #568468
Posted Friday, September 12, 2008 7:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 8, 2013 10:47 AM
Points: 63, Visits: 13,394
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.
Post #568510
Posted Friday, September 12, 2008 8:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 18, 2009 3:34 PM
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.


  Post Attachments 
pic.JPG (6 views, 8.26 KB)
Post #568527
Posted Friday, September 12, 2008 9:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:03 PM
Points: 32, Visits: 307
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


  Post Attachments 
splitpic.JPG (10 views, 132.80 KB)
Post #568634
Posted Friday, September 12, 2008 9:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 8, 2013 10:47 AM
Points: 63, Visits: 13,394
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.
Post #568667
Posted Friday, September 12, 2008 2:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 18, 2009 3:34 PM
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.


Post #568928
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse