Dynamically create an SSIS Bulk Insert Package

I am looking high and low for some assistance with developing a VB .NET solution that I programmatically create a package and add tasks. I am adding a BULK INSERT task to load large FLAT TEXT files into SQL Server 2005 tables. When I execute the application I execute a package validation and it always returns FAILURE. I have been reading and searching like crazy and I have bought 2 microsoft books, TO NO AVAIL! Can anyone PLEASE help me with this. Thank you!

Cheers~

Do you have any more specific messages other than "FAILURE"?|||

No, unfortunately not. This is a new venture for me and I am not even sure that I am going about the creation process correctly. Quite honestly, I am getting 'pieces' of code from different places off of Microsofts Books Online and sort of putting them together. I have been able to successfully load a package template and execute it successfully, but creating the package from scratch has proven to be quite the challenge. I have found a plethora of samples in C#, but I have not been able to locate a full sample in VB .NET at this point.

The reason I only get a 'FAILURE' return is because I am simply saying DTSExecResult = pkg.execute() and then displaying the return value of the result.

Any suggestions, guidance or direction will be GREATLY appreciated at this point! Thanks a million!

Cheers~

|||Be sure to enable logging: http://technet.microsoft.com/en-us/library/ms136023.aspx

Also know that I'm not going to be much help with the coding. I'm learning on my own as well, but I'm just trying to gather specific information for others who will read this thread.

Also know that at some point you may be asked to share your code.|||

I will gladly share my code! Thank you so much for the replies thus far. I am hoping that I can get some great responses and learn as much as possible in the coming days about this topic. Talk to you soon!

Cheers~

|||Try saving the package (Application.SaveToXML) after creating it. Then open and run the package in BIDS. That makes it a lot easier to see what the problem is.|||

John, I am about to do this. However, what do you mean by running it in BIDS? Thanks.

Cheers~

|||Business Intelligence Development Studio -- or Visual Studio -- which is where you develop SSIS packages via the GUI.|||

OK, what I am seeing now is that the Destination connection is not available, and there are no columns mapped from the input source - (flat file) - to the ole destination table. I am assuming that this is because I am failing to do something correctly in the code to setup the package's BULK INSERT properties.

Are there any tutorials available to understand how to set all the properties for source and destinations, respectively? Any direction you might can provide is awesome!!!!

Again, I am finding bits and pieces here and there, but nothing that says "Hey, do it this way"...

Cheers~

|||

OK, I have made some progress. I now have a Flat FIle source and an OLEDB destination. My delimma at this point is mapping the SOURCE columns from the flat file to the DESTINATION columns of the OLEDB table. The Flat File does not have a header row and it is a pipe "|" delimited file. Any idea on how to map the source / destination columns?

Cheers~

|||

Do you have Output columns in the Flat FIle Source output? Save the package in code (example http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2179817&SiteID=1) and open it to check what it looks like.

If so then you should be able to connect the output to the destination's input, and select your columns. Mapping is a simple method call, but what you map to what is your logic really. In this example I just use index position becuase I "know" that my source and destination formats match.

Dynamically adding values to line chart

I have a report with a line chart and I need to add a number of values to
the chart from the data in my dataset. The problem is that the number of
values to be drawn on the chart may change each time a report is generated
and so I cannot add the values manually in the report designer. So for
example one report may have 5 values to be drawn on the chart Value1,
Value2, Values3... but then another report may only contain 2 values in the
dataset that need to be drawn on the chart.
Each value (Value1...) in the dataset belongs to the same row and so it is
the number of columns in each row which can vary each time a report is
generated.
First report generated (5 columns):
Value1 Value2 Value3 Value4 Value5
10 20 54 32 67
Second Report generated (2 columns):
Value1 Value2
45 2
Does anybody know how I can add values dynamically to a line chart?Hi Holmes,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to generate a Chart
with dynamic columns and its values. However, I am not sure what's the data
for your X-axis and Y-axis. Would you please show me a detailed
information? (For example, the chart will be has such points (1,10), (2,
20), (3,54), (4,32), (5, 67) )
In the Chart properties, we may notice that you will have to add Fields /
Expressions that based on Fields for your Values / Category Group / Series
Group, we cannot add columns dynamically. You will have to convert the data
in the same row into same column.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi
Thanks for the reply. From what I understand you are suggesting I should
convert the dynamic values into just one column.
Here is a more detailed description of the data I have. Each query returns a
number of rows which have an ID, Value (Integer), and then a dynamic number
of Bands (Band1, Band2 ... which are Integers). However the number of bands
is constant for all rows from a single query but is does change each time
the query is executed.
So I may have
ID Value Band1 Band2
1033 25 30 60
1034 35 30 60
The bands need to be represented as straight lines drawn horizontally across
the line chart.
The Values need to be plotted as points and then a line drawn connected them
all.
So I can put the Band values into a single column but how will this help?
How can I get the Band values back from a single column?
Kind Regards
Lewis Holmes
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:ZiTlmYovFHA.780@.TK2MSFTNGXA01.phx.gbl...
> Hi Holmes,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood you would like to generate a Chart
> with dynamic columns and its values. However, I am not sure what's the
> data
> for your X-axis and Y-axis. Would you please show me a detailed
> information? (For example, the chart will be has such points (1,10), (2,
> 20), (3,54), (4,32), (5, 67) )
> In the Chart properties, we may notice that you will have to add Fields /
> Expressions that based on Fields for your Values / Category Group / Series
> Group, we cannot add columns dynamically. You will have to convert the
> data
> in the same row into same column.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Lewis,
If the Bands number are not sure, I am afraid you cannot add these values
dynamically. As I have said, you must convert the tables like below
Line1: (for Band1)
X-axis Y-axis
1033 30
1033 60
Line2: (for Band2)
X-axis Y-axis
1034 30
1034 60
Make sure what was the X-axis and Y-axis value you want in the table and
then compose a line.
If you have any more tables or pictures to demonstrate this scenario more
clearly, you are welcome to send the files here or send them to me directly
if you have security concerns. my direct email address is
v-mingqc@.microsoft.com, you may send the file to me directly and I will
keep secure.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Dynamically Adding Subreports

