Dynamic Queries With Data Reader Source Adapter

Apr 16, 2008

I am a business user trying to build an incremental ETL package with SSIS. I have a working prototype on SQL Server 2005 where I select the max(ID) from the last successful run and pass that value into a variable. Then, in my Data Flow step, I select an OLE DB source adapter and use this variable in a custom select statement.

Here's my challenge....the live data is actually in a Postgres DB so I have to use a Data Reader Source adapter. When I try to pass my variable to this adapter the job bombs out. Does anyone know how to dynamically update the query text inside a Data Reader source adapter using variables or otherwise?

View 3 Replies


ADVERTISEMENT

Data Reader Or Data Adapter With Data Set?

Dec 4, 2007

I have used both data readers and data adapters(with datasets) in the projects that I have worked on. I am trying to get some clarification on when I should be using which one. I think I am doing this correctly but I want to be sure I am developing good habits.

As the name might suggest, it seems like a datareader is for only reading data. I have read that the data adapter and dataset are for a disconnected architecture. Or, that they can be used for this type of set up. I have been using the data adapter and datasets when writing to a database and the datareader when reading from a database.

Is this how these should be used? Is the data reader the best choice for reading data? Am I doing this the optimal way from a performance stand point?

......................................................thanks in advance

View 1 Replies View Related

Query Results SSIS Data Flow Source Adapter

Jun 1, 2006

Hi,
Quick question on how SSIS handles queries from Data Source in a Data Flow. I noticed that when I run a particular query from Query Analyzer it takes forever. But, when I run the same query in SSIS data source in a data flow. The query results are immediate.

The query plan is already cached in SQL.

Is this just something which I am seeing incorrect or is there some bit of optimization in there in SSIS. As per my understanding SSIS does not optimize the source query.

Thanks,
Gaurav



View 3 Replies View Related

Using Variables In A Data Reader Source

Feb 28, 2007

Greetings SSIS friends,

I have tried using a sql command for my data reader source. I added the following expression to my datareader source :

"select * from result where result_id > " + @[max_result_id]

but I get the following error message :



The SQL command has not been set correctly. Check SQLCommand property.

I then got rid of the variable (defined at package level) and replaced it with a string like so :

"select * from result where result_id > " + "123456"

but I still get the same error message!



What am I doing wrong?!



Please advise.

View 20 Replies View Related

Data Reader Source Cannot Be Configured

Jan 31, 2006

Hi,

I am using a Ado Connection Manager to connect to a M S Access source.

But when I use this connection Manager in Data Reader Source, I am Not able to Configure Data reader Source. It gives exception "Cannot Acquire Managed Connection From Run Time Connection Manager".

Can anyone help on this.

Thanks

Dharmbir





View 10 Replies View Related

Pipeline Error-excel Source-data Reader Does Not Read In Meta Data

Apr 16, 2008

Hi all, i got this error:


[DTS.Pipeline] Error: "component "Excel Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

and also this:

[Excel Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "Fiscal Week" needs to be updated in the external metadata column collection. The column "Fiscal Year" needs to be updated in the external metadata column collection. The column "1st level" needs to be added to the external metadata column collection. The column "2nd level" needs to be added to the external metadata column collection. The column "3rd level" needs to be added to the external metadata column collection. The "external metadata column "1st Level" (16745)" needs to be removed from the external metadata column collection. The "external metadata column "3rd Level" (16609)" needs to be removed from the external metadata column collection. The "external metadata column "2nd Level" (16272)" needs to be removed from the external metadata column collection.


I tried going data flow->excel connection->advanced editor for excel source-> input and output properties and tried to refresh the columns affected.
It seems that somehow the 3 columns are not read in from the source file?
ans alslo fiscal year, fiscal week is not set up up properly in my data destination?
anyone faced such errors before?

Thanks

View 13 Replies View Related

Truncation Of String Data With Data Reader Source Connecting To ODBC DSN

Mar 18, 2007

A data reader is using a connection manager to connect to an ODBC System DSN . A query in the SqlCommand property is provided. Data is being truncated in the only string column . The data type in data reader output-->external columns shows as Unicode string [DT_WSTR] Length 7.

The truncated output in a text file is the first 3 characters from left to right . Changing the column order has no effect.



A linked server was created in SQL Server Management Studio to test the ODBC System DSN using the following:

EXEC sp_addlinkedserver
@server = 'server_name',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = 'odbc_dsn_name'

Data returned using "OPENQUERY" does not truncate the string column indicating that the ODBC Driver returns data as expected with sql 2005, but not with the Data Reader.

Any assistance would be appreciated.

Thanks,

View 3 Replies View Related

Data Reader Source As ODBC / Complex Sql

May 30, 2007

Hello All,



How do I get columns to output when I have a data reader source? My connection is an ODBC and does complex sql. I am connection to a Netezza database and I would like to execute a very complex query, but in essence does



Create newtable as

(select day, sessionId)

from source

// lots of other joins and unions



select day, sessionId from newtable





drop newtable



I have an ODBC connection and I have a Datareader source, I cannot connect this source to my SQL Server destination because no output columns are available. What am I missing here?



Are there any good examples of this, taking data from a ODBC source into SQL server?



Thanks in advance.

View 5 Replies View Related

Passing Variables To Data Reader Source

May 22, 2007

I am running a sql task which will pass table as object variable to the result set

I have a for each loop container which is used to loop for all the servers. I use two of the parameters to establish connection string in the for each loop task from the reasult set variables.



Now my next step is a data flow task (Data Reader Source) where i have to run a query but the table name and column names are dynamic and i dont see an option to call variables.

can someone tell me if this is possible (var1,var2,... are variables in the package scope)



select var1, var2 from var3.var4

where var5 = 'y'

View 9 Replies View Related

Inconsistent Result Using Data Reader Source To Get Data From DB2

Apr 22, 2008

I am having a very wierd issue regarding a DB2 sql query. I need to get data from Db2 and insert into our sql server database. Using data flow task, to get data I am using the data reader source. COnnection is ado.netodbc connection.
THis sql query also has some comments in it.

The first wierd thing is...

1. On Development server, when I run this query manually, meaning using toad, winsql (connection to the db2 database), the query runs fine. Brings back approx 667 rows which is correct. ON the same server when I try to run this query, via a SSIS pkg, data flow task, using data reader source, gives me error on those comments that exist in that query. But if I run the same SSIS pkg on another server (Integration server). It runs fine. The same pkg also runs fine if I run it from my machine. SO What is different on my Dev server compared to the Integration server.

2. Say if I take those comments out from the sql query, then try to run the ssis pkg. The query is stuck at the first record and goes in an infinite loop mode. though my query is not a procedure, it is just a sql statement. But this ssis pkg with the query runs absolutly fine on the other server. I aslo tried using the other types of connection and ole db source but still the same problem on the Dev server.

What do I need to look for that is so different on the dev server compare to the INT server. I also checked the version on both these server for Visual Studio 2005(by going to About Microsoft Visual Studio), it is the same.

This is what I have on both the servers....
Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00


Please HELP !!!!

Thank you.
Jinita

View 5 Replies View Related

Data Reader Source In Data Flow Problem

Jul 18, 2006

hi all,

i have a package in ssis that needs to deliver data from outside servers with odbc connection. i have desined the package with dataflow object that includes inside a datareader source. the data reader source connect via ado.net odbc connection to the ouside servers and makes a query like: select * from x where y=? and then i pass the data to my sql server. my question is like the following:

how do i config the datasource reader or the dataflow so it will recognize an input value to my above query? i.e for example:

select * from x where y=5 (5 is a global variable that i have inside the package). i did not see anywhere where can i do it.

please help,

tomer

View 11 Replies View Related

Issue With Dynamic SQL Stored Procedure And SSIS OLE DB Data Reader

May 16, 2006

We are writing SSIS packages to pull data from MSO€™s through a stored procedure and drop the output into the pipeline to store it on our local database.

Here is an example of a very simple data flow task that executes the following query in step 1

EXEC GetCustomerData @SELECT = 'OrganizationID', @FROM = 'Organization',@WHERE = 'StateOrProvinceCode = ''WA'''

This command is stored in a variable called SQLOrgCommand.
When I open the task and click on Columns I do not see any columns returned
This allows me to conclude that the metadata needed from the GetCustomerData is not understood by SSIS because the SP uses dynamic sql.


While profiling we realized that it executed the following SQL:

SET FMTONLY ON


to get the metadata. However when we ran the sp with FMTONLY ON we recieved the following errors

Msg 536, Level 16, State 5, Procedure sp_GetData, Line 100
Invalid length parameter passed to the SUBSTRING function.
Msg 536, Level 16, State 2, Procedure sp_GetData, Line 101
Invalid length parameter passed to the RIGHT function.
Msg 536, Level 16, State 5, Procedure sp_GetData, Line 116
Invalid length parameter passed to the SUBSTRING function.
Msg 536, Level 16, State 2, Procedure sp_GetData, Line 117
Invalid length parameter passed to the RIGHT function.
Msg 536, Level 16, State 5, Procedure sp_GetData, Line 187
Invalid length parameter passed to the SUBSTRING function.

The same stored procedure executed without FMTONLY OFF generates the proper output.

Is this a known bug in SQL Server? If so is there a patch that we can install.

View 1 Replies View Related

How Can Pass Variable Or Parameter In DATA READER SOURCE Ising ADO:NET Connection Manager

May 3, 2007

In SSIS in Sql task we have option to pass parameter or variable..But in Data Flow Task when we use Data Reader Source using ADO.NET connection..There is no option to pass parameter or variable Or no option to receive a parameter or variable .

I am having a query were it need to pass a parameter.in sql task ...And Data Reader Source have to receive this parameter from sql task .



Sql Task finds a value of parameter and pass to DataReader Source in DataFlow Task .. ...



Please can any one help me to solve this problem of Receiving parameter or variable in DataReader Source using DAO.Net connection in DataFlow Task..thank you dilsa

View 3 Replies View Related

Script Task User Defined Variable And Data Reader Source SQL Statement

Jan 12, 2007

Hello,

Please can anyone tell me if or how I have to cross reference to a user defined variable from a script task in the SQL of a Data Reader Source?



The script task creates a variable for the last Sales Ledger Session. The SQL in the data reader source that updates the DW sales invoice lines file based upon those invoice lines where the session number is greater than the value from the script task.



The SQL command in the custom properties doesn't cross reference back to the variable.



If anyone can help or needs further detail to help please post a resonse.

Thanks,

Marcus Simpson

View 4 Replies View Related

SSIS Execution Error: The Product Level Is Insufficient For Component Data Reader Source

Jun 5, 2007

Hi all, while executing my ssis package Im getting this error

" the product level is insufficient for component "data reader source" "



but through the bussiness intelligence while debugging it was executing successfully,

but only after deploying the package on to the IS server, while executing getting this error

I checked with the server version also, its was with service pack 2



can I get any help



thanks in advance



View 1 Replies View Related

Custom Source Adapter UI

Jun 22, 2006

I am currently writing a custom source adapter that extracts data from a JD Edwards OneWorld system. In the custom user interface of the source component I need to allow the user to set a query (a custom property), and then refresh a list of output columns that will be extracted based upon that query (similar to the list shown in the advanced editor).

My question is, can I apply the custom property change to the component and build my output column list without closing and restarting my custom UI form? I understand that the IDTSComponentUI interface being implemented allows for transactional editing of the component, in that the changes to the component are not applied until I have returned a result in the implemented Edit() method. However is there a way to apply changes without returning this result (and closing my UI)?

Essentially I am looking to have similar behaviour to that of the 'Refresh' button in the advanced component editor form.

Thanks

View 3 Replies View Related

OLE DB Source Adapter && Variables.

Jan 4, 2007

Hi All,

Using a data access mode of SQL Command, I have a stmt that returns one date ( e.g select max(LastChangedDate) from TblA ).

I want to store the result in a variable. How do I do this?

Cheers,

Tamim.



View 13 Replies View Related

Reading Dates From An XML Source Adapter

Oct 30, 2007

I am using an "XML Source Adapter" in an SSIS Package to try and load an XML file into a database table. But having trouble with a date field.
The XSD defines the field as:-



<xs:element minOccurs="1" name="DateTime" type="xs: string" />

The XML looks like this:-


<DateTime>12/31/2007 10:12:14.123</DateTime>

And the database table column is defined:-



[DateTime] [datetime] NOT NULL,


So in the Data Flow I have a "Data Conversion Transformation" converting [DT_WSTR] to [DT_DBTIMESTAMP].
However, when I run the package I get:-


Error: 0xC02020C5 at Load XXX File, Data Conversion 1 [13550]: Data conversion failed while converting column "DateTime" (15100) to column "DateTimeDT" (15422). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".


So what how do I fix it?
I don't mind losing some miliseconds of precision, so I have tried configuring the error output of the Data Conversion for this field to "ignore failure" for "Truncation". But that seems to have no effect.


I'd be happy to change the XSD to:



<xs:element minOccurs="1" name="DateTime" type="xs:dateTime" />

But the "XML Data Flow Source" doesn't seem to support the dateTime data type.
Any suggestions appreciated. Thanks.

View 1 Replies View Related

Logging From A Custom Source Adapter

Apr 3, 2006

I'm having alot of trouble figuring out the proper way to log from inside my custom source adapter.

I couldn't find my useful information in MSDN, it mostly applies to logging from inside a script task.

I'd like to log my messages along with all the other SSIS package log entries, which I have going to the dts log table.

I'm assuming I should be using some functionality from Microsoft.SqlServer.Dts.RunTime, probably the LogProvider.

Can anyone advise?

View 3 Replies View Related

XML Source Adapter Inserts NULLS Into SQL Instead Of Blanks

Jul 3, 2007



I'm working on a new project using SSIS in SQL Server 2005 and have an issue that I need resolved.

I'm loading an XML file into SSIS using the XML Source Adapter. This maps to an OLE DB destination which reads the data into a SQL table (please see below for the table structure). In between this stage is a data conversion to convert the Unicode characters from XML into non-Unicode characters.



CREATE TABLE [dbo].[XMLTest](

[Forename] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[Surname] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

[PostCode] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL

)



The problem I have is with blank strings in xml are being inserted into the SQL table as NULL values and not blanks (see the xml below).



<Root>
<People>
<Forename>Duncan3</Forename>
<Surname></Surname>
<PostCode>ME16 0WH</PostCode>
</People>
</Root>



Correct me if I'm wrong but a null value in xml means the node does not exist (See the xml below)?



<Root>
<People>
<Forename>Duncan3</Forename>
<PostCode>ME16 0WH</PostCode>
</People>
</Root>



Please also find the schema attached for reference.



<?xml version="1.0"?>
<xschema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Root">
<xs:complexType>
<xsequence>
<xs:element minOccurs="0" name="People">
<xs:complexType>
<xsequence>
<xs:element minOccurs="1" name="Forename" type="xstring" />
<xs:element minOccurs="0" name="Surname" type="xstring" />
<xs:element minOccurs="1" name="PostCode" type="xstring" />
</xsequence>
</xs:complexType>
</xs:element>
</xsequence>
</xs:complexType>
</xs:element>
</xschema>

View 5 Replies View Related

SSIS XML Source Adapter Can't Make XSD Location An Expression

Nov 21, 2007

I am using the "XML Source Adapter" in an SSIS package.
I notice that although you can specify the XML filename as an expression, the XSD appears to have to be a fixed file path.
This is a problem for me since the path for the XSD is different in my development than it will be in production (in production it's on drive E:, which I don't have).

I'd like to have the file location specified in the config file, but since I can't make it an expression how can I do that?

Also, since they don't have Connection Managers I can't switch DelayValidation on.

Thanks.

View 24 Replies View Related

Dynamic Data Source Name...

Jun 6, 2005

Sorry if this is a pretty basic question.. but my team here is working on SQL Reporting app for the first time.

View 19 Replies View Related

Oledb Source Adapter Exception Error Code: 0x80040E21

Feb 9, 2006

Hi
I am using Oledb Provider for Db2 from IBM. (There is another one from microsoft)
Through this provider I am constructing a oledb connection manager.

This connection manager I am Using in Oledb Source adapter.
Now when I set Data Access mode as Table or view I Am able to preview the data.

But when I use Data Access Mode as SQL Command try to preview I get this Error


TITLE: Microsoft Visual Studio
------------------------------
Error at s_NEWMAPPING10 [EMP [1]]: An OLE DB error has occurred. Error code: 0x80040E21.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------

I am not able to figure out how to resolve this.

View 3 Replies View Related

Integration Services :: Share Point Source RSSBus Adapter

Nov 16, 2015

I want to use the excel sheets located on a share point site as source, for this i downloaded the adapter from the following link URL....I got the connection manager to work, but when coming to load the excel files to the destination location its not doing what is intended. For example i want the destination location my local drive for the excel files, how can i do that which destination should i use?

View 2 Replies View Related

Integration Services :: Get Data From Source By Executing Set Of Queries That Have Temp Tables

Jul 29, 2015

I need to grab data from teradata(using odbc connection).. i have no issues if its just bunch of joins and wheres conditions.. but now i have a challenge. simple scenario, i have to create volatile table, dump data into this and then grab data from this volatile table. (Don't want to modify the query in such a way i don't have to use this volatile table.. its a pretty big query and i have no choice but create bunch of volatile tables, above scenarios is just mentioned on simple 1 volatile table ).

So i created a proc and trying to pass this string into teradata, not sure if it works.. what options i have.. (I dont have a leisure to create proc in terdata and get it executed when ever i want and then grab data from the table. )

View 2 Replies View Related

Dynamic PIVOT Table As Data Source View

May 29, 2008

I would like to use a dynamic pivot table in my data source view. It seems that a named query can be only one sql statement. So, I cannot use my multi-statement procedure that creates a dynamic pivot table output.

What is the best course of action here? I could hard-code my pivot table query. I could maintain a redundant table in the pivot format. Do I have any good options?

KenS


Ken

View 1 Replies View Related

Best Practice To Pull Data From Sql Server 2000 To Sql Server 2005 With Dynamic Queries

May 3, 2007

Hi There,

I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?

Thanks a lot!!

View 4 Replies View Related

Data Access :: What Is Correct Usage For Processing Data Adapter Rows

Sep 9, 2015

I have a table that is returning rows from a table query. It seems I have done it before but I cannot seem to get the right procedure to obtain the values. I will paste in the code below in which you will see my bad attempts at accomplishing what I need.

Dim uid As String
Dim pw As String
Dim em As String, fn, ln, mi As String
Dim par As String
Dim Field, n, j As Integer
Dim JJ As Integer

[code]...

View 3 Replies View Related

Data Adapter FILL Causing Data Corrupt Error In C# Web Service

Apr 2, 2008

I hope someone has seen this one...I've got a very simple web method that returns a DataTable from my database. It worked fine until I changed this line of code:

string SqlString = @"SELECT Timestamp, Confession FROM Confessions WHERE ConfessionID = ? ORDER BY Timestamp desc";

To this line of code:

string SqlString = @"SELECT Timestamp, Confession FROM Confessions WHERE ConfessionID = ? AND CategoryID = ? ORDER BY Timestamp desc";


Here is the complete webmethod:

[WebMethod]
public DataTable GetConfession (int ConfessionID, int CategoryID) {

DataTable dt = new DataTable();
dt.TableName = "XMLConfession";

string SqlString = @"SELECT Timestamp, Confession FROM Confessions WHERE ConfessionID = ? AND CategoryID = ? ORDER BY Timestamp desc";

using (OleDbConnection cn = new OleDbConnection (ConnectionString)) {
using (OleDbCommand cmd = new OleDbCommand (SqlString, cn)) {

cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue ("@ConfessionID", ConfessionID);
cmd.Parameters.AddWithValue ("@CategoryID", CategoryID);
cn.Open();

OleDbDataAdapter da = new OleDbDataAdapter (cmd);

da.Fill (dt);
}
}

return dt;
}
To cause the error, all I did was add the AND statement into the query. Now I get this .NET error message every time I try to run this program:


System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt. at System.Data.Common.UnsafeNativeMethods.ICommandWithParameters.SetParameterInfo(IntPtr cParams, IntPtr[] rgParamOrdinals, tagDBPARAMBINDINFO[] rgParamBindInfo) at System.Data.OleDb.OleDbCommand.ApplyParameterBindings(ICommandWithParameters commandWithParameters, tagDBPARAMBINDINFO[] bindInfo) at System.Data.OleDb.OleDbCommand.CreateAccessor() at System.Data.OleDb.OleDbCommand.InitializeCommand(CommandBehavior behavior, Boolean throwifnotsupported) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

I am using SQL Server Compact Edition 3.5 and C# .NET 2.0. It's worth noting that the web service does this outside of the program as well, so I don't think the problem is in the code that calls it. Also worth noting is that the query itself works fine when I substitute values in and run it in .NET Server Explorer.

Thanks in advance for any assistance.

View 5 Replies View Related

Asp.net Page Is Unable To Retrieve The Right Data Calling The Store Procedure From The Dataset/data Adapter

Apr 11, 2007

I'm trying to figure this out
 I have a store procedure that return the userId if a user exists in my table, return 0 otherwise
------------------------------------------------------------------------
 Create Procedure spUpdatePasswordByUserId
@userName varchar(20),
@password varchar(20)
AS
Begin
Declare @userId int
Select @userId = (Select userId from userInfo Where userName = @userName and password = @password)
if (@userId > 0)
return @userId
else
return 0
------------------------------------------------------------------
I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value.
But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0
 passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters();
Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) );
 Do you guys have any idea why?
 
 

View 6 Replies View Related

SqlDataReader Reader Connection Closing Before Dt.Load(reader)

Jun 26, 2007

As you see in the images the connection is closing. During the read it counts 5 columns which is correct. When I step through the code it closes the connection when it hits dt.Load(reader) and nothing is loaded into the datatable.
 
------------------------------------------------------------AS I STEP THROUGH -----------------------------------------------------------------------------------------------------------------------

 
Please help,
 
Thanks,
Tom

View 1 Replies View Related

Dynamic Source And Dynamic Destination

Apr 15, 2008

I have a requirment which i have partly accomplished , but could not get through completely

i have a file which comes in a standard format ending with date and seq number ,

suppose , the file name is abc_yyyymmdd_01 , for first copy , if it is copied more then once the sequence number changes to 02 and 03 and keep going on .

then i need to transform those in to new file comma delimited destination file with a name abc_yyyymmdd,txt and others counting file counting record abc_count_yyyymmdd.txt. and move it to a designated folder. and the source file is then moved to archived folder


what i have taken apprach is

script task select source file --------------------> data flow task------------------------------------------> script task to destination file

dataflow task -------------------------> does count and copy in delimited format



what is happening here is i can accomlish a regular source file convert it to delimited destination file --------> and move it to destination folder with script task .

