Newbie question. I'm new to using variables in SSIS. Are there any good examples around of how to return an @@identity value from a procedure and then use it globally within an SSIS package for multiple "execute SQL" tasks? I've gone though the documentation but don't find it to be very complete.
GC
I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value.
I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;
I have almost 50 references to these parameters in the query so a substitution would be helpful.
Dan
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
Here are the task steps.
[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.
[Execute SQL Task] - Log an entry to a table indicating that the import has started.
[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.
[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.
If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.
If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]
/*
The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.
If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.
Otherwise it returns a FALSE value in the IsNewFile column.
Example:
exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0
*/
@ProcessName varchar(50)
, @FileCreateDate datetime
, @IsNewFile bit OUTPUT
AS
SET NOCOUNT ON
--DECLARE @IsNewFile bit
DECLARE @CreateDateInTable datetime
SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName
IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)
BEGIN
-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.
IF (@FileCreateDate > @CreateDateInTable)
BEGIN
-- This is a newer file date. Update the table and set @IsNewFile to TRUE.
UPDATE tbl_ImportFileCreateDate
SET FileCreateDate = @FileCreateDate
WHERE ProcessName = @ProcessName
SET @IsNewFile = 1
END
ELSE
BEGIN
-- The file date is the same or older.
SET @IsNewFile = 0
END
END
ELSE
BEGIN
-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.
INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)
VALUES (@ProcessName, @FileCreateDate)
SET @IsNewFile = 1
END
SELECT @IsNewFile
The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.
General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
Result Set is empty.
Expressions is empty.
When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: 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.
SSIS package "MyPackage.dtsx" finished: Failure.
When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) 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.
SSIS package "MyPackage.dtsx" finished: Failure.
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?
The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.
Thanks for your help.
hi,
I have one variable at package level in ssis.
I want to use this in sql statement of execute sql task.
but i m not able to find how to do this.
my sql statement is
select * from Var
where Var is variable at package level which is string.
please help me
Thanks,
Chetan S. Raut.
I need to be able to, at run time, set the location of a DataSource in an SSIS package.
I've searched and searched and beat my head against the wall till I'm bloody but I can't figure this out.
Can anyone please point me in the right direction?
I am using an oledb source. the query is coming from a variable. The database to which oledb source is connected is Oracle.Mt variable contains the following query:
"SELECT REQUEST_ID FROM COMPLIANCE_REQUEST
where LOAD_TMSTP between (select max(END_TMSTP) FROM BATCH_JOB_LOG) and
TO_DATE("'+RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System:tartTime] ), 2) + "-"+RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System:tartTime] ), 2) + "-" +RIGHT("0" + (DT_STR,4,1252)DATEPART( "yy" , @[System:tartTime] ), 2) + " " +RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System:tartTime] ), 2) + "." +RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System:tartTime] ), 2) + "." +RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System:tartTime] ), 2) +'",'DD-MM-YY HH24.MI.SS')"
I am getting error as :
Error at Data Flow Task [OLE DB Source 2 [2177]]: No column information was returned by the SQL command.
Error at Data Flow Task [OLE DB Source 2 [2177]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4A.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E4A Description: "Command was not prepared.".
Error at Data Flow Task [OLE DB Source 2 [2177]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
Hi
I am importing data and wand to assign a fixed value to an output col. the fixed value should be a uniqueidentifier maintained in a variable so that it can easely exchange prior to execution. My problem is that I realized that there is no variable type for uniqueidentifier. When trying to store the guid in a string variable and then trying to cast back using a script component i received a casting exception telling me that a string cannot be converted into a guid.
I dont want to use an object type because then i would have to manually read out the variable a execution start.
Does anyone have experience using varialbes and guids?
Thanks a lot
Alex
Hi,
I have a SSIS with a set of SQL Tasks that executes SPs, I need to add a precedence constraint which handles case of error, but as way to use only one SQL Task for Error... I think about using a variable that stores the SQL Task Name in turn and that can be used as a Parameter in the SQL task that I use with "sp_send_dbmail" to notify error..
That is..
________
|_sql 1__| --------
|
| ___________________
________ | sql handling Error |
|_sql 2__| -------->> |___________________|
|
|
________ |
|_sql 3__| --------
Need that Every SQL Task send its name as a parameter to my
"Sql handling Error Task" to be use with something like this code..
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profile',
@recipients = @MailRecipient,
@body = 'Error in' + @SQLTaskName,
@subject ='Error al ejecutar ...' ;
I want to receive the name of the failing task in message body or subject.
I need to implement it also is projects with SQL 2000 (with Global Variables). Would appreciate any help, Please : )
Can I return a value in a variable from a SSIS program back to C# after the SSIS program is run from C#?
View 1 Replies View RelatedIs there a way to find the content of a variable in SSIS while the package is executing?
------------------------
I think, therefore I am - Rene Descartes
how to pass the numeric(12,0) data type to a variable in SSIS? what kind of variable data type should I choose?
I am trying to assign object_key column ( numeric(12,0)) to a variable in SSIS
If i select int32 , it keep giving me an error:
Error: 0xC001F009 at Row by Row process: The type of the value being assigned to variable "User::Object_Key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Hi all of you,
That's an easy one. I've got a Send Mail task which might send a message in plain text along with a SSIS variable.
Something like that:
'La tabla "' + SUBSTRING( @[System:ackageName], 3,20) + "' se ha cargado correctamente'
TIA for that,
I am trying to execute a ssis package, where a user will be able to excute the package from an ASP page, the user will select a file, this will be the source file for the SSIS package, this will change when ever the package is executed, I was thinking of just using a sqlClient.sqlCommand and editing the command text and excuting it that way, or through a SP, would this be the best solution? Is there a better solution? and if not what would be the SQL command to execute the package, along with editing the variable?
View 4 Replies View RelatedHello
Which variable type in SSIS maps to bigint in SQL Server 2005?
I am returning a single column value of type bigint from SQL Server and want to store that in a varible in SSIS, what datatype should I use?
I tried Int32, Int64, Uint64 and it did not work. Did I do something wrong?
Thanks
The source table has timestamp column (Modified Date), which gets upated with each modification via an update trigger.
I store the MAX(modifieddate) in a DeltaExtractionHistory Table in the staging database for each extraction or package execution. Then in the next run, I need to pick the coulmns that have a greater datetime, than the last extracted MAX(ModifiedDate) in the DeltaExtractionHistory table for delta processing.
The OLEDB source has the following SQL statement:
SELECT * FROM [dbo].[MyTable] WHERE [ModifiedDate] > ?
with the following parameter:
Parameter 0 : dtLastModifiedDate
dtLastModifiedDate is an SSIS variable of type Datetime. I read the value of MAX(ModifiedDate) in this variable in a SQLExecuteTask prior to running the data flow task that contains the OLEDB source task.
The problem is - instead of extracting greater datetime, it also extracts the MAX(Modifieddate), which was already extracted in the last run. In other words instead of functioning as [ModifiedDate] > ?, it is functioning as [ModifiedDate] >= ?
because of this, the primary key gets violated on the target table. I think this is happening because the date is stored as "2007-03-01 11:56:34.550" in the table, but SSIS variable shows it in the data viewer as "01/03/2007 11:56:34 PM". It truncates the milliseconds and picks the same date again, because it thinks milliseconds .550 is greater than 000. But I am not 100% sure, may be it is only showing this in the dataviewer, but it contains the ms part as well. I tried using
SELECT * FROM [dbo].[MyTable] WHERE [ModifiedDate] > ? AND DATEPART(ms, MODIFIEDDATE) > DATEPART(ms, ?)
But it fails with an error that the provider was not able to parse the SQL and try storing the statement in the SQL variable. I could not succeed in that - the statement never parses with the parameters.
Can someone please help in this.
Thanks
Does anyone have an example of how to use a SSIS variable in an openquery stmnt. I am having a hard time finding a way to pass the variable to the openquery.
Thanks,
hi,
i have a global variable that is a file dir param lets say: d:input2006_07_18.bcp.
this param supposed to be built from other 3 params i.e: day, month and year.
how do i build it dinamically i need the exact syntax. i have already put the filedir param as an evaluated expression but when i try to do somthing like:
@[User::Filename] + @[day] + @[month] + @[year] i get an error, although i succeed in putting only one param at its expression i.e: @[user:filename].
the question is how do i build the parameter that will b built from these 3 params in its expression.
Brian, i will b happy to hear from you in regard.
thx,
Tomer
Is it possible to add a variable in SSIS like
name of variable: myVar
Scope: Data Flow Task
Data Type: String
Value:SELECT hello FROM blah WHERE (azerty = @[User::pda]) AND (qwerty = @[User::phone])
@[User::pda] and @[User::phone] are also variables in SSIS just like the myVar I made
I know I'm doing something wrong with the data type because it's stores the whole select statement as a string
Help
Worf
Hi All,
may be i'm asking a question that was discussed for billion time, but i can't find exact answer.
So i have access database that generates .xml or xls files by pressing button. So user select from drop down menu id ( e.g. 100,101,102,103 etc) and then file 101.xml or 102.xml or 101.xls or 102.xls is generating.
i need SSIS package to be configured to get filename is variable and then use insert data from xml or xls file to the related table
I don't understand how to pass file name through variable to SSIS package and then work with so called "variable".xls or
"variable".xml file. Could you please give detailed answers if it's possible?
Your early reply will be very much appreciated
Thank you
Is there anyway to pass data from a sql database to a variable in a ssis package. I'm trying to get a few fields from a sql database into some variables in my package and send an email using these variables with the send mail task?
Thanks,
Hi
I need to use a variable as column in SQL statement in Execute SQL task of integration services. I am setting Parameter Setting to map variable use it in the query like; select ? , col1name from tablename. But its not working.
Anybody having any idea; would be of great help.
Thanks,
Salman Shehbaz.
My requirement demands me to use a extract based on a date. And on success i need to increment this date by a day.
So i have a declared two variables, vDate and vSourceSQL. I am using the sql statement from variable in the data access method. My package is successful.
Reading through the forum and after following this blog
http://blogs.conchango.com/jamiethomson/archive/2005/02/09/SSIS_3A00_-Writing-to-a-variable-from-a-script-task.aspx
i now have a script task which will increment my "vDate" variable and i am calling it as a post execute task. I have posted the error below. Tried to get atleast the sample provided to succeed, but.... this is what i get.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Flat File Destination" (800)" wrote 12 rows.
Error: 0xC001405C at Script Task Data Flow Task: A deadlock was detected while trying to lock variables "User::vMyVar" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.
Error: 0xC001405D at Script Task Data Flow Task: A deadlock was detected while trying to lock variables "System::InteractiveMode" for read access and variables "User::vMyVar" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.
Error: 0x2 at Script Task Data Flow Task: The script threw an exception: A deadlock was detected while trying to lock variables "User::vMyVar" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.
Task failed: Script Task Data Flow Task
Warning: 0x80019002 at OnPostExecute: The Execution method succeeded, but the number of errors raised (5) 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.
Warning: 0x80019002 at SqlCommandFromVariable: The Execution method succeeded, but the number of errors raised (5) 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.
SSIS package "SqlCommandFromVariable.dtsx" finished: Failure.
The program '[2316] SqlCommandFromVariable.dtsx: DTS' has exited with code 0 (0x0).
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
I am trying to migrate database from old structure to new structure usign SSIS.
The table in new db have extra field that i need to assign it using variable. this is because i have few customer that having different variable value. (meaning for 1 customer, the variable will be fix for all the tables in the database)
my question, without using the Execute Sql Task, can i assign the variable into the the old db destination?
eg my data flow Task is : OLE Db Source - Derived Column - OLE DB Destination.
Example data
Old structure (key = txnID)
---------------------
TxnID
ChequeNo
Bank (chq Bank - Bank in Bank)
Amount
New Structure (key = TxnID & CoCode)
-------------------------
TxnID
ChequeNo
ChqBank
BankInBank
Amount
CoCode
TQ.
Hello,
When I try to execute a Integration Services with parent variables, always appears this warning for each parent variable:
Configuration from a parent variable "%1!s!" did not occur because there was no parent variable collection.
The IS is executed correctly, but we don't know the reason why this warning appear, if the value is correctly assigned.
Thanks,
Pablo Orte
Hello everybody,
I'm executing SSIS package, like a datareader source in a report in SSRS. Everything it's ok. But, I'm using
@[System::UserName] variable like a add column to save the user that ran the package.
But it's incredible, when the user execute the report, the system don't save the login that opened the browser, neither the user that run the ssis service or user for ssrs service. This is catching the user that opened session in the host where Sql server was installed.
I need catch the user that run the report, how can i do this?
Att,
JULIAN CASTIBLANCO P
MCTS SQL SERVER 2005
BOGOTA, COLOMBIA
I have a table which contains a column ([Connection String]) that has every connection string for the SQL instances on our estate.
I have a package which will import data from a single instance, but how do I set the connection manager so that it uses a variable and is populated by a selecting the connection strings from my central database?
Do I use a cursor to select the next connection string?
How do you pass a value in a parameter to a variable ?
View 2 Replies View RelatedHow to use table variable in SSIS 2012, is it possible to use table variable in SSIS.
I want to insert some results from EXECUTE SQL TASK to this table variable and use this variable in OLEDB SOURCE task in data flow where it is used in SQL query with IN Operation.
The table variable contain multiple values like '100','234','XYZ' Is it possible to do or is there any other solution to achieve this?
Since SSIS is developed in Visual Studio is there a way to view the variable values as they are passed into SSIS? In Visual Studio I know if you hover over the variable a popup appears with the value in the variable.
I am having an issue with a variable value that is supplied through an "Execute SQL Task" it is a Directory path that is entered in through the application and used to determine where to pick up files to transfer. I have entered in the path using Fully Qualified(D:ImportExportCentralPrinting) and UNC (\SQLDEVELOPImportExportCentralPrinting) through both methods SSIS is telling me no files are available in the directory path. When I know for fact they do exists.
I would really love to see the variable values that are being used can anyone out there help?
Thank you all,
Mike
Michael Alawneh, DBA
Hi,
I am creating SSIS package which using Execute SQL Task to which I am supplying one .sql file code is
delete from Test where ID = @ID
I defined @ID variable to SSIS and also path where .sql file placed but i am not able to execute this package i am getting error like can not find C:@Path file......
As i got information that passing such variable to .sql you need ADO.net connection so I changed SQLSERVER Database connection string to ADO.net .... after that when i set hard core value for these variable i.e for @ID and @Path then it runs sccessfully but by setting parameter i am getting above error
Any suggestion that will be gr8 help for me
T.I.A
Hello everyone,
This is my first post. :-)
I've been having an error with Script Tasks in SSIS, where the error is every time I use a For Each In Dts.Variables, it always seems to crash. I'm trying to log the variables before I proceed in any other tasks. I'm passing in one value as a ReadOnlyVariables.
Here is the snippet of code:
Dts.Log(String.Format("{0} variables:", Dts.Variables.Count), 0, Nothing)
For Each v As Variable In Dts.Variables
Dts.Log(String.Format("{0} = {1}", v.Name, v.Value), 0, Nothing)
Next
It's very strange because the Dts.Variables.Count gives me the correct number of items that I have passed in, but after I step into the For Each, I get this for Dts.Variables.Item:
In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user. Microsoft.SqlServer.Dts.Runtime.Variable
And as soon as I step into the next line, it crashes with this error:
Value does not fall within the expected range.
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables90.GetEnumerator()
at Microsoft.SqlServer.Dts.Runtime.Variables.GetEnumerator()
at ScriptTask_932938a13af547149ade0331cf39ecfe.ScriptMain.Main()
My colleagues have tried this snippet of code and it worked fine on their machine, only mine has this error :-(
I tried to do several searches online and they recommended me to:
Get the latest SP (I have all the updates now for .net and SQL)
Re-register some .dll's such as dts.dll/pipeline/etc
Change/toggle the PreCompileBinary
Alter the script a little and rebuild.
None of these suggestions have had any success on this error that I'm having. I was wondering if anyone had any insight or had any similar problems as me. Any help would be much appreciated!
Thanks,
-Simon
<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->
Greetings,
I have written a SSIS package which does the following:
- An 'Execute SQL Task' uses an OLEDB connection to execute a Stored Procedure in SQL Server 2005
A SSIS user defined variable named @SourceSQLStatement is passed to the Stored procedure as an OUTPUT
This variable is a string Data Type
- The Stored Procedure generates an SQL command and saves it in the variable
This variable is defined as @SourceSQLStatement nvarchar(4000) OUTPUT
The varible is then returned to the SSIS package
When the package is executed the variable does not contain the entire SQL command.
When I check in the Locals window during Debug, the contents seem to have been truncated:
+ User::SourceSQLstatement {SELECT a.FailureID, a.ExceptionHandlerID, a.FailureTypeCd, a.AccountNbr, a.AccountNm, a.CaseNbr, a.CustNm, a.AssociateNm, a.TargetSysNm, a.FailureDt, a.FailurePointTxt, a.SubmittedByNm, a.ErrorMsgTxt, a.CreateId, a.CreateDt, a.UpdateId, a.UpdateDt FROM GSPIntegrationException a WHERE a.CreateDt >= (select max(ETLC.RunDt) from X141572_ETLSTAGING.dbo.ETL ETL inner join X141572_ETLSTAGING.dbo.ETLControl ETLC on ETL.ETLID = ETLC.ETLID inner join X141572_ETLSTAGING.dbo.ETLRun ETLR on ETLC.ETLID = ETLR.ETLID an} String
The fully generated SQL command does not exceed the 4000 character length provided in the stored procedure.
Has anyone encountered this issue before?
Any help would be greatly appreciated
Thank You