May 11, 2007 at 1:52 pm
I know how to create variables and use them in a for loop.
What I need to be able to do is to use an Execute SQL Task to assign a value to a package variable. Either using a query or a stored procedure. I can do it in a DTS package but haven't been able to get it working in an SSIS package. Do any of you know where a good example is or where step by step instructions are? So far everything I have found in the SQL Server tutorials and at MSDN have not been helpful.
I also need to be able to use the value stored by the above mentioned process in a data flow task in an OLE DB source.
Again do any of you know where a good example is or where step by step instructions are?
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
May 11, 2007 at 3:06 pm
May 14, 2007 at 6:40 am
I tried clicking on that link and the page doesn't load for me.
Here is the query I put in the first SQL Task that I mentioned in my first post. I am simply trying to capture todays date:
SELECT getdate() AS Today
Then I click on the Parameter Mapping and enter the following:
Variable Name: User::gvDateTime (This one only allows you to
choose from a drop down box so
the variable to use is only
available if created before I
try to map to it)
Direction: Output
Data Type: DBDATE
Parameter Name: Today
The parameter name is where I get the error and so I am not sure if I have done it right.
The error I get when I execute this task reads:
Error: 0xC002F210 at variable parameter test, Execute SQL Task: Executing the query "SELECT getdate() AS Today" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
For the SQL Task that is supposed to use the value in a package variable I have the following query:
SELECT MyID AS Test INTO tblTest
FROM dbMine.dbo.tblMe
WHERE MyID = ?
In the Parameter Mapping I have the following:
Variable Name: User::gvMyID
Direction: Input
Data Type: LONG
Parameter Name: NewParameterName
When I execute this task I get the following error:
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "SELECT MyID AS Test INTO tblTest
FROM dbMine.dbo.tblMe
WHERE MyID = ?" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
In both examples if I open the SQL Task and select Result Set everything there is grayed out and so I can do nothing to configure anything there.
Thanks in advance to all who read this. I hope someone has seen this before and can help me fix it.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
May 14, 2007 at 7:38 am
I removed everything from the Parameter Mapping.
Under General I switched the ResultSet setting from None to Single Row.
That made me feel dumb when that was all I needed so that I could add stuff in the Result Set area.
Anyway, this now looks similar to how I would do it in DTS. So I click the add button in the Result Set area get the data type of the variable syncronized with the type of data I am trying to put into the package variable and the task finally executes with no errors.
Unfortunately, it doesn't seem to be storing the value I want it to store. It seemed to clear it once but now it doesn't change what I enter in there manually.
I feel I am getting closer but haven't quite got it yet. I also found this link http://msdn2.microsoft.com/en-us/library/ms141689.aspx and did what it said here but still it doesn't change the variable's value.
Any ideas or suggestions on what I am doing wrong? As I often do I keep looking for the solution and check back here periodically to see if someone replies.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
May 14, 2007 at 11:07 am
Well, I am now able to use Package variables in an Execute SQL Task. I learned that I don't need to set the resultset to use values from a package variable. I simply add an entry in the parameter mapping for each variable I want to use and for the Parameter Name I use a number to indicate which question mark the parameter is for. 0 is for the 1st parameter and 1 is for the 2nd and so on.
The thing that really gets me is I don't see why I can't change the value of a package variable. I even created a script task and set the value of a package variable and then used MsgBox to show me the value and the pop up box shows the value I changed it to, but when I switch back to design mode I find that it didn't keep the change.
I wanted to implement using the value in a package variable in a data flow OLE DB source but it does parameters differently than the execute SQL Task.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
May 22, 2007 at 7:17 am
Robert, you can probably also set the variables by using a Named Parameter (this probably requires an ADO.NET Connection). You query would need to include that parameter: "Select @ID = ID From MyTable Where...".
Also, remember that you will NEVER see a Package Variable change it's value. If you can verify that the variable IS changing by using a Script to MessageBox it, then feel safe and secure that everything is working well. Don't worry about what you see in the designer.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply