Configure data source insertion into SQL Server 2005 database - Express editions

I am attempting to insert information from Visual Web Developer 2005 using either the Gridview or Datalist controls into a SQL Server 2005 database and get stuck when defining the custom statement.

When I enter the text within the insert tab, the <next> button remains greyed out, preventing me from continuing to the next page.

If I copy the same text into the select tab, then I can continue with the wizard, however this raises other problems which may or may not be related (multiple insertions of the data into the SQL Server database table - possibly due to postback functions). I would rather use insert to confirm that my second problem is not because I am using the wrong option.

My question is:

Should I be able to use the insert function within VWD express or is this only available within the standard/pro editions?

Are you using SqlDataSource as the DataSource fo GridView/DataList? I tried that and I can edit customized INSERT command. Can you open QueryBuilder when you enter INSERT command?

|||

I am using SqlDataSource as the datasource as well as a valid connection string to my SQL Server Database.

I launched query builder to construct the sql command for insert. It brings up the appropriate table and columns and I have tried using both parameters and then free text responses with the same result.

If I copy the complete insert command (as constructed from query builder) and paste it into the select tab, then I am able to complete the wizard and my table is populated (albeit with multiple rows).

This implies that the sql statement is valid, however I would have thought that even a simple insert query - insert into table1 values("xxx") - would allow me to progress through the SqlDatasource wizard without any difficulty.

Sadly, I am unable to attach a screenshot which would make the visualisation of the problem that much easier.

To re-iterate my problem, I only get so far within the configure data source wizard - click a tab to create a SQL statement for that operation - <INSERT-TAB>, "sql_statement" before hitting a brick wall as both the next and finish buttons are greyed out.

I would appreciate any assistance you can provide on this matter.

Paul

Config Error (Oracle 10g Source)

Hello All

I was trying to configure my package. I have a Oracle 10g DB as source. I used "Oracle provider for OLE Db" as provider in connection manager. I entered user name and password.

When I set my package protection level to : DontSaveSensitive and executed the package

it gives me this error:

[Connection manager "WDEV.wuser1"] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01005: null password given; logon denied".

When i go back to connection manager I see the blank password. I retype it but again when I execute I get the above error.

I configured the package with same setting and in the config file I mentioned the passwords and all details but again I when I execute the package i get the below error:

[Connection manager "WDEV.wuser1"] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Since it was not storing password OR not executing without password I set the Protection Level to : "EncryptAllWithPassword"

and excute it I works Great. and runs Awesome. Except I get this Warning

