Monday, March 29, 2010

SSIS 2005: Script consuming variable value

Trough my SSIS incursions I have discovered an interesting 'bug'. I'm calling this a 'bug' since I have not seen any postings about this.

I was using a script to clean a variable with a string value and so far so good.

1) passing the variable to the Script Task Editor's "ReadWriteVariables"
2) scripting:
Dts.Variables("ErrorMessage").Value = ""

Then I decided I did not want to clean it's value any more so I commented the script.

' Dts.Variables("ErrorMessage").Value = ""

The value kept on being cleaned so I deleted the commentary line.cThe value kept on being cleaned.

The next step was to remove the variable from the Script Task Editor's ReadWriteVariables... them the variable stopped being cleaned.

Hope that if someone stumbles upon this one, this post may help out. I just lost a few minutes around this one.


Tuesday, March 09, 2010

SSIS 2005: Calling a package execution from the command line with variables

In order to call a package execution from the command line, SSIS has the 'dtexec' utility.

For more information check the URL from Microsoft:

another good link is this one:

The idea is to call a package execution from the command line in order to speed up testing.

The simple expression is to call the file directly:

dtexec /F 'file path'

to use variables the expression adds the following:

/SET \Package.Variables[User::'VariableName'].Properties[Value];'NewValue'

or, if the variable is a task value:

/SET \package\'DataFlowTask'.Variables[User::'VariableName'].Value;'NewValue'

It works very well.

For my next challenge I need to send the result to a file in order to check how everything worked.


SSIS 2005: Partition Table

This is my recent problem:

I'm trying to work with partitions tables in SSIS 2005 by using the SQL Task tool instead of a script. Sorry for the redundancy in this matter but I wanted to ask your opinion on this one, since you posted a great response.

My challenge is that I have to load monthly files to a table, so I would like it to be partitioned by month (COD_MES is my key), but I do not want to create blank partitions, only the ones I need, because my files may not have values for a specific month.

What I did was to create a sequence of SQL Tasks to do this:

1) default declaration, when creating the data base tables from a script file
(this is not a SQL Task)

IF NOT EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'funcPart_NAME')

IF NOT EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'schePart_NAME')

) ON [schePart_NAME]([COD_MES])

In the SSIS, using SQL Tasks and after the data has been loaded into tempPartition table:

2) add the partition to the partition function


Alter table [tempPartition] add constraint new_month_date
check (COD_MES <= 201001 and COD_MES >= 201001 and COD_MES is not null)

declare @num int
SELECT @num = $partition.funcPart_NAME(201001)
ALTER TABLE [tempPartition] switch TO [TBL_NAME] PARTITION @num

This works fine for my first partition them it stops with an error "...generates more partitions than there are file groups mentioned in the scheme..."

I understand the error, and I thought that, by changing the partition scheme creation (1) I could override this by adding the 'ALL TO' clause.

Bottom line is, is there a way of making the partitions to be created dynamically as needed?



And the answer is... there is.

After searching on the net for a solution I found this blog

and made this change in point (2):



And that was it.

The NEXT command allows the expansion of the scheme that is related to the function.

hope it helps anyone that might have need for it.


Monday, March 01, 2010

SSIS 2005: Packages Configurations

I recently discovered that SSIS 2005 package variables have and order associated with them, so, if you need to use a value that is been populated after another, the first one will not work.

An example for this is in the use of a environmental variable for passing the connection string for the DB to be used (this is my case scenario).

Trying to build an environmental variable to do this is taking me a while and still with no success.

My steps where to create a Win XP environmental variable and set it's value to the connection string of my OLEDB connection.

This being done, I them created an package variable of string type and, using package configurations, set it's value to be field using the system environmental variable previously created.

So far my logic seems correct but the package keeps falling with user can not log in error.

I'm also using the package security property "do not save sensitive information" because I do not want to associate my packages neither to my account or to a package password.

The idea is for the package to be run at the production machine with the production user and not mine.

Advice is welcome and I will try to post new results.




Found by using the system variable and passing the connection with the correct configuration:

Provider=SQLNCLI.1;Data Source=DATASOURCE;Integrated Security=SSPI;Initial Catalog=DATABASE;

If you require to pass the login and password (in my case)

Provider=SQLNCLI.1;Data Source=DATASOURCE;Persist Security Info=True;Password=PASSWORD;User ID=USER;Initial Catalog=DATABASE;

Doing this and guaranteeing that this variable is the called before all others makes it work.


Friday, February 26, 2010

SSIS 2005: Reading a value from a Flat file

This is a small explanation to read a value from a flat file into a variable in SSIS 2005, if you know a better process please comment on this post.

1) Create an package object type variable to store it. This variable will be like an temporary table for your data. (User::Value_FileCount) and the variables for the values read from the file.

2) Using a DataFlow task
2.1) create a Flat File Source (with the connection to the file you want to read)

and map the columns...

2.2) create a Recordset Destination and choose the 'Value_FileCount' into the 'variable name' property.

and again map the values read to the object variable 'rows' (this is when you read from the file into 'Value_FileCount', building the memory data)

3) Going back to the Control Flow and to read from the object variable, add a Foreach Loop
3.1)Set it as a 'Foreach ADO Enumerator', reading from the 'Value_FileCount'

3.1) Map the columns from the 'Value_FileCount' into the variables accordingly to the columns you are reading (in this case just one - 'FileValue')

4) Inside the Foreach Loop container insert a script task
4.1) Pass the variables that you want to set values to. Remember that if you want to change the variables values you must pass them in the 'ReadWriteVariables' section, her I'm just reading the value.

4.2) Add the script to read the variables values


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
Public Sub Main()
'Retrieves the value from the variable
MsgBox("Read from file as FileValue = " & Dts.Variables("FileValue").Value.ToString)
Dts.TaskResult = Dts.Results.Success
End Sub

End Class


That is it.

The end result is something like this:

With the data flow looking like this:

Hope it helps,