67 questions
0
votes
0
answers
120
views
Failed to create an IDataAdapter object
I have SSIS package which consist Execute SQL Task step. Where package variable contains SQL query that executes on predefined SQL Server. There is no parameter mapping nor any more complex config.
...
- 327
1
vote
1
answer
789
views
Not able to use temp tables in one "Execute SQL Task" after another in SSIS Control Flow
I have two tasks in control flow. The first task is in sequence container and creates multiple global temp tables. The second task uses these temp tables. Both the tasks are in 'Execute SQL Task'. ...
- 11
0
votes
1
answer
116
views
HI, I am not able to call redshift stored procedure in SSIS ExecuteSqltask
I am getting this error, while calling stored procedure of Redshift. we are using oledb connection
Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "call dev....
- 1
0
votes
1
answer
99
views
Microsoft Visual Studio 2019 Execute SQL Task Editor Syntax Errors Encountered
Trying to build a query on Execute SQL Task Editor.
SELECT ? = CONVERT(VARCHAR(10), COUNT(DISTINCT Email))
FROM [xxx].[dbo].[DataTEST] AS D_Test INNER JOIN
[xxx].[...
- 1
0
votes
1
answer
260
views
What does a red circle on a task mean in an SSIS package?
I am creating a similar package to this package but I don't know the meaning of the red mark in the pic below:
- 15
0
votes
1
answer
219
views
SSIS/DTSX/ETL error: "The data in the reparse point buffer is invalid."
I have an SSIS/DTSX/ETL which is giving me the following error in a "Task" in the "Control Flow":
[Execute SQL Task] Error: Executing the query "" failed with the ...
- 9
3
votes
1
answer
8k
views
How to use output parameter in SSIS execute sql task from a stored procedure
Using a parameter, @IsSuccess in stored proc. How to use that @IsSuccess parameter in SSIS Execute SQL Task and take that as output from that component to another component?
- 33
2
votes
1
answer
641
views
SSIS package insert data from CSV file to a staging table and then move from a staging table to a main table with a Site Column
I have a CSV file that successfully moves from Source File to a staging table in a Data Flow Task from a sequence container. There will be a few sequences of this. I then need to move this data from ...
- 155
2
votes
1
answer
2k
views
SSIS - how to capture PRINT message?
The Execute SQL task calls a procedure that has a PRINT command inside. How can I display these messages at once in the progress tab (or in output)?
- 191
1
vote
1
answer
459
views
File counter expression in SSIS using execute process task output variable
I have a console application in c# that downloads files from a website.
I have created a variable named Filecount that counts the number of files downloaded in that instance.
In SSIS, I have set the ...
2
votes
1
answer
3k
views
Extract filename and update table in SSIS Foreach Loop Container
I have an SSIS package that has this Foreach Loop Container(with File Enumerator) that reads from a folder with multiple CSVs file and then upload the data into a flat table.
This is working fine but ...
- 71
1
vote
1
answer
382
views
Delete Fails using ODBC in SSIS
I changed an Execute SQL Task targeting my default, local SQL Server instance from OLE DB to ODBC. The SQL is
delete from tablename
The ODBC version works fine when the table has records but fails ...
- 61
2
votes
2
answers
2k
views
Error with execute SQL task when using output parameter
I want to retrieve the latest date from a SQL Server table.
In an "Execute SQL task" I have the following SQL Statement:
SELECT ? = MAX(MYDATE) --SQL data type of this column is datetime
...
- 9,886
1
vote
1
answer
248
views
SSIS return total committed rows count in execute sql task
I have an Executed SQL Task with SQL query to delete the data from table. I am using while loop to delete the data in batch and try/catch to handle the failure.
In Execute SQL Task I want to return ...
- 219
0
votes
0
answers
324
views
Executing SSIS package using TSQL commands in visual studio
I am trying to execute the SSIS package using TSQL commands (as a part of asynchronous execution testing) using Execute SQL task but I am getting error with related to server authentication and ...
0
votes
1
answer
433
views
SSIS execute sql statement multiple steps
In my SSIS pacakge I have an Execute SQL Task with 2 statements:
statement 1:
select coalesce ( max (id), 0)+1 as ID from AAA
statement 2:
Insert into BBB (id) values (?)
In the first statement, I ...
- 195
0
votes
1
answer
892
views
Insert only few columns from exec (SQL) when column name is not fixed
I have the below code:
IF OBJECT_ID(N'tempdb..#VALS') IS NOT NULL
BEGIN
DROP TABLE #VALS
END
IF OBJECT_ID(N'tempdb..#Tbl_Eval_Temp') IS NOT NULL
BEGIN
DROP TABLE #Tbl_Eval_Temp
END
IF exists (select ...
- 119
0
votes
1
answer
2k
views
Extract result set from a execute sql task and store it in a excel file?
I have a execute SQL task, that needs two input variables and a output variable to run. The SP returns a table that i'm storing in ResultSet parameter. I want to store the values in this ResultSet ...
- 342
0
votes
1
answer
533
views
Error while trying to execute a stored procedure through "Execute SQL task" in SSIS
I have created a stored procedure. While trying to execute it through SSIS I'm running into problems.
The definition of my stored procedure looks something like this:
CREATE PROCEDURE dbo.[...
- 342
1
vote
1
answer
2k
views
SSIS Error: Disconnected recordsets are not available from ODBC connections
I have one EXECUTE SQL TASK in SSIS package ,
Task Type: Execute SQL TASK
Data Source Type: ODBC for MySql (DSN)
Error :
[Execute SQL Task] Error: Executing the query "select * from
nasb_dev....
- 99
0
votes
1
answer
593
views
SSIS Execute SQL Task - multiple sources in sequence and not in parallel
I have a massive SSIS package with an Execute SQL task that reads data from 14 different sources, runs them through a Union All, and then through all the same transformations.
Problem is, running 14 ...
- 193
1
vote
1
answer
4k
views
Execute SQL Task Error: Executing the query failed with the following error: "Incorrect syntax near ''."
I am working on a SSIS package that rejects already loaded files & load only new files to table.
I used for each loop and exceute SSQL to validate if the files are already loaded. When I ...
- 11
0
votes
1
answer
618
views
Active Directory Authentication using JWT Token connection string issue
I am trying to run a SQL script to create a user role in Azure SQL Database using the task Azure SQL database deployment by specifying the connection string with JWT token.
From the Microsoft ...
- 1
1
vote
1
answer
1k
views
Create SSIS- SQL Task Oracle SQL Statement With WHERE Clause "IN" keyword
I really hope I can get some input on this. I'm trying to create an SSIS package with SQL Statement with "IN" in WHERE clause (ex: SELECT * FROM Oracle.Table1 WHERE Col1 IN (?) ). Basically,...
- 55
2
votes
2
answers
9k
views
Azure Data Factory Set Parameter with SQL Query
What is the alternative to SSIS' Execute SQL Task in ADF?
I've created a Pipeline parameter called ExtractDate (i know there isn't a date datatype option so I'm using a string datatype here) that I ...
- 507
0
votes
1
answer
379
views
Combining rows from multiple sources in Virtual list Filemaker
I am trying to make an Excel-like 'pivot table' in Filemaker using a Virtual List as the source of the data. The issue is I want to be able to have 'categories' down the first column that aren't fixed....
- 13
0
votes
0
answers
813
views
SSIS Error - Parsing Query from SQL Task Fails Due to 80040153, Invalid Value for Registry
Problem Statement
I have a lot of SQL Server environments locally.
When trying to parse a SQL task with very basic truncate table dbo.table syntax, I get the following error message.
I should note ...
0
votes
1
answer
966
views
How to map multiple row results to different variables in SSIS Execute SQL Task?
I need to map 1 column frmm multiple result rows of SQL query with different variables in the same SSIS package.
For example,
the output of my query is:
Category, Count
A, 16
B, 23
C, 41
D, ...
user11647819
2
votes
2
answers
880
views
SSIS Execute SQL Task - Input String in wrong format
I have an Execute SQL Task which tries to execute a stored procedure, like this:
EXEC usp_stored_proc ?, ?, ? OUTPUT, ? OUTPUT;
I have 4 variables mapped to parameters. Ignoring the output parameters,...
- 669
3
votes
1
answer
2k
views
Package part debugging is not supported when starting SSIS package
This very strange issue. When I press the start button to execute the SSIS project it shows "Package part debugging is not supported" and nothing else. I create a control flow with the data flow task ...
- 61
1
vote
1
answer
633
views
SSIS - How to use results of Execute SQL Task to call a Web Service
I need to use a result from SQL SELECT which should return an array of IDs to iterate through it in my foreach loop.
I'm quite new to SSIS. I created Execute SQL Task, connect to DB and write
SELECT ...
- 163
0
votes
0
answers
773
views
Overwrite of Excel destination using drop and create table statement in SQL task is not working properly. The new data is adding as new records
Actually I have designed the package to overwrite the excel destination with OLEDB data source. I have 5000 records in my SQL server DB table. First time when the SISS package executed same numbers of ...
0
votes
0
answers
76
views
SSIS Not Inserting More Than One Row
I'm Using Execute Sql Task To Update Time Dimension I'm Using This Code
declare @i int=isnull((select max(id) from DIM_DATE)+1,1)
declare @Date Date=isnull((select [date] from dim_date where ID=@i-1)...
2
votes
1
answer
2k
views
SSIS ForEach - Get current value of collection
I have a Variable User:AllBatches (ObJect) populated from an ADO.NET SQL Query. The SQL query returns a single column of Integers.
That part of my package is working.
The next step is a ForEach Loop ...
- 868
2
votes
1
answer
801
views
SSIS: Order by Column Full Result Set for Execute SQL Task
I have an Execute SQL Task placed before a Foreach Loop Container so that for every row returned by the Full Result Set a Script Tasks Inserts this into an Excel row.
However, on one particular ...
- 543
0
votes
0
answers
714
views
Connecting SSIS to excel via execute SQL task
I am encountering a frustrating problem with SSIS. The background is as follows:
I have an excel file with 5 sheets - the first sheet is named "Meta" (the remaining 4 sheets are not relevant to this ...
3
votes
1
answer
772
views
SSIS Execute SQL Task multi step with parameter mapping
I have a DTSX file that has an update statement that is selecting from itself in the update statement and it is filling up the temp DB. It is using parameter mapping for a variable in the update ...
- 281
3
votes
1
answer
5k
views
Execute SQL Task output parameter vs ResultSet
We have parameter direction as output in parameter binding and at the same time we do have result binding. So if we are having output variable or return type variable which will be available at output,...
3
votes
1
answer
809
views
Why is the value of a variable null inside a foreach loop container
I have a package that has a foreach file enumerator. Now I have a variable called EmailTo which is populated by an execute sql task outside the foreach container placed just before the loop container ...
- 634
0
votes
0
answers
455
views
SSIS Package Cant execute OPENROWSET Stored Procedure
Stored Procedure on SQL Server 2017 calls an OPENROWSET command to load a file from an .xlsx file.
When running the Stored Procedure on the server it works fine and will load the xlsx rows into data ...
1
vote
1
answer
1k
views
Problem with execute a procedure in SSIS with execute SQL task
I write in three variables values from a DataBase using an Execute SQL task and if I check if values was correctly written with the breakpoint, everything seems to be correct.
Now I want use the ...
- 169
2
votes
1
answer
395
views
I have two Foreach Loop,which hava same enumerated values.So how do I deal with it?
I declare two variables as "Object" named a and b,
and have a simple SQL task:.
select code from code_list
Using Full result set, I'm trying to pass the result to the variables declared(a and b),
...
- 21
2
votes
1
answer
364
views
Store result in Execute Sql task
I have written a stored procedure which will return the columns BEID, Date_of_txn, Txn_Amount as a result after the execution. I am getting the result manually when I run the stored procedure in SQL ...
- 207
3
votes
2
answers
6k
views
Read file in SSIS Project into a variable
My SSIS projects tend to run queries that require changes as they move between environments, like the table schema might change or a value in the Where clause. I've always either put my SQL into a ...
- 121
1
vote
2
answers
228
views
SSIS Precedence Constraint Not Working Due to Path Formatting
Using SSIS and MS-SQL Server 2012
I have a SQL Task executing:
SELECT COUNT(id) as id FROM PORG_Files WHERE filename = ?
It never returns anything except 0 because the SSIS filename looks like:
\\\\...
- 1,550
2
votes
2
answers
2k
views
Error with SQL query: The query failed to parse. Exception from HResult: 0x80040E14
I am using SSIS to create a data flow task to a postgresql server database.
I get the Error with SQL query:
The query failed to parse. Exception from HResult: 0x80040E14
See below screenshot:
...
- 51
3
votes
2
answers
260
views
Not able to recognize a table variable in ssis sp
I have created a variable in ssis called Vpop_summary_table.
I have used this variable in a sql statement.
my variable
Vpop_summary_table
is expected to give value
[dbo].[2019-02-02_pop_table]
...
-2
votes
1
answer
1k
views
Can we truncate data in a Excel sheet using Execute Sql task?
I have a SSIS package in which Data is loaded from Sql server to Excel sheet.
Now, whenever I run the package data got loaded with the previous loaded data. Requirement is to load only the fresh data ...
- 207
2
votes
3
answers
2k
views
SSIS 2017 "Execute SQL Task" fails Could not find stored procedure
I'am using SSIS with an ADO.NET connection to Azure. Within SSIS I want to execute a procedure with one input and 2 output parameters. The input parameter is a static value. The Procedure works with T-...
- 1,650
3
votes
1
answer
2k
views
SqlStatementSource expression vs Variable Source Type
I use SSIS in VS 2017 and SQL Server 2016.
I would like to know what is the difference between the following methods to for setting SQLStatement's value in Execute SQL Task:
First Way
Declare a ...
- 12.7k