.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Data query task issue

Posted By:      Posted Date: August 31, 2010    Points: 0   Category :Sql Server
Hi Someone help me undersand how to implement a Data meaning query  in SSIS project step by step. even a small package for testing this task ThanksThe complexity resides in the simplicity

View Complete Post

More Related Resource Links

Can't make parameterized query work in data flow task


I have an OLE DB source in a data flow task.  The connection is pointing to a SQL Server 2008 instance.  My SQL works fine when I click the preview button.  Now I need to make it parameterized by adding a question mark in my where clause.  After doing so & clicking the Parameters button, I get the error described in this post .  When I try to do what's described therein to correct the problem (click the "build query" button), I get the error "The PIVOT SQL construct or statement is not supported."

I can't make this query an expression; it's simply too complex to maintain that way (besides, just pasting it into an expression builder window and trying to evaluate as is doesn't work - can't evaluate).

So the question is, how do I parameterize a query?  And why does SQL here work so radically differently from SQL in an execute SQL task?

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Data Truncation issue with Enterprise Library Logging WriteLog stored Proc


Hi ,

I'm using Enterprise Library Logging  feature for logging. The issue i am facing is when the Logging message is too large(more than 65534 chars) ,complete data  is not logged in the Formatted Mesage column which is  of data Type nText .

I am able insert complete data if i try inserting from Sql insert Query from sql management studio. Do i need to add any attributes to data base listener or do i need to change the sp.

 Is there any way to increase the WriteLog stored proc param size in EnterpriseLibrary.Logging config file ? . Please let me know.


Thanks In Advance.

Data Points: Standard Query Operators with LINQ


LINQ to Objects and LINQ to Entities have an arsenal of Standard Query Operators that operate on sequences to perform a wide variety of operations.

John Papa

MSDN Magazine March 2008

Web Q&A: Storing SQL Data, URL Query Length, and More


Find out the best way to store large amounts of XML data in SQL Server, along with the performance implications. What's the maximum length of an XML query to SQL Server in a URL?

Edited by Nancy Michell

MSDN Magazine May 2003

Execute Package Task password issue

I have a parent and child package. Both are stored on the file system and both are password protected. My problem is that when I call the child package from the parent using execute package task, I get prompted for the child package's password. Now the PackagePassword property of the Execute Package Task is supposed to take care of that. But for some reason the password stored in the Execute Package Task is not getting passed to the child package.  I've re-entered the PackPassword property several times, but it did not help.  I even tried loading the password from a config file, but that did not make any difference.  Can anyone help?

How do I add two XML tasks to a Data FLow task?

I am new to SSIS and am developing a package which takes input from an XML file and then populates an existing SQL table with these records.  However, this is a very large XML file.  I keep getting an Out of Memory exception.  (This file is 210 MB)!  So what I tried doing was separating this XML file into 8 smaller files.  Now, I can successfully load data from these smaller files one at a time.  But how can I do all of them in the same package?  I connected two XML tasks to the Data Flow task and get the error below.  I also increased my timeout and number of errors to no avail.  Advice? Error: 0xC002F304 at XML Task 1, XML Task: An error occurred with the following error message: "Exception of type 'System.OutOfMemoryException' was thrown.". Task failed: XML Task 1 Warning: 0x80019002 at zSkywardEnrollment: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Error: 0xC002F304 at XML Task, XML Task: An error occurred with the following error message: "Exception of type 'System.OutOfMemoryException' was thrown.". Task failed: XML Task SSIS package "

How to detect that a Data Flow Task has failed (from inside a custom componet)

Hi, in the PostExecute Method of a custom component i would like to check if the Data Flow Task has been failed. Is that possible? Also it would be great to be able to check if this custom component already got all data or has been marked as "green".  Thanks, Dennis

Data flow task with multiple destinations randomly hangs

I have a package whose control flow consisting of a single data flow task with multiple destinations continues to hang at random locations.  The data flow task is fairly simple: - One OLE DB source (one query of selecting about 10 fields from one table) - 9 lookups that serve basically as left joins to capture which data "drops off" (does a row redirect on on lookup errors to an "error flow") - Two destinations: one the captures all the data that doesn't drop off from the lookups, and another to capture the data that does drop off; these write to two tables unrelated to each other, and unrelated to any of the tables I'm reading from I have tried the following: - Changed one or both destinations from OLE DB to flat file -- still randomly hangs - Removed both destinations altogether -- this always suceeds.   - Removed destination A and leave destination B -- this always succeeds - Removed destination B and leave destination A -- this always succeeds I took great care in making sure I get no warnings when the package runs, even resolving the unneeded column warnings, and the duplicate keys on all my lookups, but it still hangs. However, once I give up and stop the package, the following error is thrown (among others that appear to be a byproduct of the root issue): Error: 0xC02020C4 at Data Flow Task, OLE_SRC AP12 [1]: The attempt to

SQL Query to retrive the data in XML Predefined tag format

I am looking out the way for  instead of using column names as tags, be able to use a predefined mapping. For example Select firstname from employee <firstname>John</firstname> Instead, Have a file that maps the firstname to fname so when we run the TSQL should get:  <fname>John</fname> Please consider that there are over 100 columns in the Query for which how can I use the predefined format.  Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!

Importing xml data with bcp issue

I am trying to transfer one table's data from one server to another. The table structure on both servers are identical: CREATE TABLE [dbo].[_CachedQueriesArchive]( [id] [int] NULL, [statement_text] [varchar](max) NULL, [execution_count] [bigint] NULL, [avg_logical_reads] [bigint] NULL, [last_logical_reads] [bigint] NULL, [min_logical_reads] [bigint] NULL, [max_logical_reads] [bigint] NULL, [plan_handle] [varbinary](64) NULL, [query_plan] [xml] NULL, [cursor_type] [varchar](max) NULL ) I am using bcp DBNAME.._CachedQueriesArchive out e:\temp\cq.dat -N -T statement to export data. And bcp DBNAME.._CachedQueriesArchive in e:\temp\cq.dat -N -T to import. All records were imported successfuly. The issue is that column "query_plan" (of "xml" type) is filled in source DB in all 22 records. But in target DB it is filled only in 2 (two!) records. Other columns were imported perfectly. Tried bcp with -e option. The error file was empty. Tried BULK INSERT [_CachedQueriesArchive] FROM 'e:\temp\cq.dat' WITH (DATAFILETYPE='widenative') for import - same result. Tried bcp with -w option instead of -N. No success. Tried importing file on the source server - everything was fine (all 22 records was imported with their "query_plan" data). The problem occurs only on target server. Servers have different versions: source - 9.0.3257 target - 9.0.4053

C# newbie stuck - trying to access column data in a SharePoint list in an SSIS script task

Hello, I'm sure this is the simplest question but I can't figure it out, even with Google's help. I am trying to stumble through some C# code in an SSIS script task and I am frustrated that I can't figure out how to do the easiest things.  I eventually want to find data in a column,and then use another list as a lookup to replace that value with another where the existing value matches a value in the lookup list.  So, the data in my (multiple choice) column might be "apples; bananas" and in another list I have a row that contains two columns, the first holding the value "Apples" and the second containing "Red Delicious" and my original column should read: "Red Delicious; bananas." But, alas, I can't even figure out how to see the data that is in a column. Here is my code: /*<br/> Microsoft SQL Server Integration Services Script Task<br/> Write scripts using Microsoft Visual C# 2008.<br/> The ScriptMain is the entry point class of the script.<br/> */<br/> <br/> using System;<br/> using System.Data;<br/> using Microsoft.SharePoint;<br/> using Microsoft.SqlServer.Dts.Runtime;<br/> using System.Windows.Forms;<br/> using Microsoft.SharePoint.Utilities;<br/> <br/> namespace ST_08becda4c05c49cd9f30ea76110076cd.csproj<br/> {<br/> [

Access the SQL Command Query Value Property from the OLEDB Container within a Script Task

Hello, I'd like to Access the SQL Command Query Value Property from the OLEDB Container within a Script Task so I can audit the queries which are executing. What is the syntax for this or does anyone have a sample on how to do this? Thank you

Convert Data from TXT file and face a length issue! Need help!

Hi All, I am trying to convert some TXT files in to sqlserver database by using SSIS.  The problem I am facing right now is that some TXT files I have are fixed with line length 286.  However, for the some other TXTs they have 296 characters per line. The reason why this happened is some one added one more field before generating those TXTs.  Instead of creating two SSIS packages for converting those TXTs, can I find another way to get around this?   

Data Reader Destination issue

Hi, I'm new to BI, I created a package to test the data reader destination the dtsx is executing well every thing is green but what after that. The question is how to use this Data reader destination? As I know as a .net programmer there  is a class named data reader that could be used to retieve data in connected mode programmatically using a  connection and a command objects, but in the case of data reader destination how to use it. Or am I confusing the data reader that I kno in the ADO .Net with this one used in BIDS  Should I add a script task after the data reader destination or should I consume this data reader within my proper separate code with a dll or an exe that I create as a .Net project ? For instance, two properties are remaquables for me those are locted within the custom proeprties  datareader and  usercomponentdatatype   The complexity resides in the simplicity

OLE DB Destination issue in ForEach Data Flow

Has anyone encountered the following problem:I have a ForEach container with a single task in it - a Data Flow. The Data Flow uses a Source Script transformation to read each file and writes out to several output streams, each of which is connected to a OLE DB Destination.The ForEach executes for each file (in my example 3 times) but only the data from the last execution is in the DB tables.This from is my output window:SSIS package "PIF.dtsx" starting.Information: 0x4004300A at ETL Audit, DTS.Pipeline: Validation phase is beginning.Information: 0x4004300A at Load V6 Policy, DTS.Pipeline: Validation phase is beginning.Information: 0x40043006 at Load V6 Policy, DTS.Pipeline: Prepare for Execute phase is beginning.Information: 0x40043007 at Load V6 Policy, DTS.Pipeline: Pre-Execute phase is beginning.Information: 0x4004300C at Load V6 Policy, DTS.Pipeline: Execute phase is beginning.Information: 0x402090DF at Load V6 Policy, stg_RiskLocation [5449]: The final commit for the data insertion has started.Information: 0x402090DF at Load V6 Policy, stg_PDMRFeed [5277]: The final commit for the data insertion has started.Information: 0x402090DF at Load V6 Policy, stg_Policy [5208]: The final commit for the data insertion has started.Information: 0x402090E0 at Load V6 Policy, stg_RiskLocation [5449]: The final commit for the data insertion has ended.Information: 0x402090DF at Load V6 Pol

Collect Data from a user task error

Hello, I am a new admin and I'm hoping you can help. I have used the Collect Data from a user Action in a workflow in Designer. I get no errors in saving or publishing. My workflow works find and sends me the task assignment as an email when I test. However, I then run into three problems which (together or separately) prevent me from moving forward in the workflow. 1) When I click the "edit task" button located in the task email, my entire Outlook locks up and must be restarted. (I am using outlook 2007 and Sharepoint Foundation 2010) 2) The email says in step 3 to use the "Open the Task" button to mark the task complete. I cannot find this button anywhere. 3) When I go directly into the task, I can view it but I can find no way to "complete" the task without editing it directly. This gives me an error window that says, "Type 'Microsoft.SharePoint.WebControls.SPDataSource' does not have a public property named 'UseServerDataFormat'. "
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend