SSIS And The Receive Command

Feb 1, 2007

I built a system where I am sending batches of messages using a single conversation. I want to pull these messages out of my queue using Service Broker. I may have more than one batch sitting in the queue waiting to be picked up, so....

In my SSIS package i started by getting a unique list of conversation_handles where the message type is my end of batch message ( should only be one per batch). Then I used the foreach loop construct thinking I could pass the conversation_handles around and into the data flow.

In the data flow I want to pull all of the messages at once. It looks like the receive statement is designed to do this with the concept of using a table variable.

So I built this SQL to use in for the SQL Command of my OLE DB Source. It gives me this error "Syntax error, permission violation, or other nonspecific errorr"

declare @messages table ( conversation_handle uniqueidentifier, message_type_name sysname, message_body xml );

receive conversation_handle, message_type_name, message_body
from dm.[consultant queue]
into @messages
where conversation_handle = ?

select conversation_handle, message_type_name, message_body
from @messages

If I change it to this, by removing the passed parameter and looking up what should be the same value that I am passing in then it works.

declare @ch uniqueidentifier;
declare @messages table ( conversation_handle uniqueidentifier, message_type_name sysname, message_body xml );

select top 1 @ch = conversation_handle
from dm.[consultant queue]
where message_type_name = 'BatchEnd'
order by queuing_order;

-- select conversation_handle, message_type_name, message_body
receive conversation_handle, message_type_name, message_body
from dm.[consultant queue]
into @messages
where conversation_handle = @ch

select conversation_handle, message_type_name, message_body
from @messages

I have tried sneaking the parameter into the SQL in other ways, but always get the same I have tried sneaking the parameter into the SQL in other ways, but always get the same error message. It just seems that SSIS, or OLE DB, don't want to pass parameters into a block of SQL that is executing this receive command. Has anyone else done something similar to what I am doing here? Any ideas on how to resolve this?

Obviously by using SSIS I want to work on the whole batch at once and not iterate message by message. Right now I just don't like the idea that I am getting the conversation_handle twice and possibly getting a different batch of messages to process.

Thanks!

View 7 Replies


ADVERTISEMENT

Probleim In Using RECEIVE Command.

Feb 23, 2008



Hi,
My msdb grown up abnormally to 35 GB.
I used :

use msdb
go
SELECT TOP 10 OBJECT_NAME([object_id]), *
FROM sys.dm_db_partition_stats
WHERE index_id IN (0,1)
ORDER BY in_row_reserved_page_count DESC;

And i found queue_messages_407672500 table is occupying a lot of space.

When i tried to query :
select TOP 5 * FROM queue_messages_407672500 ;
I got error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'queue_messages_407672500'.

Also , to clear the queued messages in msdb , i tried to use :
RECEIVE TOP (1) * FROM queue_messages_407672500 ;
But got the same error as above.

Please suggest , how can i shrink the msdb now, as without receving the messages i am not able to shrink it.

Many Thanks in advace.
Mohit

View 2 Replies View Related

Using A Variable In SSIS - Error - Command Text Was Not Set For The Command Object..

Nov 4, 2006

Hi All,

i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,

enterName - String packageLevel (will store the name I enter)

myVar - String packageLevel. (to store the query)

I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"

Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.

Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".

Can Someone guide me whr am going wrong?

myVar variable, i have set the ExecuteAsExpression Property to true too.

Please let me know where am going wrong?

Thanks in advance.








View 12 Replies View Related

Ssis EXEC Command

Aug 21, 2007

I have 2 variables, one is the column name of the table and the otherone is the table name and I need to write this in the "Execute SQLtask" of a "For each loop" container in a ssis package like this:Truncate table <tableName>Insert into <tableName>Exec (' select [' + ?+ '] from '+ ?)It gives me error message when I try run the ssis. However if I putthe above statements into a stored procedure and wrote look this, itworks:spStroedproc ?, ?Do I need to change any of my settings for my "Exec" to work?Thank you in advance

View 9 Replies View Related

SSIS OLE DB Command Error

Sep 21, 2006

I have written a stored procedure that accepts 33 parameters. I am trying to execute the stored procedure from a SSIS OLE DB Command task. The code I am putting into the "SqlCommand" property is "Exec dbo.CO_PROD_UPDATE ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?

View 6 Replies View Related

OLE DB Command Error SSIS

Jun 15, 2006

Hi,

when we are using OLEDB Command we are getting the following error?

when we use sql server as the source its working fine .but its throwing error when we try to connect the orcale database .
Error at Data FLOW TASk[OLE DB Command[3863]]: An OLE DB error has occured :0x80040E51.
An OLE DB record is available. Source : "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E51
Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
ERrror at Data Flow Task [OLE DB Command [3863]]: Unable to retrieve destination column description from the Parameter of the SQL Command
Thanks & Regards
Jegan.T





View 4 Replies View Related

Windows Command In SSIS?

Apr 23, 2007

Is it possible to run a windows command on SSIS, like a dos command?





Thanks,

Phil

View 3 Replies View Related

OLEDB Command In SSIS

Mar 27, 2007

I AM working on DW building and i m using SSIS.I haev problem with data transformation OLEDB command.I have written a query to clean data in OLEDB command box ,but it takes whole lot of time because at a time it slects 6000 rows from the source and put in to destination but i have 300000 rows to process.How can i increase the size.

View 4 Replies View Related

SSIS OLE DB Command Fatal Error

Mar 24, 2008

I have a pretty simple senario.

In a data flow task, I have a OLE DB Source, which is a simple select statement. That is then connected to a OLE DB Command object, which executes a stored procedure on a different server, with the columns of the source as parameters to the stored procedure. I then have that connected to another OLE DB Command object, that will update the row in the source, marking it processed if there wasn't an error (no errors raised) in the stored procedure.

This seems to work just fine. Rows that had errors will error out, but I have the max allowed errors set very high to prevent the task from stopping. The problem that I have noticed is that if the very last row of the original source has an error, the component will fail with a fatal error and not go back and mark the rows that did succed as processed.

The fatal error looks like this:
Error: 0xC0047022 at OrderItems, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Insert OrderItem" (97) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

At this point I really believe this is a bug in SSIS, because if the last row succeeds, reguardless of how many rows failed in the middle, the last step runs, and updates the source as processed for the rows that did not fail. But if the last row fails, it doesn't execute the next step.

Has anyone experienced this before. I really hope there is a minor configuration change that can be made so I do not have to redesign the package. My only alternative at this point is to redesign the stored procedure to not raise an error, and return any errors in an output parameter, and then use a conditional split to determine what rows to go back and update at the source, and then rather than relying on the package output to see the errors, I'll have to write the errors to a table somewhere.

- Eric

View 1 Replies View Related

How To Run SSIS Package Through The Command Line?

Sep 13, 2007



Dear member,

Does any body know how to run SSIS package through the command line?

Thanks

Shamen

View 3 Replies View Related

SSIS SQL OLE DB Source - SQL Command Vs Mapping

Oct 10, 2007

I created a Stored Proc to compute all the data I need to export to a CSV file.
I use the provider MS OLE DB Provider for SQL Server.
It's a very simple package with a single Data Flow Task.
This flow task is using an OLE DB Source to call a simple SQL Command : Exec MyStoredProc.
There are no params.

This Stored Proc is using table variables to compute the data.
It takes about 10 seconds to return anything.
The problem is that the mapping doesn't work with the OLE DB Source.
There are no fields at all shown in the mapping screen.

I tried to replace the Stored Proc by a version which only returns fields and no data.
Then the mapping would work just fine.
The package is then compiling and working fine.
But everytime I put back the real stored proc, even without changing the SQL Command, the SSIS execution breaks at execution.
It keeps saying :

"Error: 0xC0202005 at Data Flow Task, OLE DB Source [477]: Column "RecordType" cannot be found at the datasource."

My guess is that SSIS doesn't wait the 10 secs and thinks the Stored Proc is not returning anything.
What can I do to make this work ?


Thanks,

Vincent

View 3 Replies View Related

Sp_executesql Error From SSIS OLE DB Command

Jul 20, 2006

Hi


I've got an SSIS package, I'm in a Data Flow step that has a OLE DB Command Data Flow Transformation.


The SQLCommand in the OLD DB Command is:
______________________
UPDATE Employment_Episode_Dim
SET Commence_Serv_Date = ?
WHERE AGS_Number = ?
______________________


The package will run through successfully, but I don't see any updates
in Employment_Episode_Dim. I ran a trace, and this is a sample of the
SQL that is being executed:


______________________
exec sp_executesql N'UPDATE EMPLOYMENT_EPISODE_DIM
SET COMMENCE_SERV_DATE = @P1
WHERE (AGS_NUMBER = @P2)',N'@P1 datetime,@P2 int',''2005-01-27
00:00:00:000'',78577229
______________________


If I take that SQL and execute it in a query window, it fails due to
two single quotation marks placed around the date parameter being used
as @P1.


Why does SQL/SSIS put two singles around the date? It's outside of the
string to be executed, so it doesn't seem to need to have the double.


Can anyone please help?


Thanks
Earth

View 2 Replies View Related

Debugging In SSIS - Immediate And Command Windows

Jan 20, 2006

Hi everyone,
I am having a difficult time debugging a package that I'm working on. I read in BOL that the immediate window should be an option during debugging but I can't find it anywhere(nor can I enable it), and I was planning on using it to access an oledb source property that is using an expression. Is the command window the same as immediate? I didn't orignally think so but I'm not sure. What is the syntax to use for this once I find it?
Thanks,
Adrian

View 2 Replies View Related

Conversion Of DTS To SSIS Command Line

Apr 5, 2006

I am trying to convert a command line using the dtexecui utility. I need to pass three parameters ; account number ,begin and end date to project.

What am i doing wrong ?

DTEXEC /DTS "File SystemArchive Data" /SERVER SRV2 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW package /SET "Account_Number";"'00001'" /SET "File_Name";"'C:InetpubwwwrootoutputArchive'" /SET "Begin_Date";"'04/03/2006'" /SET "End_Date";"'04/04/2006'"


Error I get

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 9:52:49 AM
Warning: 2006-04-05 09:52:51.58
Code: 0x80012018
Source: Archive Data
Description: The configuration entry, "Account_Number", has an incorrect form
at because it does not begin with the package delimiter. Prepend "package" to t
he package path.
End Warning
Warning: 2006-04-05 09:52:51.58
Code: 0x80012017
Source: Archive Data
Description: The package path referenced an object that cannot be found: "Acc
ount_Number". This occurs when an attempt is made to resolve a package path to a
n object that cannot be found.
End Warning
DTExec: Could not set Account_Number value to '00001'.
Started: 9:52:49 AM
Finished: 9:52:51 AM
Elapsed: 2.172 seconds

View 1 Replies View Related

SSIS OLE DB Command Task Error

May 25, 2007

hi,



I am having trouble executing a DB2 stored procedure using OLE DB command task.



The stored procedure takes 2 paramters.



1. filename 2. logfilename



filename - servernamefoldername extfilename

logfilename - servernamefoldernamelogfilename

Both are user defined variables with data type string. i created 2 dervied columns FILE and LOG to pass these values.



FILE takes the filename value



LOG takes the logfilename value



When I execute it gives me the following errors.



[OLE DB Command 1 [77]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "IBM OLE DB Provider for DB2" Hresult: 0x80004005 Description: "[DB2/NT] SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row. SQLSTATE=21000 ".



[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Command 1" (77) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.



[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0202009.



Can anyone please help me resolve this issue.



I can execute the SP using execute sql task but the problem is I need to pass the rejected rows to a reject table for logging purposes. thts why am tryin the OLE DB command.



Thank You

View 7 Replies View Related

Calling SSIS Package Through Command Prompt

Feb 24, 2008

All, I am developing the data mart refresh task in SSIS. I wanted to call the package in command prompt. I need help. Can any one give any tips on it. Thanks and appricate in advance.

Regards
Govind

View 2 Replies View Related

Running SSIS From Command Line W/ SQL Agent

Apr 17, 2007

Hi Everyone!



I'm trying to run have SQL Server Agent excute an SSIS package from the command line and I keep recieving an error message. I will assume that I have miss typed something. Can someone validate that the execute line looks workable?



"C:Program Files (x86)Microsoft SQL Server90DTSBinndtexec.exe" /F "F:ProjectsSSISCustomerMaster_1CustomerMaster_1Package.dtsx" /CONNECTION "APLUS70F70.AKIN";""uid=AKIN;Dsn=APLUS70F70;"" /CONNECTION "CRMPSQL.IBT_Aplus";""Data Source=CRMPSQL;Initial Catalog=IBT_Aplus;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW



Thanks in advance!



Anthony Akin



View 10 Replies View Related

Executing A Command From Within SSIS Script COmponent

Jan 3, 2008

I'm trying to use an SSIS script component that will go do a DNS lookup on the contents of an input column. I've already got a vbs function that performs the wshshell.exec and parses the answer for address and/or errors. I'm having trouble with syntax for the call. When I attempt to run it, I get a lot of errors like "Name 'wshell' is not declared", "StdErr" is not a member of 'String'"

Does anyone have an example of a script that calls a command?

I'm fairly new to SSIS, but think I've properly set up the inputs and outputs.

View 9 Replies View Related

Errors While Running Ssis Packages From The Command Prompt.

Dec 23, 2005

I have created ssis package. It has been successfully running at UI level.

But when i am trying to execute it from the command prompt by using dtexec utility it is showing the following error messages.

Error: 2005-12-23 17:01:57.67
   Code: 0xC00470FE
   Source: Data Flow Task DTS.Pipeline
   Description: The product level is insufficient for component "Flat File Source" (1).
End Error
Error: 2005-12-23 17:01:57.67
   Code: 0xC00470FE
   Source: Data Flow Task DTS.Pipeline
   Description: The product level is insufficient for component "Script Component" (9).
End Error


i have entered the command as follows.

dtexec /f "c:somefolderpackage1.dtsx"

 

Any points regarding this issue would be helpful.

 

View 7 Replies View Related

SSIS Logging Issues When Package Run From Job/Command Line

Feb 13, 2008



All,


I am logging all the tasks in my SSIS package to SQL Server. For each task I am logging atleast the Pre-Execute, PostExecute, OnError events. For Script tasks, I have custom logging and I am logging the ScriptTaskLogEntry event too.

When I run the package manually from BIDS, the logging works great ! But when I try to run the package from a job or from the command line, the number of events that is logged greatly reduces. For eg. when manually run, I get 104 records in the log table but when run from the command line I get 23 records only. Most of the custom logging messages from the Script Task do appear. Its the pre and post-execute events that are skipped. Any idea why ?

Here is command line from the job. I also use the same command line with "dtexec" from the command prompt.

/FILE "\MyServerprojectsSSISPackagesIRRLoadingPackage.dtsx" /DECRYPT mypwd /CONNECTION "ATLSQLDEV01.SSAM.User1";"Data Source=ATLSQLDEV01;User ID=user1;Initial Catalog=SSAM;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;" /CONNECTION CashFlowConnection;"\MyServerpublicReportingLoadingInProgress8634_Cashflow.txt" /CONNECTION "SMTP Connection Manager" ;"SmtpServer=ATLEXCH01;UseWindowsAuthentication=True;EnableSsl=False;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /LOGGER "{59B2C6A5-663F-4C20-8863-C83F9B72E2EB}";"LogFile.txt" /LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"ATLSQLDEV01.SSAM.User1"

Thanks in advance for your help.
Amir

View 1 Replies View Related

Calling Oracle Package And Select Command Together In SSIS

Jan 14, 2008

hi,

Below are 2 oracle commands




1. (calls an oracle package) - call lpaarchive.pibrdg.setlastbridgeruntime(sysdate);

2. (selects rows) - select timestamp ,



pitag ,

rtlmp from LPAARCHIVE.I_PIBRDG_BUS5MINRTLMPS

where timestamp <= (sysdate + 0.002777778)

order by TIMESTAMP ASC;
I need to execute these 2 statements together. I tried using Execute SQL task to call the package and then an OLEDB source that calls a variable with the select statement. But it does not retrieve any rows. It seems like the result of calling the package is used by the select statement to give the final rows. Could anyone please help me resolve this issue.

View 5 Replies View Related

SSIS OLE DB Source SQL Command From Variable DataType Problem

Sep 12, 2006

Hi,

I have to read data from an MDB file into SQL Server. Simple, but I need to place a literal into every row. In my OLE DB Source I would use:

SELECT '001' AS col1, foo, bar FROM Table1

But the problem is that I pass my value for col1 into the package from C# using Managed DTS.

So I set up my OLE DB Source using "SQL command from variable." And I have two variables one to receive the value of for col1 (col1) and one that is an evaluated expression that has a dynamic sql statement (dynSql).

col1 is a String as is dynSql. dynSql looks like this:

"SELECT " + @[col1] + " as col1, foo, bar FROM Table1"

Now as long as I set the value of col1 to '001' that should work. Right? Well not exactly. The destination for col1 in SQL Server is a char( 3 ). But the package makes the col1 column from the source a DT_WSTR. Which causes the famed: "Text was truncated or one or more characters had no match in the target code page.".

Is there a better solution?

Thanks. Kenneth.

View 1 Replies View Related

Handling Updates In SSIS With OLEDB Command Transformation

Apr 26, 2008


Hello My package flow is like This

OLEDB SOURCE --> LOOK UP TRANSFORMATION
|
lookup output / error output(for new Inserts)
(updated records) /
/
DERIVED COLUMN DERIVED COLUMN
TRANSFORMATION1 TRANSFORMATION1
| |
v v
OLEDB COMMAND TRANSFORMATION OLEDB DESTINATION (inserting new records to
(Updating records in destinationTable)
destination)



in this senario new records r insrted properly
but though package runs without error records not get updated in Destination.
In OLEDB COMMAND my query is like below,

UPDATE TARGET_SCD_1 SET
CURRENTSTATUS = ?,
CURRENTSTATUSEFFECTIVEDATE=?,
PROPOSALEFFECTIVEDATE=?,
UNDERWRITINGEFFECTIVEDATE=?,
TECHLAPSEEFFECTIVEDATE=?,
WITHDRAWNEFFECTIVEDATE=?,
DCSEFFECTIVEDATE=?,
PREPOSIONEFFECTIVEDATE=?,
INFOURCEEFFECTIVEDATE=?,
LAPSEEFFECTIVEDATE=?,
SURRENDEREFFECTIVEDATE=?,
FLCEFFECTIVEDATE=?,
CANCELLEDEFFECTIVEDATE=?,
DCIEFFECTIVEDATE=?,
REC_UPT_DT=?
WHERE O__NUM = ?

In advanced editor of OLE DB I hv created additional 16 paramater columns though i assign datatype as numeric to tht columns when i press refresh automatically it changes to DT_STR.
My destination table columns r numeric .

I though due to this datatype mismatch the error came So i change the datatype of dest to varchar to make compatible with OLEDB Comand Transformation. THN also no Use NO UPDATES



package is running without error but records not get updated.

if change the flow like below

OLEDB SOURCE --> LOOK UP TRANSFORMATION
|
lookup output / error output(for new Inserts)
(updated records) /
/
DERIVED COLUMN DERIVED COLUMN
TRANSFORMATION1 TRANSFORMATION1
| |
v v
OLEDB COMMANDTRANSFORMATION /
(Updating records in /
destinationTable) /
/
/
UNION ALL TRANSFORMATION
|
v

OLEDB DESTINATION


In This Case The updated record get inserted in the target as wel as the old remains as it is means m getting one additional record.



kindly help me to figure out the bug
M frusted with this issue please.............

View 1 Replies View Related

Integration Services :: Execute SSIS Package From Command Prompt

May 28, 2015

How to execute ssis package from command prompt and also pass configuration file to it and set logging to ssis log provider for sql server. Writing all those options with cmd.

View 3 Replies View Related

Saving Passwrod In SSIS | Error When Using Command Line Dtexec.exe

Sep 13, 2007

Hi All,

In one of my SSIS package (which connect to DB2) i save the DB2 username and password in SSIS packge itself by using option "EncryptAllWithPassword".
When I try to run the packge using dtexecui.exe it works perfectly fine. It prompts me for package password and then run fine.

Now I am trying to run it using command line dtxece.exe and i get fooolwoing error. Please suggest and help!!!
DTExec.exe /FILE C:pkgDB2Load.dtsx /CONFIGFILE C:ConfigpkgConfigMaster.dtsConfig /Decrypt mypassword /CHECKPOINTING OFF /REPORTING EWCDI

Error Message:
----------------------
Description: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "IBM OLE DB Provider for DB2 Servers" Hresult: 0x80040E21 Description: " SQL30082N Attempt to establish connection fa
iled with security reason "17" ("UNSUPPORTED FUNCTION"). SQLSTATE=08001
"



Thanks
Sid

View 1 Replies View Related

Command Line Parameters Are Invalid Is The Output For A SQL Job To Run SSIS Package

May 25, 2006

Hello,

I created a SSIS package that has a flat file manager whose connection string is a package variable, the reason is that there is a foreachfile container loop to loop thru CSV format files in a directory and load them to a sql table.

The package execution is working in the designer studio as well as in the management studio-- a copy was saved to a sql 2005 server where a sql job is deployed to run the package. However when I ran the job, it claims successful but doesn€™t do anything in reality. If I checked the box failing the package upon validation warming, the job will fail with the error: the command line parameters are invalid. The command line looks like below:

/SQL "Package" /SERVER FTLQSQL03 /WARNASERROR /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

One thing that I think maybe wrong is on the data source tab of the job step GUI, the flat file manager€™s connection string is blank, compared to other connection managers having related values.

Does anyone know how to make the job working?

Thanks in advance





I

View 3 Replies View Related

How To Script An Excel Datasource In Sql Job? (SSIS)The Command Line Parameters Are Invalid.

Dec 4, 2005

I have a Sql agent job which will exeute a SSIS package.  While, one of my data sources is an excel file. I scriptted the sql agent job and recreate on a test server, it fails with The command line parameters are invalid.. The command line has the following for the excel data source, and that is the place fails the job. /CONNECTION UserDataExcel;"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\myserverUserDataUserData.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";" I guess it has something to do with the double-quote inside, Please advise.

View 1 Replies View Related

Integration Services :: SSIS - Can Use Variables In A Data Flow Task Command

Jul 2, 2010

In my SSIS Data Flow Task, I have a query that retrieves data based on a couple of date parameters. Is there a way we can pass/use the Variables defined in the SSIS package in the query ?

(I am assigning values to those variables from C# code)

The query should look like this:

select ordernumber, customerid from salesorder

where statecode=3 and datefulfilled between @variable1 and @variable2

View 8 Replies View Related

Job To Execute A SSIS Package Fails - The Command Line Parameters Are Invalid

Jul 28, 2005

I have created a job to execute a SSIS package located in the SSIS Package Store.  When starting the job I receive an error.  The history log reports:

View 12 Replies View Related

Calling Scalar Valued Function From SSIS OleDB Command Transformation

Mar 2, 2007

Hi There,

I need to call a function to calculate a value. This function accepts a varchar parameter and returns a boolean value. I need to call this function for each row in the dataflow task. I thought I would use an oledb command transformation and for some reason if I say..

'select functioname(?)' as the sqlcommand, it gives me an error message at the design time. In the input/output properties, I have mapped Param_0(external column) to an input column.

I get this erro.."syntax error, ermission violation or other non specific error". Can somebiody please suggest me what's wrong with this and how should I deal this.

Thanks a lot!!

View 8 Replies View Related

Integration Services :: Using Parameters With OleDB Command Provider For Oracle Into SSIS Package?

Dec 8, 2011

I need to delete some records in a Oracle RDBMS based at a SQL Server's query. I'm using the follow structure SSIS's package:

View 4 Replies View Related

SSIS Package That Runs Fine Through Command Prompt, XML Error When Run Through SQL Server Agent Job CmdExec

Aug 17, 2006

I have an SSIS package that runs fine through command pormpt although when I try to run it from a SQL Servr Agent Job CmdExec step it bombs out.  Please help this has me stumped...the SSIS package uses an XML connection string so certain key settings such as connection strings and email info can be changed easily.  Currently this is all on the same machine.  I have not moved it beyond where I am developing.

 

On the command line I am using the following command...

dtexec /F "S:connectionscontacts.dtsConfig" /DE "password"

 

Below is the output log...

 

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started:  6:59:40 PM
Progress: 2006-08-16 18:59:41.29
   Source: Data Flow Task
   Validating: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.29
   Source: Data Flow Task
   Validating: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.71
   Source: Data Flow Task
   Validating: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.73
   Source: Data Flow Task
   Validating: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.77
   Source: Data Flow Task
   Validating: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.77
   Source: Data Flow Task
   Validating: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.77
   Source: Data Flow Task
   Validating: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.77
   Source: Data Flow Task
   Validating: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.79
   Source: Data Flow Task
   Prepare for Execute: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.79
   Source: Data Flow Task
   Prepare for Execute: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.79
   Source: Data Flow Task
   Prepare for Execute: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.79
   Source: Data Flow Task
   Prepare for Execute: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.81
   Source: Data Flow Task
   Pre-Execute: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.84
   Source: Data Flow Task
   Pre-Execute: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.90
   Source: Data Flow Task
   Pre-Execute: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.90
   Source: Data Flow Task
   Pre-Execute: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.92
   Source: Data Flow Task
   Post Execute: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.92
   Source: Data Flow Task
   Post Execute: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.92
   Source: Data Flow Task
   Post Execute: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.92
   Source: Data Flow Task
   Post Execute: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.92
   Source: Data Flow Task
   Cleanup: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.93
   Source: Data Flow Task
   Cleanup: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.93
   Source: Data Flow Task
   Cleanup: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.93
   Source: Data Flow Task
   Cleanup: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.95
   Source: Send Mail Task
   The SendMail task is initiated.: 0% complete
End Progress
Progress: 2006-08-16 18:59:42.09
   Source: Send Mail Task
   The SendMail task is completed.: 100% complete
End Progress
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  6:59:40 PM
Finished: 6:59:42 PM
Elapsed:  1.984 seconds

 

 

When I try to use the same command within SQL Server Agent Job using a CmdExec step I get the following error...

 

Description: The package is attempting to configure from the XML file "S:connectionscontacts.dtsConfig".  End Info  Warning: 2006-08-16 18:40:03.15     Code: 0x80012012     Source: contactsPackage Description: The configuration file name "S:connectionscontacts.dtsConfig" is not valid. Check the configuration file name.  End Warning  Warning: 2006-08-16 18:40:03.15     Code: 0x80012059     Source: contactsPackage Description: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed.  End Warning  Info: 2006-08-16 18:40:03.20  ...  Process Exit Code 1.  The step failed.

 

Thanks in advance for any help!!!

View 2 Replies View Related

RECEIVE Vs RECEIVE TOP(1)

Apr 6, 2007



In working through some examples, sometimes I will see this pattern for receiving messages: What is the purpose of the "nested" WAITFOR (RECEIVE? What is this actually doing? Is it receiving the same message in both RECEIVE?


WAITFOR (
RECEIVE @dh = [conversation_handle],
@message_type = [message_type_name],
@message_body = CAST([message_body] AS NVARCHAR(4000))
FROM [Queue]), TIMEOUT 1000;
WHILE @dh IS NOT NULL
BEGIN
IF @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
RAISERROR (N'Received error %s from service [Target]', 10, 1, @message_body) WITH LOG;
END
END CONVERSATION @dh;
COMMIT;
SELECT @dh = NULL;
BEGIN TRANSACTION;
WAITFOR (
RECEIVE @dh = [conversation_handle],
@message_type = [message_type_name],
@message_body = CAST([message_body] AS NVARCHAR(4000))
FROM [Queue]), TIMEOUT 1000;
END
COMMIT;



Other times I will see this pattern for receiving messages: Why do a RECEIVE TOP(1) instead of just a RECEIVE?



WAITFOR(RECEIVE TOP(1)


@conversationHandle = conversation_handle,

@messageTypeName = message_type_name,

@messageBody = message_body

FROM [Queue]), TIMEOUT 1000;



And other times I will see this pattern for receiving messages: What is the purpose of RECEIVING into an in-memory table when you can just process the message directly?



WAITFOR(RECEIVE


queuing_order,

conversation_handle,

message_type_name,

message_body

FROM [Queue]

INTO @tableMessages), TIMEOUT 1000;

IF (@@ROWCOUNT = 0)


BEGIN


COMMIT;

BREAK;
END



What is the difference between the three approaches from an architectural and performance perspective? I need to process messages as fast as possible and I'm not sure why or when each should be used. Also, does the timeout have any impact on how FAST messages will be processed, or is it exactly what it says - a timeout - if a message is not found within the period then the procedure will break?

View 5 Replies View Related







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