found some useful sql tips

·      Insert multiple rows

insert into tablename (col1, col2)

select ‘this1’, ‘that1’

union all

select ‘this2’, ‘that2’

union all

select ‘this3’, ‘that3’

union all

select ‘this4’, ‘that4’

From Personal Notes of Pinal Dave

·         Conditional Where Clause

select *

from   dbo.tblConditionalWhereClause

where  ((Businessdate = @BusinessDate) and (_NODEID_ = @nodeID))

              or     ((@BusinessDate is null) and (_NODEID_ = @nodeID))

              or     ((@nodeID is null) and (Businessdate = @BusinessDate))

              or  ((@BusinessDate is null) and (@nodeID is null))

logical where clause (Not A) or B means if A then B

create a child package and pass variables with this Execute Package Task

Now we need to create a child package.

1) create a new package and create new package variables

2) right click on the design screen and click Package Configurations

3) On the pop up window, enable package configurations and click Add...

4) click next and change Configuration type to Parent package variable and type a Parent variable

5) on the next screen, expand Variables and variable name(this time "logFileName") and expand its properties and click Value and click Next

6) then click next and you will see a new configuration on the Package Configurations Organizer window

7) repeat this if you have more than one variable to pass from parent to child package.
(In my project, I had to pass 3 variables - LogFileName, TxtFileName, and LogSource)

8) then build this package.

9) go back to parent package and modify Execute Package Task 

10) Location can be SQL server or file system depending on where you built your child package. 

SSIS 2008 - flat file source look up other flat file with foreach loop

I don't know what this title should be but here's simplified objectives


1. Pull from a specified directory all files matching a format of “007C0*.log
2. From each error log, you must retrieve the line number from the contents of the error log and the error message
3. Using the file name, pull from  another directory the file that has the same base name, but with a “txt” extension
4. Using the Line Number from the error log, pull the line data from the text file.
5. Use both the information from the error log and the upload file to populate the table

log file format (007C0*.log)

text file format (logfilename_datetimestamp.txt)

Objective 1 and 2 can be done in one foreach loop.
For objective 3, I needed one more foreach loop since text file consists of full log file name and datetimestamp.
For objective 4, I had to put a data into somewhere but I was asked not to put in our database so I had to use cache transform. That means, I had to create two packages - parent and child since cache transform cannot be placed in a for loop(connection problem).

So lets start!

1) create five variables.

2) put a foreach loop container and double click to open up an editor.

click Collection and click + sign to expand Expressions and click ... to put new values

3) Pick Directory and FileSpec  from Property column drop down menu.

and Click ... to add expression.

(In this example, FileSpec is a filename and  Directory is the logfilesource.)

4) click Variable Mappings tab and pick [User::LogFileName] and close an editor.

5) add a Script Task inside Foreach Loop Container. (need to get a log file name without a path and file extension)

Click ReadWriteVariables row and add [User::LogFileName] and click Edit Script

5.1) simple code

         public void Main()

        {   

            String str = Dts.Variables["User::LogFileName"].Value.ToString();            

            String logfilename = str.Substring(3, str.IndexOf(".")-3);

            Dts.Variables["User::LogFileName"].Value = logfilename;            

            Dts.TaskResult = (int)ScriptResults.Success;

        }

* the reason why I used "3"s in str.substring is that the path is "C:\". If your path(directory) is like "C:\Users\asung\Documents\Visual Studio 2008\Projects\Integration Services Project3" then you need to use a different number. 

6) add a new Foreach Loop Container inside and set it up like the one before

But this time FileSpec Expression should be @[User::LogFileName] + "*.txt"

7) click Variable Mappings tab and pick [User::TxtFileName] and close an editor.

8) add a Script Task inside an inner Foreach Loop Container and repeat step 5 with [User::TxtFileName]

8.1) just like 5.1

        public void Main()

        {

            String str = Dts.Variables["User::TxtFileName"].Value.ToString();

            Dts.Variables["User::TxtFileName"].Value = str.Substring(11);

            Dts.TaskResult = (int)ScriptResults.Success;

        }

9) add an Execute Package Task

Next is to create a child package and pass variables with this Execute Package Task.