[OLE DB Source [1]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

Question 1: "CAN I IGNORE THIS WARNING?'

Question2: "THE PACKAGE RUNS GOOD WHEN I SET THE PROTECTIONLEVEL =EncryptAllWithPassword. With this settings when i configure my package it gives me error. Why is it so Any Help please Below plz find the config file?"

[OLE DB Source [1]] Error: The AcquireConnection method call to the connection manager "WDEV.wuser1" failed with error code 0xC0202009.

[Connection manager "WDEV.wuser1"] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

x<?xml version="1.0" ?>

- <DTSConfiguration>

- <DTSConfigurationHeading>

<DTSConfigurationFileInfo GeneratedBy="AWM" GeneratedFromPackageName="Package1" GeneratedFromPackageID="{289810A-ASD0-4698-BAC5-3270D3E5D17D}" GeneratedDate="9/9/2007 10:18:10 PM" />

</DTSConfigurationHeading>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[WDEV.wuser1].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Provider=OraOLEDB.Oracle.1;Data Source=WDEV;User ID=wuser;Password:XYZ;</ConfiguredValue>

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[WDEV.ccuser1].Properties[InitialCatalog]" ValueType="String">

<ConfiguredValue />

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[WDEV.wuser1].Properties[Name]" ValueType="String">

<ConfiguredValue>WDEV.wuser1</ConfiguredValue>

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[WDEV.wuser1].Properties[Password]" ValueType="String">

XYZ<ConfiguredValue />

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[WDEV.wuser1].Properties[ServerName]" ValueType="String">

<ConfiguredValue>WDEV</ConfiguredValue>

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[WDEV.wuser1].Properties[UserName]" ValueType="String">

<ConfiguredValue>wuser</ConfiguredValue>

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Properties[PackagePassword]" ValueType="String">

<ConfiguredValue /> AWM

</Configuration>

</DTSConfiguration>

Thanks a lotsI had a similar problem with a pair of SSIS running with Oracle. I put "Rely on server storage and.."|||I can help with question number 2.

By default SSIS do not stored any sensitive information when the package is saved. That is given by the ProtectionLevel property you find at the package level. You can use a combination of 'package configurations' and/or protection level value to set connection strings and any other dynamic/sensitive data at execution time. This KB article shows 5 methods to get around the issue you are having; I personally used the #4:

http://support.microsoft.com/kb/918760

I cannot help with the #1 as I haven't worked with SSIS and Oracle at all.

Conditional statement with a cast from string to date

My source file is showing column 10 as string. My destination table is datetime. I am using the derived transformation with a conditional statement. How do I convert the value from string to date. Everywhere I try the (DT_DATE) I get an error.

[Column 10] == "01/01/0001" ? " 01/01/1801" : [Column 10] <= "12/31/1801" ? "12/31/1801" : [Column 10]

What's the error?|||

I modified it to the following but I get an error when I try to debug.

[Column 10] == "01/01/0001" ? (dt_date)" 01/01/1801" : [Column 10] <= "12/31/1801" ? (dt_date)"12/31/1801" : (dt_date)[Column 10]

Error message is:

...conversion between types dt_str and db_timestamp is not supported

|||What is the output column data type specified as in the derived column?|||Where do I check that? I only see the input defined in the derived column transformation which is dt_string 50. The column is defined as datetime in the table.|||http://ssistalk.blogspot.com/2007/01/derived-column.html

I would expect to see the data type of the derived column be DT_DBTIMESTAMP (or DT_DBDATE). The expression should be:

[column10] == "xxxxxx" ? "01/01/1801" : ......

Make sure "Derived Column" is set to "add as new column".|||I don't want to add it as a new column. I am using the conditional statement instead of the SQL case statement. I have dates from Oracle that are outside SQL's range that I need to convert.|||Right, but you can't replace the column because it's a DT_STR.... So if you want a date data type, you need to have a new column. This is the proper way to do it. Then in the data flow, you just ignore [column10] and use [DateColumn10], for instance.|||I tried that but now I get the same error for my new column10.|||

RMooreFL wrote:

I tried that but now I get the same error for my new column10.

Okay, I've posted a new example.

http://ssistalk.blogspot.com/2007/01/derived-column.html

Conditional Split Transformation

Hi all,

I have set up a conditional split task which i want to use with a flat file data source. The flat file consists of multiple rows of data where the first column is an ID. The conditional split is based on the first column value.

What i'd like to know is if in the conditional split once it splits the data can the output be transformed. e.g. If one of the values coming from the flat file requires to be either split up into two values or requires to be passed into a stored procedure to manipulate it, can this be done?

Hope that makes sense.

All help is greatly appreciated, TIA.

Cheers,

Grant

Well in least words, YES!

Output of conditional split can be simply passed to any other control to manipulate in whatever way u like

|||Hi,

thanks for the reply. I have just realised what a stupid question it was. I have just dragged a constraint from the conditional loop and see that a dialog box allows you to select the output. My apologies, and thanks for the help.

Cheers,

Grant|||Hi Again,

Out of interest once i have the row of data i want to process, how would i go about doing the actually processing.

The first this i need to do is to pass once of the row values into a stored procedure and return a variable. Whta would be the best command for this. In the control flow i would have used an Execute SQL task, but this doesn't appear to be available.
Do i have to script anything like this once i have the row?

Thanks again,

Grant

Conditional Split Question

I have a package which has a conditional task which directs rows to its respective OLEDB command. The records are sorted from the source system in chronological order. The problem I am experiencing is that some of the operations do not seem to be occurring in the same order. An example of this would be someone inserts a record, deletes the record and reinserts in the record in that order. When we run the package we can see the records are coming down in chronological order but the delete from the split seems to occur after the inserts. Has anyone else experienced this? Is there anything I might be missing to ensure things happen in the order they should? Any advice would be greatly appreciated. Thank you.Can you provide more details surrounding your data flow setup? Try to illustrate to us how you have the data flow built from source all of the way through the destinations (or OLE DB Commands).|||

Bagles1 wrote:

I have a package which has a conditional task which directs rows to its respective OLEDB command. The records are sorted from the source system in chronological order. The problem I am experiencing is that some of the operations do not seem to be occurring in the same order. An example of this would be someone inserts a record, deletes the record and reinserts in the record in that order. When we run the package we can see the records are coming down in chronological order but the delete from the split seems to occur after the inserts. Has anyone else experienced this? Is there anything I might be missing to ensure things happen in the order they should? Any advice would be greatly appreciated. Thank you.

If you have 2 OLE DB Command tasks in the same dataflow you should not rely on the rows being actioned in the order that they enter the pipeline. There is no sychronisation between the two paths. Once the rows are in different paths then they are two seperate streams of data and will be teated as such. If you want to ensure that the deleted happen after the insertions push the data for deletioninto a raw file and issue the deletes from another data-flow.

-Jamie

|||

Jamie Thomson wrote:

If you have 2 OLE DB Command tasks in the same dataflow you should not rely on the rows being actioned in the order that they enter the pipeline. There is no sychronisation between the two paths. Once the rows are in different paths then they are two seperate streams of data and will be teated as such. If you want to ensure that the deleted happen after the insertions push the data for deletioninto a raw file and issue the deletes from another data-flow.

-Jamie

Yep, or create separate data flows, with precedence enforced at the control flow level.|||I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.|||Ugggghhhhh! That hurts.|||

Bagles1 wrote:

I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.

As I said above, there is no guarantee of the order in which rows get processed, especially when you send them to different data paths.

There is also no guarantee that data will actually enter the pipeline from the staging environment in the order that you think it does. There is no concept of a set of data being ordered - there are lot of things that can influence the order that rows appear in the pipeline.

-Jamie

|||

Jamie Thomson wrote:

Bagles1 wrote:

I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.

As I said above, there is no guarantee of the order in which rows get processed, especially when you send them to different data paths.

There is also no guarantee that data will actually enter the pipeline from the staging environment in the order that you think it does. There is no concept of a set of data being ordered - there are lot of things that can influence the order that rows appear in the pipeline.

-Jamie

Jamie,

Are you saying that if I have an Order By statement in my data reader that there is no guarantee that it will actually be in that order or have I misunderstood your statement?

|||

Bagles1 wrote:

Jamie Thomson wrote:

Bagles1 wrote:

I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.

As I said above, there is no guarantee of the order in which rows get processed, especially when you send them to different data paths.

There is also no guarantee that data will actually enter the pipeline from the staging environment in the order that you think it does. There is no concept of a set of data being ordered - there are lot of things that can influence the order that rows appear in the pipeline.

-Jamie

Jamie,

Are you saying that if I have an Order By statement in my data reader that there is no guarantee that it will actually be in that order or have I misunderstood your statement?

Well, YES, the order will be retained until some other downstream data flow component rearranges the order. Surely you can't expect a union all to maintain order, for instance.|||

Bagles1 wrote:

Jamie,

Are you saying that if I have an Order By statement in my data reader that there is no guarantee that it will actually be in that order or have I misunderstood your statement?

In that case then yes, it will enter the pipeline in the order decreed by the ORDER BYstatement but thereafter you should not rely on the ordering within the pipeline. And you should DEFINATELY not rely on rows reaching a destination/OLE DB Command in some order when those rows are in different data paths.

-Jamie

|||Thank you for your explanation. A very painful lesson to learn this late in the project. Unfortunately in our case we have to process the rows in chronological order so it looks like it will be the script component once again.|||

Bagles1 wrote:

Thank you for your explanation. A very painful lesson to learn this late in the project. Unfortunately in our case we have to process the rows in chronological order so it looks like it will be the script component once again.

If this is true then it sounds as though you have some sort of procedural logic going on - that's not really possible with the standard components. Script component may help though.

I don't actually know your requirement but wouldn't it just make more sense to count the number of inserts and deletes per "thing". If there are more inserts than deletes then you insert the "thing", otherwise you don't.

Good luck with it anyway.

-Jamie