ATL & MFC vs SSIS

Hi All,

I'm trying to call ATL COM component with MFC support from SSIS Custom Component, but I can't show netiher the GUI (MFC Property Sheet) neither to call methods from my ATL component. There is no error or at least Inteligense Studio doesn't report errors. What I'm doing wrong? Thanks in advance!

Regards,

Svilen Varbanov

Wow, there's really not enough information here to know what's wrong.

What errors are you getting? How are you attempting to call the ATL code? Why?

The question is really unclear, so the answer will likely be muddled.

You should generate a primary interop assembly that you then reference and call in your script.

Kirk Haselden
Author "SQL Server Integration Services"

|||

It works! Thanks!!!

Regards,

Svilen Varbanov

Assigning the value of variables in a Subpackage

Hi,

I have a parent SSIS package that executes various subpackages. Each of the subpackages contain variables that are required for their successful execution, e.g. one has a variable of datetime datatype and a variable of varchar datatype.

This date will essentially change with every running of the package as it specifies the date that additional data has been added to the back-end SQL Server 2005 database.

I can't find anything in the expressions of the Execute Package Task that would allow me to pass these variables into the package.

Can anyone advise?

Thanks,

Paul

Look at the documentation of "Execute Package" task @. http://msdn2.microsoft.com/en-us/library/ms137609.aspx

Section on "Passing Values to Child Packages"

Hope this helps.

Thanks,
Loonysan

|||

Thanks Loonysan

I'll give it a go

assign numeric data type to user variable

how to pass the column that has a numeric(12,0) data type to user variable in SSIS? what kind of variable data type should I choose?
if i select int64, it keep giving me an error:
Error: 0xC001F009 at Row by Row process: The type of the value being assigned to variable "User:bject_Key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

there is no numeric data type in variable..

if you click the drop down box in variable data type, you can only see the below data type:

Int32
int64
Object
Sbyte
single
string
uint32
uint64
boolean
byte
char

Do some searching on this forum. It has been discussed in just the last week, along with several other occasions. You'll likely have to read it in as a string and then later convert it to an integer using variable expressions.|||thank you

assign new value of ReadWrite column of string type in script component?

I am new user on VB ( I wish ssis support c# script)

I have made a input string type column ( strName ) in script componen as ReadWrite.

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)

at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.Input0Buffer.set_strName(String Value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Could anyone tell me what I did wrong?

Thanks!

Jun Fan wrote:

I am new user on VB ( I wish ssis support c# script)

It does. In SQL Server 2008!

Jun Fan wrote:

I have made a input string type column ( strName ) in script componen as ReadWrite.

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)

at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.Input0Buffer.set_strName(String Value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Could anyone tell me what I did wrong?

Thanks!

What is "prefix"?|||

Jun Fan wrote:

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

Could anyone tell me what I did wrong?

Thanks!

Your concatenated string is too long for the defined datatype of the strName column.
|||

Prefix is another input string column.

Does string property on Row object is fixed length? If so what is legth. I don't have any very long string. Current my street name, and prifix were in two seperate input column. I was trying to concatenate them together.

For example, strName is "200", prefix is "E". I need put them in a single column as "200 e".

Any suggestion?

Thanks for help.

|||

Jun Fan wrote:

Prefix is another input string column.

Does string property on Row object is fixed length? If so what is legth. I don't have any very long string. Current my street name, and prifix were in two seperate input column. I was trying to concatenate them together.

For example, strName is "200", prefix is "E". I need put them in a single column as "200 e".

Any suggestion?

Thanks for help.

Right, but if strName is defined as three bytes, and you try to add another byte from "prefix," it will fail beacuse four bytes is larger than the defined three byte maximum for strName.|||

Thanks for helping.

Yes, My streetName and prefix both are DT_WSTR wiht max lengh 100. Even the acutual value on each property is a couple char, but it take up all lengh. So I could not concatenate them without trim both property.

Thanks Again!

Jun

assign new value of ReadWrite column of string type in script component?

I am new user on VB ( I wish ssis support c# script)

I have made a input string type column ( strName ) in script componen as ReadWrite.

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)

at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.Input0Buffer.set_strName(String Value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Could anyone tell me what I did wrong?

Thanks!

Jun Fan wrote:

I am new user on VB ( I wish ssis support c# script)

It does. In SQL Server 2008!

Jun Fan wrote:

I have made a input string type column ( strName ) in script componen as ReadWrite.

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)

at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.Input0Buffer.set_strName(String Value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Could anyone tell me what I did wrong?

Thanks!

What is "prefix"?|||

Jun Fan wrote:

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

Could anyone tell me what I did wrong?

Thanks!

Your concatenated string is too long for the defined datatype of the strName column.
|||

Prefix is another input string column.

Does string property on Row object is fixed length? If so what is legth. I don't have any very long string. Current my street name, and prifix were in two seperate input column. I was trying to concatenate them together.

For example, strName is "200", prefix is "E". I need put them in a single column as "200 e".

Any suggestion?

Thanks for help.

|||

Jun Fan wrote:

Prefix is another input string column.

Does string property on Row object is fixed length? If so what is legth. I don't have any very long string. Current my street name, and prifix were in two seperate input column. I was trying to concatenate them together.

For example, strName is "200", prefix is "E". I need put them in a single column as "200 e".

Any suggestion?

Thanks for help.

Right, but if strName is defined as three bytes, and you try to add another byte from "prefix," it will fail beacuse four bytes is larger than the defined three byte maximum for strName.|||

Thanks for helping.

Yes, My streetName and prefix both are DT_WSTR wiht max lengh 100. Even the acutual value on each property is a couple char, but it take up all lengh. So I could not concatenate them without trim both property.

Thanks Again!

Jun

Assign expression value using code

I am trying to use a variable to set an attribute value of an SSIS task but I keep running into the 4000 character limit of the string variable. Not sure why the variable which is of .NET type String has this limit when it doesn't when you are in a .NET environment. Regardless, can anyone provide some sample code that I could use to do this in a script task? I am trying to set the QueryString property of the Data Mining Query Task. All help would be appreciated.

Thank you in advance.

Variables and expressions in SSIS are limited to 4000 bytes. Sorry.|||As far as dynamically setting the QueryString, when you look at the properties of the Data Mining Query task, is there an "expressions" parameter? If you expand the expressions parameter, do you have an option for QueryString? (I'm not sitting in front of SSIS at the moment, so I can't verify.) If so, you may want to put your variable there instead of using a script.|||

All task properties support expressions, it is only Data Flow components that require the developer to actually set some code that says an expression is supported.

The problem is that the result of an expression cannot be greater than 4000 characters. Using a variable whose value is greater than 4000 characters will not work because this still has to pass through the expression evaluator to be assigned to the property value.

Since the DM Query Task does not offer anything other than a literal string for the query, this cannot be set dynamically within the package. I think this is a limitation, obviously it would be nice to have expressions with > 4000 characters but also tasks should be (consistently) developed with properties such as this accepting literals, variables and files. An ideal example is the Execute SQL Task with the SourceSQLType property that describes the interpretation of the SQL Statement property.

|||That is too bad. Hopefully this will change in a later version. Thanks for the help.

assign configuration package

There is a configuration file which is used to hold a connectionstring for the ssis packages.
Once the configuration file is created then is it necessary to go to each package and then package configuration to point the package to the configuration file?
It seems the packages do not automatically refer to the config file. Instead each time the config file is changed then all the packages have to be pointed to the config file one by one. i.e. going from Dev to live.
Am I right?
Thanks

Once a package points to a config file, you don't have to do anything when migrating to different environments, provided the package can find that file in the new environment.

assertion task or something like it

I am playing around trying to develop a thing in SSIS that would be like an assertion elsewhere -- e.g., test a precondition and raise an exception if the precondition isn't true. I'm posting this to see if folks have already done something like this or have suggestions how to go about it in a semi-general way that could be re-used.

My toy thingamabob right now has the goal of comparing the grand totals for a numeric field in two xml files and raising an exception if they aren't equal. I've implemented it as two XML tasks, each of which uses XSLT to compute the total and post it as a string into a variable, then a script task to convert the string variables into numbers, compare them, and take appropriate action.

chw

Nothing in the box, and nothing else I have seen. Script tasks and a "debug" variable taht you set yourself would seem to be the obvious method. Perhaps use an environment variable or similar configuration method to set the Debug variable.

Assembly name for SSIS built-in UITypeEditors?

I have a custom PipelineComponent that accepts a string of SQL. I don't have a custom UI, all I need is the Advanced Editor. Currently the SQL property is just the standard line of text that can be entered on the Advanced Editor. I would like to use the popup multi-line editor that the built-in components use for editing SQL. I was hoping it was the System.ComponentModel.Design.MultilineStringEditor but that is definitely not it (and that one is insufficient for entering more than a few lines of SQL). I'm assuming it must be a UITypeEditor that was shipped as part of SQL 2005, but I haven't been able to track down the qualified assembly name for it anywhere. I tried debugging in VS to get down to a IDTSCustomProperty90 that I could look at the UITypeEditor on, but no such like. I also tried using Reflector to see if I could dig up a string, but no luck there either as the pipeline components don't seem to have managed assemblies (I could've just missed them) and the control tasks (which happen to use the UITypeEditor I'm looking for too) seem to only be thin wrappers around COM interfaces. I scoured BOL and the WWW in general for a list of these assembly names, but looks like they're not out there either. Has anyone tried to find these before? Am I barking up the wrong tree, should I not be able to use these editors for copyright reasons?Using undocumented stuff like that could get messy, but most importantly for me I think most of the editors are rather poor. Simple things like support for Ctrl+A to select all text are missing. For what you want I would write my own, not too hard and you can make it much more user friendly. Not the answer you wanted I suspect, but really I think it would be probably faster and certainly better to write your own.

ASP.NET 2.0 Interface for SSIS

Hi there,

we have several DWH Load processes running in SSIS, however I want to enable the enduser to have more control and information about the status of these processes. Therefore I plan to write a monitoring interface based on ASP.NET 2.0. However I hardly could find good docu or samples on what is available from within ASP.NET 2.0 to read out or control SSIS.

Anybody having me some good pointers to start with ?

To control Ssis packages programmatically start with the Microsoft.SqlServer.ManagedDTS assembly, (Microsoft.SQLServer.ManagedDTS.dll). For the more monitoring type functions, I don’t think there is anything specific, but I’d suggest you look at the logging built into SSIS, set this on in your packages, and query the destination log.

There is also the SSIS service, but not sure if there is a way of communicating with that, other than basic service APIs, and/or WMI.

|||Application class in Microsoft.SqlServer.Dts.Runtime namespace (Microsoft.SQLServer.ManagedDTS.dll assembly) provides some ability to interact with service and monitor/manage running packages. In particular, see
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.getrunningpackages.aspx
method.

Together with SSIS logging this should provide the API you need.