Custom Data Flow

To all the SSIS gurus,

I have a situation where I have multiple source tables, I need to populate the destination tables that have the same schema as the source ones. I dont want to do the repetitive task of creating a Dataflow for each source-destination load.

I want to create one custom dataflow component and loop through all my source tables and provide destination tables dynamically.

Is there a way to do that? Any custom data flow component out there?

Your ideas are greatly appreciated.

Regards,

ssiscoder wrote:

To all the SSIS gurus,

I have a situation where I have multiple source tables, I need to populate the destination tables that have the same schema as the source ones. I dont want to do the repetitive task of creating a Dataflow for each source-destination load.

I want to create one custom dataflow component and loop through all my source tables and provide destination tables dynamically.

Is there a way to do that? Any custom data flow component out there?

Your ideas are greatly appreciated.

Regards,

No you can't do this currently. Well, not with the out-of0the-box components anyway.

What you CAN do is build a package programatically in dotnet using the SSIS API. That dotnet code can be called from any place that can execute dotnet code. One of those places happens to be a SSIS package.

HTH.

-Jamie

|||

Thanks for the info Jamie!

Can you please post some articles here of how to go about doing the data flow part programitically?

Regards,

|||

ssiscoder wrote:

Thanks for the info Jamie!

Can you please post some articles here of how to go about doing the data flow part programitically?

Regards,

I can do better than that. here's a sample:

http://msdn2.microsoft.com/en-us/library/ms161541.aspx

-Jamie

|||

This might help as well: http://msdn2.microsoft.com/en-us/library/ms345167.aspx

-Jamie

Custom Data Extension - Query String

Hi,
--Question
I have a created a custom data extension and I want to populate the query
string with some default text, how can I do this? I've initialised the
command text property to my desired default but it always shows an empty
string in the designer? I've included the background as to why I want to do
this below.
Any help would be much appreciated
--Background info
I have created a custom data extension which works just fine when I hard
code the 3 parameters required. These parameters are actually instruction for
my DAL to work with so they are essential to know up front, rather than
typical parameters that would be passed to a SP to filter data. My parameters
are XmlConfigFile and DataProviderID, my DAL then creates an object using
info from the config file for the specified DataProviderID. Since these
params are essential to return any data I want them to be passed via the
query string, I also want the default query string to have them populated
with empty strings
e.g. @.XmlConfigFile='' ; @.DataProviderId=''
This way the report designer just needs to add the values an not remember
the parameter names.The CommandText property gets set by report designer based on the
user-specified values. From the designer point of view, the CommandText
property is "set-only". It will never get the value of the property.
Not sure if this would work for your situation, but you could generate a
"report template" which has a prepopulated dataset with command text.
Instead of creating a new report, report authors should instead create the
new report based of that template.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Neil" <Neil@.discussions.microsoft.com> wrote in message
news:BDF78560-A993-4CBD-A126-3B8DB7D70C2E@.microsoft.com...
> Hi,
> --Question
> I have a created a custom data extension and I want to populate the query
> string with some default text, how can I do this? I've initialised the
> command text property to my desired default but it always shows an empty
> string in the designer? I've included the background as to why I want to
> do
> this below.
> Any help would be much appreciated
> --Background info
> I have created a custom data extension which works just fine when I hard
> code the 3 parameters required. These parameters are actually instruction
> for
> my DAL to work with so they are essential to know up front, rather than
> typical parameters that would be passed to a SP to filter data. My
> parameters
> are XmlConfigFile and DataProviderID, my DAL then creates an object using
> info from the config file for the specified DataProviderID. Since these
> params are essential to return any data I want them to be passed via the
> query string, I also want the default query string to have them populated
> with empty strings
> e.g. @.XmlConfigFile='' ; @.DataProviderId=''
> This way the report designer just needs to add the values an not remember
> the parameter names.

Custom Col. Format