I would like to Create a report that will print as one document, but is
basically made up of multiple reports (rdl files), however, I need to be able
to determine which reports (rdl files) should be added and pass parameters to
each rdl file dynamically. Can this be done with SQL Reporting Services?You could do that in VS 2005 using the RS Winforms/Webforms controls in
"local" processing mode and registering a subreport callback which
dynamically provides different RDLs with different sets of parameters.
I don't really have a good suggestion for RS 2000 - you could probably
achieve some of your requirements by dynamically generating RDLs, publishing
and rendering them by writing an application that uses the SOAP-API.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Amie Fedric" <AmieFedric@.discussions.microsoft.com> wrote in message
news:6194746B-7D5B-49CE-8498-50AD8EF3B561@.microsoft.com...
>I would like to Create a report that will print as one document, but is
> basically made up of multiple reports (rdl files), however, I need to be
> able
> to determine which reports (rdl files) should be added and pass parameters
> to
> each rdl file dynamically. Can this be done with SQL Reporting Services?

Dynamically adding Select Parameters (Filter)

how do i add parameters like this dynamically? do i need to change the select command? to add the @.ID part?

Although this is for delete you get the idea. This is using a sqldatasource with stored procedures.

protected void SqlPages_Deleting(object sender, SqlDataSourceCommandEventArgs e)
{

// Wipe out the auto params and replace with the correct one.
DbParameterCollection CmdParams = e.Command.Parameters;
DbParameter oParam = null;
foreach (DbParameter cp in CmdParams)
{
//Trace.Warn(cp.ParameterName, Convert.ToString(cp.Value));
if (cp.ParameterName == "@.PageID") {
oParam = cp;
}

}
CmdParams.Clear();
CmdParams.Add(oParam);

//e.Cancel = true;
}

HTH,

|||

You can see the select i am using has a filter as well. Here is the aspx code:

<asp:SqlDataSource ID="SqlPages" runat="server"
ConnectionString="<%$ ConnectionStrings:MonkeyCon %>"
SelectCommand="Pages_SelectPagesBySite"
SelectCommandType="StoredProcedure"
...

<SelectParameters>
<asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" />
<asp:ControlParameter ControlID="ddlSiteFilter" Name="SiteID" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

|||

can i like have an option to display back all? unfiltered? like first i filter by Item Name = "Something", then i want it to be <All> now, how do i do that? something like Select * From SomeTable. no more where...

|||

You could add a branch in your SPROC where if the ID = 0 you return all. And just add an option of <Show All> with a value of 0 to your dropdownlist ..

Dynamically adding fields to a report.

Not sure how to tackle this one. I need to create a report that dynamically
adds fields based on information from a data store.
[Abstracting the problem for clarity]
Let's say I have a table with contact data (FirstName, LastName, Street,
City, State, Zip) and a customer table with a definition of what contact
fields they use and how to render them.
I need a report that I pass a customerId and it looks up what fields to
pull, say customer1's report would look like:
FirstName LastName
Zip
And customer2's report would look like
FullName (Combining First and LastName)
Street
City, Zip
Each report needs to only show the relavant data, and additionlly position
it according the cutomer's defined format. Creating a report for each
customer is out of the question, way too many customers, and that's just
retarded. I thought about adding every field to the report and hiding the
ones that weren't used but positioning would be a headache.
Is there a way that I can dynamically create and add fields to the report at
runtime?
Any ideas would be greatly appriciated.Sorry, dupe of a previous submit. See Below.|||The short answer is no, since dynamically adding report elements is not
supported in version 1.0 of Reporting Services. That said, it looks like the
best workaround in your case is to pre-process the report by loading the
report definition in XML DOM and add/remove the fields you don't need. This
will require an application front-end to generate the report definition,
upload and generate the report.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Joshua Belden" <JoshuaBelden@.discussions.microsoft.com> wrote in message
news:35B3E7B6-3BFB-4985-A1D9-CDCF261FED82@.microsoft.com...
> Not sure how to tackle this one. I need to create a report that
dynamically
> adds fields based on information from a data store.
> [Abstracting the problem for clarity]
> Let's say I have a table with contact data (FirstName, LastName, Street,
> City, State, Zip) and a customer table with a definition of what contact
> fields they use and how to render them.
> I need a report that I pass a customerId and it looks up what fields to
> pull, say customer1's report would look like:
> FirstName LastName
> Zip
> And customer2's report would look like
> FullName (Combining First and LastName)
> Street
> City, Zip
> Each report needs to only show the relavant data, and additionlly position
> it according the cutomer's defined format. Creating a report for each
> customer is out of the question, way too many customers, and that's just
> retarded. I thought about adding every field to the report and hiding the
> ones that weren't used but positioning would be a headache.
> Is there a way that I can dynamically create and add fields to the report
at
> runtime?
> Any ideas would be greatly appriciated.|||You need something like ad-hoc right?
It will be available with SQL 2005.
But right now you can dynamicly build query in SQL. I did it once. it
is not perfect report but idea is :
1. create parameter string where user will put field they want to see
using coma delimeter
2. pass this string on SP and parse it into the table.
generate query what return you what ever you need just remember each
field should be name generic (col1, col2, col3) and sequence should be
how they want to see it on the screen
3. create report base on col1, col2 col3 data returned.
If you need more info please send me email(natta@.netzero.net). I will
try to send you example.
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message news:<#KjqSlqzEHA.1564@.TK2MSFTNGP09.phx.gbl>...
> The short answer is no, since dynamically adding report elements is not
> supported in version 1.0 of Reporting Services. That said, it looks like the
> best workaround in your case is to pre-process the report by loading the
> report definition in XML DOM and add/remove the fields you don't need. This
> will require an application front-end to generate the report definition,
> upload and generate the report.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Joshua Belden" <JoshuaBelden@.discussions.microsoft.com> wrote in message
> news:35B3E7B6-3BFB-4985-A1D9-CDCF261FED82@.microsoft.com...
> > Not sure how to tackle this one. I need to create a report that
> dynamically
> > adds fields based on information from a data store.
> >
> > [Abstracting the problem for clarity]
> > Let's say I have a table with contact data (FirstName, LastName, Street,
> > City, State, Zip) and a customer table with a definition of what contact
> > fields they use and how to render them.
> >
> > I need a report that I pass a customerId and it looks up what fields to
> > pull, say customer1's report would look like:
> > FirstName LastName
> > Zip
> >
> > And customer2's report would look like
> > FullName (Combining First and LastName)
> > Street
> > City, Zip
> >
> > Each report needs to only show the relavant data, and additionlly position
> > it according the cutomer's defined format. Creating a report for each
> > customer is out of the question, way too many customers, and that's just
> > retarded. I thought about adding every field to the report and hiding the
> > ones that weren't used but positioning would be a headache.
> >
> > Is there a way that I can dynamically create and add fields to the report
> at
> > runtime?
> >
> > Any ideas would be greatly appriciated.

Dynamically Adding DataFields to Chart

I am in need of some advice on my first project.
I have a chart which displays as I would expect when a static query is
executed. I need to somehow dynamically add the datafields to the chart at
runtime which is where I am coming unstuck. The number and name of
datafields could change each time the report is run so they absolutely need
to be dynamic.
Any advice on the best way to approach thsi problem would be much
appreciated.
Thanks, SimonDoes anyone have experience in creating Reports with Dynamic fields like
this. The only solution I can think of is to programatically build the RDL
each time, save it to the server then call the report to render it for the
client. It seems very inefficient which is why I wanted to ask others with
more experience in this field '?
Simon
"Simon Dingley" <newsgroups@.nospam-creativenrg.co.uk> wrote
> I am in need of some advice on my first project.
> I have a chart which displays as I would expect when a static query is
> executed. I need to somehow dynamically add the datafields to the chart at
> runtime which is where I am coming unstuck. The number and name of
> datafields could change each time the report is run so they absolutely
need
> to be dynamic.
> Any advice on the best way to approach thsi problem would be much
> appreciated.
> Thanks, Simon
>

Dynamically Adding Columns

Is there a way to dynamically add columns as a report parameter?could you explain some more?
Columns in a table or matrix? What would the parameter be, the number of
columns, a specific column field?
"Terry" wrote:
> Is there a way to dynamically add columns as a report parameter?
>|||For a table, where the user will select or enter the number of columns needed.
Thanks!
"Antoon" wrote:
> could you explain some more?
> Columns in a table or matrix? What would the parameter be, the number of
> columns, a specific column field?
> "Terry" wrote:
> > Is there a way to dynamically add columns as a report parameter?
> >|||Columns will be needed for mailing list.
"Antoon" wrote:
> could you explain some more?
> Columns in a table or matrix? What would the parameter be, the number of
> columns, a specific column field?
> "Terry" wrote:
> > Is there a way to dynamically add columns as a report parameter?
> >|||I haven't tried this.
Make a parameter (say "n") which holds the number of columns.
Make a table with the maxim number of columns you will alow.
Set the expression for the (fe) fifth column to =iif(Parameters!n.Value < 5,
Nothing, " ")
Then put the width of the columns = 0
Hope this works
"Terry" wrote:
> Columns will be needed for mailing list.
> "Antoon" wrote:
> > could you explain some more?
> > Columns in a table or matrix? What would the parameter be, the number of
> > columns, a specific column field?
> >
> > "Terry" wrote:
> >
> > > Is there a way to dynamically add columns as a report parameter?
> > >|||Thank you.
"Antoon" wrote:
> I haven't tried this.
> Make a parameter (say "n") which holds the number of columns.
> Make a table with the maxim number of columns you will alow.
> Set the expression for the (fe) fifth column to =iif(Parameters!n.Value < 5,
> Nothing, " ")
> Then put the width of the columns = 0
> Hope this works
> "Terry" wrote:
> > Columns will be needed for mailing list.
> >
> > "Antoon" wrote:
> >
> > > could you explain some more?
> > > Columns in a table or matrix? What would the parameter be, the number of
> > > columns, a specific column field?
> > >
> > > "Terry" wrote:
> > >
> > > > Is there a way to dynamically add columns as a report parameter?
> > > >|||If the column width doesn't work you can also hide the border lines with the
same expression
"Terry" wrote:
> Thank you.
> "Antoon" wrote:
> > I haven't tried this.
> > Make a parameter (say "n") which holds the number of columns.
> > Make a table with the maxim number of columns you will alow.
> > Set the expression for the (fe) fifth column to =iif(Parameters!n.Value < 5,
> > Nothing, " ")
> > Then put the width of the columns = 0
> >
> > Hope this works
> >
> > "Terry" wrote:
> >
> > > Columns will be needed for mailing list.
> > >
> > > "Antoon" wrote:
> > >
> > > > could you explain some more?
> > > > Columns in a table or matrix? What would the parameter be, the number of
> > > > columns, a specific column field?
> > > >
> > > > "Terry" wrote:
> > > >
> > > > > Is there a way to dynamically add columns as a report parameter?
> > > > >

Dynamic Views

Hi,
I created view like this: "SELECT * FROM TABLE_NAME"
But after change the table structure (adding, deleting, modifiying fields);
when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
table structure.
I always drop and create view after structure change.
Is there any way to create "dynamic" view?
Sereza
It is strongly recomended to avoid using SELECT * in the production.
Run sp_refreshview 'view'
"Sergey Amanov" <a@.a.com> wrote in message
news:eyI1mh25EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I created view like this: "SELECT * FROM TABLE_NAME"
> But after change the table structure (adding, deleting, modifiying
fields);
> when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
> table structure.
> I always drop and create view after structure change.
> Is there any way to create "dynamic" view?
>
>

Dynamic Views

Hi,
I created view like this: "SELECT * FROM TABLE_NAME"
But after change the table structure (adding, deleting, modifiying fields);
when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
table structure.
I always drop and create view after structure change.
Is there any way to create "dynamic" view?Sereza
It is strongly recomended to avoid using SELECT * in the production.
Run sp_refreshview 'view'
"Sergey Amanov" <a@.a.com> wrote in message
news:eyI1mh25EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I created view like this: "SELECT * FROM TABLE_NAME"
> But after change the table structure (adding, deleting, modifiying
fields);
> when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
> table structure.
> I always drop and create view after structure change.
> Is there any way to create "dynamic" view?
>
>