February 14, 2012 at 9:26 pm
Hi,
How to read the value from text file and pass the value to another text file in SSIS using variable
I know that we can do by SQL Task and Script task or SQL Task but i couldn't.
I am missing some steps.Can anyone guide me in this.
Thanks
February 14, 2012 at 11:44 pm
Can you elaborate a bit more on what you are trying to do?
Sample data and desired output would be appreciated.
Reading/writing of a flat file is done using a Flat File Connection Manager and a Flat File Source/Destination.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 15, 2012 at 12:02 am
The thing is like this
I am having a text file which is having around 100 records with 2 columns
say Empid, mgrid
1 , 5
10 , 25
15 , 52
25 , 23
30 , 56
......
I need the read above text file value one by one and i need to pass it to Stored procedure say exec mysp @parm1,@param2 .
The task which was assigned to me is do it with Script task to read the text file.(i am not sure about it the task assigner has given me some conditions/suggestions).
Thanks
February 15, 2012 at 1:04 am
Read the text file using a Flat File source and pass the two columns as parameters in an OLE DB Command component.
The OLE DB Command will have a SQL command that resembles this:
EXEC mysp ?, ?
Map each OLE DB parameter (the question mark) to the corresponding column.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 15, 2012 at 2:13 am
Hi,
But how to pass each and every value one by one basis.
February 15, 2012 at 2:16 am
Koen Verbeeck (2/15/2012)
Read the text file using a Flat File source and pass the two columns as parameters in an OLE DB Command component.The OLE DB Command will have a SQL command that resembles this:
EXEC mysp ?, ?Map each OLE DB parameter (the question mark) to the corresponding column.
I am getting the below error when i try to connect from flat file to OLEDB source.
TITLE: Microsoft Visual Studio
------------------------------
Cannot create connector.
The destination component does not have any available inputs for use in creating a path.
------------------------------
BUTTONS:
OK
------------------------------
February 15, 2012 at 2:44 am
Try an OLE DB Destination instead of an OLE DB Source.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 15, 2012 at 3:06 am
Hi Have you tried before i couldn't able to connect it. :w00t:
can anyone guide me on step by step basis.
Thanks
February 15, 2012 at 3:19 am
yuvipoy (2/15/2012)
Hi Have you tried before i couldn't able to connect it. :w00t:can anyone guide me on step by step basis.
Thanks
You don't need an OLE DB Destination, but an OLE DB Command.
This article explains a bit how to set it up with a stored procedure:
Output Parameter of Stored Procedure In OLE DB Command - SSIS
Ignore the output parameter part, you only need input parameters.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 15, 2012 at 10:09 pm
You don't need an OLE DB Destination, but an OLE DB Command.
This article explains a bit how to set it up with a stored procedure:
Output Parameter of Stored Procedure In OLE DB Command - SSIS
Ignore the output parameter part, you only need input parameters.
Hi I tried it but i am unable to get the result.
Guys anyone help me on this 🙂
February 15, 2012 at 10:31 pm
If i pass 1,2,3,4,5 from text file to OLE DB Command and result of the same to another text file it is returning same 1,2,3,4,5 it not executing the SP
here my SP has only one input
Exec mysp ?
Exec mysp 1
Exec mysp 2
.
.
.
& so on but it not executing the Sp,the same value is returned to text file and not the sp output sp has five columns as output.
in the destination i have chosen flat file as the output to write. i couldn't find five columns as output only one column as the output.Why?
February 15, 2012 at 11:48 pm
The OLE DB Command just fires the stored procedure against the database.
If you do not do anything with it, the data in the dataflow stays the same. Hence the one column output. The article I linked to explains how to set-up output parameters. Implement 5 output parameters in your sp and link them to your dataflow.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 15, 2012 at 11:57 pm
Hi
My thing is ,i am using adventure work DB
exec [uspGetEmployeeManagers] @EmployeeID=56
The output is
RecursionLevel EmployeeID FirstName LastName ManagerID ManagerFirstName ManagerLastName
i need to write to the text file,as u mention i did not have any output param in the stored procedure.
56,23,156,12..... and so on is the input i am having in the text file.
February 16, 2012 at 12:06 am
OK, that won't work in a dataflow with an OLE DB Command, as the sp can probably return more than one line. Next time be a little more specific in what you are trying to accomplish.
In this case, the script task is indeed a good solution.
Read the text file (there's plenty of code on the net on how to read a text file using .NET) and store the results in a dataset. Loop over this dataset and for each iteration call the stored procedure and capture the results in another dataset. (again, enough code samples on the net for that). Write the those results to another text file.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 16, 2012 at 2:30 am
Koen Verbeeck (2/16/2012)
OK, that won't work in a dataflow with an OLE DB Command, as the sp can probably return more than one line. Next time be a little more specific in what you are trying to accomplish.In this case, the script task is indeed a good solution.
Read the text file (there's plenty of code on the net on how to read a text file using .NET) and store the results in a dataset. Loop over this dataset and for each iteration call the stored procedure and capture the results in another dataset. (again, enough code samples on the net for that). Write the those results to another text file.
Hi,
I have read the text file
Dim streamReader As New IO.StreamReader("C:\Documents and Settings\uspGetEmployeeManagers.txt")
' Read the StreamReader To End and assign to local variable
Dim StreamText As String = streamReader.ReadToEnd()
' assign SSIS variable with value of StreamText local variable.
Me.Dts.Variables("EmployeeID").Value = StreamText
is this a correct code?
can you help me on this since i am at the neck of the moment i have taken 2days for this work,i need to give this by EOD.
i have read the text file and couldn't able to pass it to SP and that's too passing each and every value
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply