Hi,
I have a table with queries. I need to execute those queries and pass results into a variable. Then use that variable/result to execute other queries to make business decisions.
EXAMPLE:
TASK#1
TABLE-A :has queries below
select count(*) from employee
select count(* ) from mangers
I want to execute those queries and store results in @counts. How I execute all queries in table A and pass that to a variable?
TASK#2
Then I have another SQL task(may be SQL task) which use the value @counts make some decisions
If @count > 1 then pass
If @count <1 then fail
How can I do that?
I am still new to SSIS and not very familiar with variables. Any advice would be appreciated.
I am calling one SSIS package from another using the Execute Package Task. I also need to pass a parameter to the called SSIS package. Can I do this? If yes, how? If no, then what will be the work-around for this?
I have to generate a seq num and increment it by 1,i used execute sql task and wrote a proc using a variable(out) now this out variable has that value.Now i need to add a extra column to all the records coming in this current batch with the value in the variable.
I know that i can use derived column transformation ,but its not working,giving the following error.
the errors thrown are:
[OLE DB Destination [16]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Unspecified error". An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "ORA-01779: cannot modify a column which maps to a non key-preserved table ". [OLE DB Destination [16]] Error: The "input "OLE DB Destination Input" (29)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (29)" specifies failure on error. An error occurred on the specified object of the specified component. [DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (16) failed with error code 0xC0209029. 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 0xC0209029. [OLE DB Source [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. [DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. [DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
i have created a variable with scope as package,used that in execute sql task.then using precedence constrains added a data flow task to it.
In the data flow task between the source and destination i placed a derived column transformation.
the execute sql task is running fine,dataflow task is failing.In dataflow task also source and destination is failing but derived column transformation is working.
I have a simple Error row redirection (from an OLEDB Command) to redirect all rows in error to a Transform script and thereafter to a Flat file destination. This is via the red arrow (DF path) output from the OLEDB Command.
I don't understand why this leg executes even when there are no rows in error? Zero byte Flat Files get written out when there are no errors.
How come? Why would a path with a red arrow execute even when there are no errors?
Part 2 When I introduce some errors in the data to cause an integrity violation, and I hook up an OnError event handler, it is never raised even through the error rows are successfully redirected and written out to the Flat File destination.
So what consitutes an error for a Data Flow Task? Is an error raised by Sql Server for an integrity violation bubble up as an error in the SSIS package?
Maybe I'm missing something, but I can't find how to run multiple tasks in sequence while in Visual Studio debug mode. In DTS design mode I grew accustomed to right-clicking tasks one-at-a time, but in SSIS I find the additional step of having to exit Debugging mode after every task gets old after a while.
There must be a way to start execution at a certain task and have the package continue all the way to some other specified task. It would also be nice to have every task in a Group execute in sequence and stop (even if connections continue beyond the group). I could even settle for repeatedly clicking the Continue button in Debug mode, but it's always grayed out when the current task is finished!
I have two servers, A for dev and B for production.
On server A I developed a project containing a SSIS package using SQL Server business intelligence development studio. The package runs fine from the BIDS and also when I save it to SQLServer itself and run it as a scheduled job using the SQLServer Agent.
All ready to roll out to Server B I thought, so I then saved the .dtsx file to a shared network drive.
On Server B, I created a empty project with the same name as it had on Server A. I then imported the .dtsx file into the project using project > add existing item.
The package appeared to import ok but I now cannot execute any of the data flow tasks in isolation. If I right click on them, there is no option to 'execute task' as there should be, it is not greyed out, it's not there at all.
Also, if I attempt to debug the whole package I get a message saying 'This document is opened by another project'.
Can anyone help with this as my deployment to live isn't going very well to say the least!
Both server A + B are 32-bit 2005 std edition SP1 on W2003 Server std edition SP1.
In my ETL project, l need to extract rawdata using winzip, and the DOS command l use is c:program filesWinzipwinzip32.exe -min -e c: awdata est.zip c: awdata where -min for minimize and -e for extract, c: awdata est.zip for source file and c: awdata for destination. It works fine by using DOS command.
l configure the Execute Process Tasks with the following parameters, i.e. RequiredFullFileName : TRUE Executable : c:program filesWinzipwinzip32.exe Arguments : -min -e WorkingDirectory : c:program filesWinzip StandardInputVariable : User::gsRawFile StandardOutputVariable : User::gsDestDir Where User::gsRawFile = c: awdata est.zip and User::gsDestDir = c: awdata
I have a Package and a DataFlow Task. The Package has TransactionOption=Required. The DataFlow Task has an OLE DB Source and an OLE DB Destination. The DataFlow Task has TransactionOption=Supported. The package executes on a Workstation and DataSources for the OLE DB Source and the OLE DB Destination are on a Server.
After the package had been launched an error message showed:
[OLE DB Destination [43]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DWH_Destination" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. [DTS.Pipeline] Error: component "OLE DB Destination" (43) failed the pre-execute phase and returned error code 0xC020801C.
[Connection manager "DWH_Destination"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D024 "The transaction manager has disabled its support for remote/network transactions.". [Connection manager "DWH_Destination"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8004D024.
If I set TransactionOption=NotSupported in the DataFlow Task then the package executes successful.
I added tasks to refresh two tables (delete from, insert into, update) to an SSIS project . I have them running from the WinXP scheduler. The issue:
In dev the tasks integrate and execute successfully from scheduler
In prod I can right click and execute each of the three tasks without any problem, but the same tasks cause my project to fail when executed from scheduler
Questions: Any ideas about what I am failing to see? How do I get a meaningful log messages from the tasks that are failing?
Thanks for your ideas...
Installed Edition: IDE Standard
SQL Server Analysis Services Microsoft SQL Server Analysis Services Designer Version 9.00.1399.00
SQL Server Integration Services Microsoft SQL Server Integration Services Designer Version 9.00.1399.00
SQL Server Reporting Services Microsoft SQL Server Reporting Services Designers Version 9.00.1399.00
Microsoft Visual Studio 2005 Version 8.0.50727.42 (RTM.050727-4200) Microsoft .NET Framework Version 2.0.50727
Installed Edition: IDE Standard
SQL Server Analysis Services Microsoft SQL Server Analysis Services Designer Version 9.00.1399.00
SQL Server Integration Services Microsoft SQL Server Integration Services Designer Version 9.00.1399.00
SQL Server Reporting Services Microsoft SQL Server Reporting Services Designers Version 9.00.1399.00
I have some "Execute T-SQL Statement Tasks" in a package. I would like to run this same package on another SQL Server without having to change it on the other server. Since the server name can be given when setting up the connection, I think if I leave the server name out then the package could run on any server? Is my assumption correct?
The variable 1 is created as int32 and variable 2 is created as dattime.
When i execute the SQLtask, I get error:
[Execute SQL Task] Error: Executing the query "Exec mysp 'table1',OUTPUT,OUTPUT" failed with the following error: "Error converting data type nvarchar to int.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
What am i missing. I tried changing the data types adding the input variable also as a variable in the mapping. Nothing seems to work. Any ideas please?
I am trying to programmatically execute a package that contains an Execute SQL Task component bound to a variable for its "SqlStatementSource" property (via an expression). The variable is of type String and contains a simple value of "SELECT 1". The Execute SQL Task contains an expression that sets the SqlStatementSource property to the value of this variable.
The package runs fine when I execute it via dtexec or BIDS, but when I attempt to run it via the object model, I receive the following error message:
The result of the expression ""@[User::Sql]"" on property "SqlStatementSource" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
I did a search on this forum and noticed quite a few threads about this same issue, but no explanation/solution. We have quite a few packages that have dynamically constructed SQL statements for Execute SQL Tasks, and they are all failing to run via the object model. Is there something that I am missing?
I have a requirement to read an encrypted file as a data source. I am not allowed to save an unencrypted text file version on disc at any time for any length of time, therefore I created a custom source component that reads an encrypted csv file, decrypts it, and then passes each row of data to the pipeline and ultimately to an ole data destination. Basically it is just a text file reader with an added class that adds functionality that decrypts the file before the component sets columns or reads rows.
The custom component, “Encrypted File Source”, has a custom property “encryptionkey” with the encryption required flag set to true (code below) and is declared as eligible to be set in the expressions.
IDTSCustomProperty100 EncryptionKey = ComponentMetaData.CustomPropertyCollection.New(); EncryptionKey.Name = "EncryptionKey"; EncryptionKey.Description = "Secure String key value to decrypt the file"; EncryptionKey.Value = string.Empty; EncryptionKey.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY; EncryptionKey.EncryptionRequired = true;
I want to be able to set the password for the encrypted file in the SQL Agent job that executes the SSIS project. This means I have an environment with a variable, “DataPassword”, that is set to sensitive. It maps to a Project parameter in the SQL Agent job that is also set to sensitive. And I now I want to access that sensitive Project Password inside my data flow, specifically in the Encrypted File source task that I created and set my EncryptionKey to that Project Parameter.
The problem is that SSIS says.
"expression cannot be evaluated. ... The Expression will not be evaluated because it contains sensitive parameter value "$Project::DataFilePassord" . Verify that the expression is used properly and that it portects sensitive information" ((Microsoft.DataTransformationsServices.Controls) "<v:shapetype coordsize="21600,21600" filled="f" id="_x0000_t75" o:preferrelative="t" o:spt="75" path="m@4@5l@4@11@9@11@9@5xe" stroked="f">
[Code] ....
I am using SQL Server 2012, on a windows 7 box with VS2010 premium.
hi, I am new to sql server reporting services. When i pass more than 30 parameters ,my report is showing parameters which is not a desired one even though i am making showparameterPrompts property to false.
can any one tell me how to pass more no of parameters to the Report.
MY DBA wrote a trigger, that upon hitting the delete button for a payment, the payment would be transferred to another table and the trigger would call a sp as well. since the trigger is calling the sp, I should only supply the parameters.
My report uses an OLAP query with a (multi-valued) parameter, the generated MDX query contains something like "...ON ROWS FROM ( SELECT ( STRTOSET(@Region, CONSTRAINED) ) ON COLUMNS FROM [cube]) ...".
The report works fine using the Report Manager frontend. Now I want to pass this parameter directly within the query string:
But I cannot get Reporting Services to accept the parameter via URL access (instead of complaining about a missing parameter value). I've already tried quoting/encoding the brackets, spaces, and the ampersand, putting the dimension in curly braces and/or quotes... No success.
Normal (url-encoded) query parameters work fine, I just can't pass OLAP parameters (dimensions like "[foo].[bar 123].&[baz]") this way. I think ReportServer doesn't like the way I'm trying to quote them...
Hi Gurus, I want to pass 0 value to all subreport parameters in the beginning when main report is run. This is because i dont want subreport query to run.
when user clicks on '+' in main report, i want to pass the parameter values so that the subreport will run with correct parameters for that particular main report row.
My purpose of doing this is to achieve speed with subreport
I have recently had a few reports that are built around the userid parameter start failing to pass the userid parameter. If I run the report and hard code the userid everything runs just fine, but I am not able to collect the userid when the user is logging into the report server. Any thoughts or suggestions?
how do i pass the paramenters and storedprocedure to dataview from code-behind? the below code have sqldatasource control but i want to pass through code-behind everything...
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display." DataKeyNames="NewsId"> <Columns> <asp:BoundField DataField="NewsId" HeaderText="NewsId" InsertVisible="False" ReadOnly="True" SortExpression="NewsId" /> <asp:BoundField DataField="PostDate" HeaderText="PostDate" SortExpression="PostDate" /> <asp:BoundField DataField="PostedBy" HeaderText="PostedBy" SortExpression="PostedBy" /> <asp:BoundField DataField="PostedByName" HeaderText="PostedByName" SortExpression="PostedByName" /> <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" /> <asp:BoundField DataField="Body" HeaderText="Body" SortExpression="Body" /> <asp:BoundField DataField="LastUpdated" HeaderText="LastUpdated" SortExpression="LastUpdated" /> <asp:CheckBoxField DataField="IsVisible" HeaderText="IsVisible" SortExpression="IsVisible" /> </Columns> </asp:GridView> i have storedprocdure which accepts 4 parameters 3 paramenters which user will supply and 1 parameter will be supplied from code-behindwhat i mean by that is:the 4 parameters storedprocedure accepts is:empid, start_date, end_date (user will supply those 3 parameters)internal_id - which internally pass along with other 3 parmeterssomething like this:internal_id, empid, start_date, end_dateany thoughts?thanks.
UpdateCommand="UPDATE PsnQualifications SET quantity=@quantity WHERE rowstatus=1 and qualId=@original_qualId"
DeleteCommand="UPDATE PsnQualifications SET rowStatus=0, lastUpdateOn=getdate(), lastUpdateBy=@createdBy WHERE rowstatus=1 and qualId=@original_qualId"
I don't know how to pass parameters required (dates) to a query1, if I'm using a subquery (query2) which is using the results of query1, but I'm not showing that field on that subquery (query2)
Example
table1 id - autonumeric id_user - id from user dates - date of register
table2 id - user id name - user name
query1 SELECT Table1.id_user, Count(Table1.id_user) AS CuentaDeid_user FROM Table1 WHERE (((Table1.datess) Between [begining] And [ending])) GROUP BY Table1.id_user ORDER BY Table1.id_user;
subquery (query2) SELECT query1.id_user, query1.CuentaDeid_user, Table2.name FROM query1 LEFT JOIN Table2 ON query1.id_user = Table2.id;
This is just an example, the think is that I want to know that if it's possible to pass the parameters requested in query1 from the SQL of the subquery (query2)?
In a matrix report with column and row groupings, how to pass the parameters to the drill-through report? The column grouping could open up into multiple columns and the same to the row groupings. I have a navigation link to a drill-through report on a total field in the matrix report and the drill-through report is required three parameters, one from the parameter from the matrix report (this one I can map to easily), second parameter is one of the value from the column grouping field and the third parameter is one of the value from the row grouping field. How to select those values from the column/row grouping.
I am using a parameterized select query as displayed below to fetch values from source.
SELECT A.Account_GUID, M.Merchant_GUID, H.Household_GUID, B.BankAU_GUID, SR.SalesRep_GUID, E.Entitlement_GUID, I.Income_GUID, Exp.Expense_GUID, SP.Sales_Product_GUID, P.Product_cd, SUM(S.Sales) AS Monthly_gross_MC_VI_amt, SUM(S.Sales) - SUM(S.[Returns]) AS Monthly_net_MC_VI_amt, SUM(S.SaleTxns) AS Monthly_gross_MC_VI_tran_cnt, SUM(S.SaleTxns) - SUM(S.ReturnTxns) AS Monthly_net_MC_VI_tran_cnt FROM Account AS A LEFT OUTER JOIN dbKAIExtract.dbo.tblSales_STG AS S ON A.Account_No = S.AccountNo And S.BucketNo = ? And S.ProductCode in ('01','02') LEFT OUTER JOIN Merchant AS M ON A.Account_No = M.Account_no INNER JOIN SalesRep AS SR ON SR.Rep_SSN = isnull(A.rep_SSN,'000000000') INNER JOIN Household AS H ON A.Account_No = H.Account_no LEFT OUTER JOIN BankAU AS B ON A.Assigned_AU = B.AU_No LEFT OUTER JOIN SalesProduct AS SP ON A.Account_No = SP.Account_no And SP.Reporting_Interval_Id = ? LEFT OUTER JOIN Entitlement AS E ON E.Account_no = A.Account_No AND SP.Product_Cd = E.Entitlement_Card_Type LEFT OUTER JOIN Income AS I ON I.Account_no = A.Account_No And I.Reporting_Interval_Id = ? LEFT OUTER JOIN Expense AS Exp ON Exp.Account_no = A.Account_No And EXP.Reporting_Interval_Id = ? LEFT OUTER JOIN Product AS P ON P.Product_cd = SP.Product_cd WHERE (A.current_ind = 1) AND (SR.current_ind = 1) GROUP BY A.Account_GUID, M.Merchant_GUID, H.Household_GUID, B.BankAU_GUID, SR.SalesRep_GUID, E.Entitlement_GUID, I.Income_GUID, Exp.Expense_GUID, SP.Sales_Product_GUID, P.Product_cd
My problem is, I am not able to assign any variables to parameterized query. Can any body guide how to assign respective variables to the parameterized query. I have the above query as a part of OLE DB Source step within Data flow task.
I am working on SSRS. I need to open a new report from one report when user clicks on some particular summerized count link.
Its a sort of drilled down report. I am not getting how to pass the respected Ids (more than one) to the next report when user clicks on the link in the 1st report. These ids I want to use as a parameter (multiple) in the 2nd report to dump the rows from the database.
I'm having Parent package, which has For each loop which returns Val1 , Val2 , Val3 and Val 4
I want to create child package which accepts one parameter and call this package inside For each loop. Inside loop i want to call 4 child packages(same pakage) simultaneously.
If i create child package which access parent package variable, i will have to modify modify each child package. I cannot reused child package.
I want to know is any way or any possible way could pass the report parameter on this kind of porperties or this code? For some requirement, I just can use Jump to URL, but hope can pass the parameters on "jump to url" properties.
