Scripting component.

Hi Guys,

I need to generate a file using script component. I'm taking some data from an oledb source and then i would need to modify the fields, generate new fields and then generate the flat file.

I don't have any problems with this but the file i need to replicate would need to have a head record and a trail record which doesn't need to be duplicated.

Now when i generate the file and put anything in "Public Overrides Sub Input0_ProcessInputRow" it will work but it will generate the header record again and again as well.

Any quick help would be really appreciated.

TA

Gemma

Gemma,

Any reason for using the script component to create the file? Can you just use the script to populate the pipeline and use a flat file destination for the file? If so, I recently described how you would go about creating a flat file with header and trailing records.. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2168163&SiteID=1

|||

Hi Eric,

I'm just combinging the fields, padding the fields with zero and doing some cleaning of the data as well.

Is there any other way? I've checked your post but I don't know how to add something from the database in the header.

Yes you can enter any expressions but i need the value from the table.

Can you help me do that?

TA
Gemma

|||

Add a boolean flag at the class level and default it to True, Then in the ProcessInput check the flag. If it is True, write the header line and change the flag to false.

There's an example of that approach in the script I posted here - http://agilebi.com/cs/blogs/jwelch/archive/2007/09/14/dynamically-pivoting-rows-to-columns.aspx. The example is not what you were asking about, but the script illustrates the technique.

Script Task scripting help

Hi all,
I use oledb connection in the script just like as follows:

msConnStr = "Provider=SQLOLEDB;SERVER=" & msServer & ";DATABASE=" & msDb
msConn = New OleDbConnection(msConnStr)
msConn.Open()
ds = New DataSet
msData = New OleDbDataAdapter(msSqlStr, msConn)
rows = msData.Fill(ds, "TableName")

This command is working fine, I just need to ask if there is a method to do retry on the connection if the connection cannot be opened for reasons like server is temporary down? instead of just make the script task a failure. Thanks in advance.
Daren
There is no magic method on any of the opbjects, but you can easily use the Try Catch syntax to catch the rror, and warp tis within a loop to retry n times. Perhaps add a delay in the loop as well to pause between attempts. if successfull, just break out of the loop.

Script task and OLEDB destination Performance

Hi fellows,

Sorry to disturb but just a question. I have a package which extracts all the records from table A and update to table B. These records may range from 100,000 to 500,000 records.

So my question is that whether is it more feasibile/efficient to use script task to pump all the rows into table B from table A or use OLEDB destionation using sql command. Which is more efficient and help me increase my package performance? Thanks again.

Regards,

Ken

If they are inserts, I'd use the OLEDB Destination to insert directly into the target table. If they are updates, use an OLEDB Destination to write the data to a temp table, then use a Execute SQL task after the data flow to issue a batch update.

sql