Hi All, This is what I am trying to accomplish
SELECT a query from SQL2k2 export to Excel 2k.
When I populate excel workbook I need the data to be formatted in certain
order (esp. my column values should have comma) like 10000 should be 10,000
Unfortunately not allowed to write macros or any VBA's in excel...so the
only way to do would be
Format the data in SQL before sending to Excel...
can someone provide me a headstart?
TIA
-- pseudo code looking for something like this
-- create table T (myCustomCol varchar(20) Format(#,##,###)Vai2000,
1. Create your table in SQL Server
2. Populate the table with data
3. Create a view with the data format required
4. Use DTS to export the data from the view to the Excel workbook
5. Schedule the DTS package as a job for automation
HTH
Jerry
"Vai2000" <nospam@.microsoft.com> wrote in message
news:OQBg0dbzFHA.3924@.TK2MSFTNGP14.phx.gbl...
> Hi All, This is what I am trying to accomplish
> SELECT a query from SQL2k2 export to Excel 2k.
> When I populate excel workbook I need the data to be formatted in certain
> order (esp. my column values should have comma) like 10000 should be
> 10,000
> Unfortunately not allowed to write macros or any VBA's in excel...so the
> only way to do would be
> Format the data in SQL before sending to Excel...
> can someone provide me a headstart?
> TIA
> -- pseudo code looking for something like this
> -- create table T (myCustomCol varchar(20) Format(#,##,###)
>|||See function "convert" in BOL.
Example:
select parsename(convert(varchar(25), cast(12345678 as money), 1), 2)
go
AMB
"Vai2000" wrote:

> Hi All, This is what I am trying to accomplish
> SELECT a query from SQL2k2 export to Excel 2k.
> When I populate excel workbook I need the data to be formatted in certain
> order (esp. my column values should have comma) like 10000 should be 10,00
0
> Unfortunately not allowed to write macros or any VBA's in excel...so the
> only way to do would be
> Format the data in SQL before sending to Excel...
> can someone provide me a headstart?
> TIA
> -- pseudo code looking for something like this
> -- create table T (myCustomCol varchar(20) Format(#,##,###)
>
>|||Thanks but my data getting exported to excel doesn't has comma, since my
Table in SQL is a regular INT column!
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uFoV1hbzFHA.2132@.TK2MSFTNGP15.phx.gbl...
> Vai2000,
> 1. Create your table in SQL Server
> 2. Populate the table with data
> 3. Create a view with the data format required
> 4. Use DTS to export the data from the view to the Excel workbook
> 5. Schedule the DTS package as a job for automation
> HTH
> Jerry
> "Vai2000" <nospam@.microsoft.com> wrote in message
> news:OQBg0dbzFHA.3924@.TK2MSFTNGP14.phx.gbl...
certain
the
>

custom code to populate parm default

I am using custom code to retrieve the current year to populate the default
year parameter for a report. The custom code correctly retrieves the year
when tested within the body of the report. However, when referenced as an
expression code.CurYr in the "default" seection of the report parameters, it
causes the preview pane to grey-out the prompt. I can not enter anything in
the prompt and it does not contain a value. When I remove the custom code
from the expression for the "default", the prompt is white and available to
me for entry. Does anyone have any idea why this is happening, or how I can
default the current year into my prompt parameter?
Thank you...On May 1, 8:24 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I am using custom code to retrieve the current year to populate the default
> year parameter for a report. The custom code correctly retrieves the year
> when tested within the body of the report. However, when referenced as an
> expression code.CurYr in the "default" seection of the report parameters, it
> causes the preview pane to grey-out the prompt. I can not enter anything in
> the prompt and it does not contain a value. When I remove the custom code
> from the expression for the "default", the prompt is white and available to
> me for entry. Does anyone have any idea why this is happening, or how I can
> default the current year into my prompt parameter?
> Thank you...
I don't think you need to use custom code. Using a date part function
that SSRS will accept should do the trick. Try =YEAR(getdate()) or
=YEAR(now())|||=year(now is acceptable , HOWEVER, the preview pane still greys-out the year
prompt and does Not let me enter anything. It also does Not display the
results for the =year(now expression
For some reason, the preview pane doesn't work properly when an expression
provides the default value. In the past, I have specified a specific default
value, or used a query to load and had no difficulty.
Thoughts?
"toolman" wrote:
> On May 1, 8:24 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I am using custom code to retrieve the current year to populate the default
> > year parameter for a report. The custom code correctly retrieves the year
> > when tested within the body of the report. However, when referenced as an
> > expression code.CurYr in the "default" seection of the report parameters, it
> > causes the preview pane to grey-out the prompt. I can not enter anything in
> > the prompt and it does not contain a value. When I remove the custom code
> > from the expression for the "default", the prompt is white and available to
> > me for entry. Does anyone have any idea why this is happening, or how I can
> > default the current year into my prompt parameter?
> >
> > Thank you...
> I don't think you need to use custom code. Using a date part function
> that SSRS will accept should do the trick. Try =YEAR(getdate()) or
> =YEAR(now())
>|||On May 2, 3:24 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> =year(now is acceptable , HOWEVER, the preview pane still greys-out the year
> prompt and does Not let me enter anything. It also does Not display the
> results for the =year(now expression
> For some reason, the preview pane doesn't work properly when an expression
> provides the default value. In the past, I have specified a specific default
> value, or used a query to load and had no difficulty.
> Thoughts?
>
> "toolman" wrote:
> > On May 1, 8:24 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > I am using custom code to retrieve the current year to populate the default
> > > year parameter for a report. The custom code correctly retrieves the year
> > > when tested within the body of the report. However, when referenced as an
> > > expression code.CurYr in the "default" seection of the report parameters, it
> > > causes the preview pane to grey-out the prompt. I can not enter anything in
> > > the prompt and it does not contain a value. When I remove the custom code
> > > from the expression for the "default", the prompt is white and available to
> > > me for entry. Does anyone have any idea why this is happening, or how I can
> > > default the current year into my prompt parameter?
> > > Thank you...
> > I don't think you need to use custom code. Using a date part function
> > that SSRS will accept should do the trick. Try =YEAR(getdate()) or
> > =YEAR(now())- Hide quoted text -
> - Show quoted text -
Apparently once you establish an expression as your default that's
what you get without options for editing.
The way around it (although it bugs me to have to jump the extra
hoops) is as follows.
Create a new Text type dataset and enter this query SELECT
year(getdate()) AS 'Year'
In the Parameters dialog, set the default to From query and use the
new dataset. This plugs in the year but allows it to be edited.|||Got it... Thanks! :)
"toolman" wrote:
> On May 2, 3:24 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > =year(now is acceptable , HOWEVER, the preview pane still greys-out the year
> > prompt and does Not let me enter anything. It also does Not display the
> > results for the =year(now expression
> >
> > For some reason, the preview pane doesn't work properly when an expression
> > provides the default value. In the past, I have specified a specific default
> > value, or used a query to load and had no difficulty.
> >
> > Thoughts?
> >
> >
> >
> > "toolman" wrote:
> > > On May 1, 8:24 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > > I am using custom code to retrieve the current year to populate the default
> > > > year parameter for a report. The custom code correctly retrieves the year
> > > > when tested within the body of the report. However, when referenced as an
> > > > expression code.CurYr in the "default" seection of the report parameters, it
> > > > causes the preview pane to grey-out the prompt. I can not enter anything in
> > > > the prompt and it does not contain a value. When I remove the custom code
> > > > from the expression for the "default", the prompt is white and available to
> > > > me for entry. Does anyone have any idea why this is happening, or how I can
> > > > default the current year into my prompt parameter?
> >
> > > > Thank you...
> >
> > > I don't think you need to use custom code. Using a date part function
> > > that SSRS will accept should do the trick. Try =YEAR(getdate()) or
> > > =YEAR(now())- Hide quoted text -
> >
> > - Show quoted text -
> Apparently once you establish an expression as your default that's
> what you get without options for editing.
> The way around it (although it bugs me to have to jump the extra
> hoops) is as follows.
> Create a new Text type dataset and enter this query SELECT
> year(getdate()) AS 'Year'
> In the Parameters dialog, set the default to From query and use the
> new dataset. This plugs in the year but allows it to be edited.
>