but cannot work the dynamic pick of a source file.


please advise with your comments or solution you have

View 14 Replies View Related

Clue - Who Dun It? The Data Adapter, DataSet, SqlSelect Etc...

Feb 23, 2007

Ok, I know my connection string and config file are good. Once the rest of the code is added I receive this error:
"SQL Server does not exist or access denied. "
 I'm using the 1.1 framework and my hosting company provides MSSql 2005.
Thanks for your time,
Charlie
Here's the code in my cond behind file.
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
 
 
namespace blog
{
            /// <summary>
            /// Summary description for WebForm1.
            /// </summary>
            public class WebForm1 : System.Web.UI.Page
            {
                        protected System.Data.SqlClient.SqlConnection sqlConnect;
                        protected System.Web.UI.WebControls.DataList dList;
                        protected System.Data.SqlClient.SqlDataAdapter DA;
                        protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;
                        protected System.Web.UI.WebControls.LinkButton linkAdmin;
                        protected System.Web.UI.WebControls.Image header;
                        protected blog.DS ds1;
           
                        private void Page_Load(object sender, System.EventArgs e)
                        {
                                    DA.Fill(ds1, "blogEntry");
                                    dList.DataBind();
                        }
 
                        #region Web Form Designer generated code
                        override protected void OnInit(EventArgs e)
                        {
                                    //
                                    // CODEGEN: This call is required by the ASP.NET Web Form Designer.
                                    //
                                    InitializeComponent();
                                    base.OnInit(e);
                        }
                       
                        /// <summary>
                        /// Required method for Designer support - do not modify
                        /// the contents of this method with the code editor.
                        /// </summary>
                        private void InitializeComponent()
                        {   
                                    this.sqlConnect = new System.Data.SqlClient.SqlConnection();
                                    this.DA = new System.Data.SqlClient.SqlDataAdapter();
                                    this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
                                    this.ds1 = new blog.DS();
                                    ((System.ComponentModel.ISupportInitialize)(this.ds1)).BeginInit();
                                    this.linkAdmin.Click += new System.EventHandler(this.linkAdmin_Click);
                                    //
                                    // sqlConnection String
                                    //////////////////webserver////////////////
                                    this.sqlConnection1.ConnectionString = "Server=xxx.xxx.xxx.x;Database=myDataBase;User ID=myID;Password=myPass";
 
                                    //////////////////local///////////////////
                                    //this.sqlConnect.ConnectionString = "workstation id=HAL;packet size=4096;integrated security=SSPI;data source=HAL;pers" +
                                                //"ist security info=False;initial catalog=blog";
                                    //
                                    // DA
                                    //
                                    this.DA.SelectCommand = this.sqlSelectCommand1;
                                    this.DA.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
                                                                                                                                                                                                                                                                         new System.Data.Common.DataTableMapping("Table", "blogEntry", new System.Data.Common.DataColumnMapping[] {
                                                           
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        new System.Data.Common.DataColumnMapping("bodyID", "bodyID"),
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              new System.Data.Common.DataColumnMapping("linkID", "linkID")})});
                                    //
                                    // sqlSelectCommand1
                                    //
                                    this.sqlSelectCommand1.CommandText = "SELECT id, wxID, dateID, titleID, bodyID, linkID FROM blogEntry ORDER BY id DESC";
                                    this.sqlSelectCommand1.Connection = this.sqlConnect;
                                    //
                                    // ds1
                                    //
                                    this.ds1.DataSetName = "DS";
                                    this.ds1.Locale = new System.Globalization.CultureInfo("en-US");
                                    this.Load += new System.EventHandler(this.Page_Load);
                                    ((System.ComponentModel.ISupportInitialize)(this.ds1)).EndInit();
 
                        }
                        #endregion
 
                        private void linkAdmin_Click(object sender, System.EventArgs e)
                        {
                                    Server.Transfer("logOn.aspx",true);
                        }
            }
}
 

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved