Thursday, May 26, 2011

Column Name into variable to rename file

Today I came across a question in EE:


The person gets a unique file everyday, file name in the format 1234_Test.txt and the first four digits changes everyday.  Also need to place the output file and rename it based on a column value.
For ex: If a column named code is 2, then my output file should be 2_1234_TEST.txt


So it could be done by using a "File Watcher Task" to check for the file when it's available and put the file name into a variable.


Or using SSIS native task, using a "Foreah Loop" task to get the file name into variable, use a "script task" into the data flow to get the column value into another variable.


The a 3rd variable to join both of them and the destination folder.

The package could be downloaded from http://www.box.net/shared/lx4j79a39z
steps are:


3 variables -  type (STRING)


 Foreach Loop config


 File system task config
 Expression on the variable


 Clearer View



Data Flow Task
Script component config





VB script



SSIS Selective Rows - Using Row Sampling Task

I came across a question today about how to process only the first 10000 rows from a flat file.
It could be done by 3 ways, either using the "RowNumber transformation" from http://sqlis.com/post/Row-Number-Transformation.aspx .

or by using a "Script task" which I'll blog about it later or by using the "Row Sampling" Task.

"Row Sampling" would be the easiest way if you need to grab the FIRST 1000 or the FIRST any number.

But using a "Script Task" or the "RowNumber transformation" will grab any selective rows, such as "Grab rows 150 to 400".

Practice  package can be downloaded from : http://www.box.net/shared/ajzt5nccue

I'm using here the Data Generator source Task which could be downloaded for free from http://sqlis.com/post/Data-Generator-Source-Adapter.aspx
just to generate 5000 rows, you can use a flat file as normal.

 Row Sampling configuration.

 And the results...

Hope that helps.


Index Creation and Modification Date.

Ever wondered when that index was created !

Select  s.name, t.name, t.create_date, t.modify_date,i.name, c.name
From sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id
        inner join sys.columns c on c.object_id = t.object_id and
                ic.column_id = c.column_id
Where i.index_id > 0   
and i.type in (1, 2) -- clustered & nonclustered only
and i.is_primary_key = 0 -- do not include PK indexes
and i.is_unique_constraint = 0 -- do not include UQ
and i.is_disabled = 0
and i.is_hypothetical = 0
and ic.key_ordinal > 0

Order by ic.key_ordinal


well I'm not sure what's the best way, or if there's any way to get this info.
but you could get the table's creation date and use that as a reference....please note that not in every case you build your index the same date as your table!

also as one poster in a forum advised to use the statistics creation date!

http://stackoverflow.com/questions/7579932/get-index-creation-date-from-sql-server 

Tuesday, May 17, 2011

SSIS Selective Rows Count - Using Script Component

I came across a question today about how to process only the first 10000 rows from a flat file.
It could be done by 3 ways, either using the "RowNumber transformation" from http://sqlis.com/post/Row-Number-Transformation.aspx .

or by using a "Script task" which I'll blog about it later or by using the "Row Sampling" Task.

"Row Sampling" would be the easiest way if you need to grab the FIRST 1000 or the FIRST any number.

But using a "Script Task" or the "RowNumber transformation" will grab any selective rows, such as "Grab rows 150 to 400".

Practice  package can be downloaded from : http://www.box.net/shared/ajzt5nccue
I'm using here the Data Generator source Task which could be downloaded for free from http://sqlis.com/post/Data-Generator-Source-Adapter.aspx
just to generate 5000 rows, you can use a flat file as normal.


I'm here selecting only the first 1000 rows or whatever number, using a script task, not using any custom tasks.



Same concept, I'm using a Generator source to give me 5000 rows, which I'll filter using a conditional split.

Add a script component (Transformation) and add an output row (Integer)


 Edit the script, I'm using VB.net here.


Conditional split
You can here change the selection to be from row 500 to 750, you would use:
ORowNumber <= 750  && ORowNumber > 500


and the results.....



SSIS Selective Rows Count

I came across a question today about how to process only the first 10000 rows from a flat file.
It could be done by 3 ways, either using the "RowNumber transformation" from http://sqlis.com/post/Row-Number-Transformation.aspx .

or by using a "Script task" which I'll blog about it later or by using the "Row Sampling" Task.

"Row Sampling" would be the easiest way if you need to grab the FIRST 1000 or the FIRST any number.

But using a "Script Task" or the "RowNumber transformation" will grab any selective rows, such as "Grab rows 150 to 400".

Practice  package can be downloaded from : http://www.box.net/shared/ajzt5nccue

I'm using here the Data Generator source Task which could be downloaded for free from http://sqlis.com/post/Data-Generator-Source-Adapter.aspx
just to generate 5000 rows, you can use a flat file as normal.

 RowNumber Transformation, adds a column named 'RowNumber' or any name you name it here.


 Conditional Split to split the first 1000 rows -- RowNumber <= 1000

 The results.
Hope that helps someone.