Showing posts with label selects. Show all posts
Showing posts with label selects. Show all posts

Wednesday, March 7, 2012

How to Use Data Type GUID in UserVariable or foreach loop?

Hi Folks,

I am trying to build a Package that selects a list of Uniqueidentifiers by "Execute SQL-Task" and then loops through the ResultSet of the query using ADO.Net Enumerator to do something with this GUID Value, namely deleting all entrys with this PK in a different DataBase.

The main Problem I am facing is that you can′t select the type "GUID" for a user Variable in "Execute SQL-Task". All the Datatypes are there except for GUID!

This leads to the following error:

Error: 0xC001F009 at Package: The type of the value being assigned to variable "User::ImagicID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Is this a bug? Or am I doing something terribly wrong?

Best Regards and thanks in advance for your help

Chucker

Hi,

What is the variable initially defined as in the variables section? I believe it has to match that of the one that is being referenced in the SQL parameter mapping editor.

Is it possible for you to perform a CAST or CONVERT in the SQL statement to return the GUID ID as a string/varchar and do a string comparison when checking the GUID against the primary key?

Its just a though but i hope it helps.

Grant|||

For the Iterator (in the ForEach Loop), the only type that works (without converting) is "object".

When using this Type in the Delete Statement, I get the error that this is the wrong type.

So this may work if I only knew how to convert that "object" to GUID in a SQL Statement

I want to write something like

delete from table where pk = @.Uservariable

in the Execute SQL-Task within the ForEach Loop

then I get the error Message that @.Uservariable is not of type GUID

as I already mentioned in the Property Page of the ForEach Loop where

the colum pk can be mapped to a uservariable @.uservariable, the data

Type GUID does not exist.

Cheers

Martin

|||Did you try string? I had a similar issue, but it involved integers. According to books online this shouldn't be an issue, but stranger things have happen.

Friday, February 24, 2012

How to use a Wildcard with the value for the parameter?

I have a report that selects certain records based on what the user enters for the parameter but I'm trying to allow the user to enter a wildcard and show the relevant records. I used like and had the user add an * or even a % but it still returns nothing. Records are returned only when the user enters the full value with no wildcards. Help.

SELECT DISTINCT
PDFBM.ASSEMBLY_ID, PDFBM.COMPONENT_ID, PDFBM.REQUIRED_QTY, ICFPM.PART_DESC, ICFPM.DWG_REV, ICFPM.PENDING_ECN,
dbo.ICFSS.STORES_CODE, dbo.ICFSS.STOCK_LOCATION
FROM dbo.ICFPM ICFPM INNER JOIN
dbo.PDFBM PDFBM ON ICFPM.PART_ID = PDFBM.ASSEMBLY_ID INNER JOIN
dbo.ICFSS ON ICFPM.PART_ID = dbo.ICFSS.PART_ID
WHERE (PDFBM.COMPONENT_ID LIKE @.componentid) AND (PDFBM.ASSEMBLY_ID LIKE '350%')
ORDER BY PDFBM.ASSEMBLY_ID, PDFBM.COMPONENT_ID

A

I'm not sure but try converting the query to a dynamic query:

="SELECT DISTINCT
PDFBM.ASSEMBLY_ID, PDFBM.COMPONENT_ID, PDFBM.REQUIRED_QTY, ICFPM.PART_DESC, ICFPM.DWG_REV, ICFPM.PENDING_ECN,
dbo.ICFSS.STORES_CODE, dbo.ICFSS.STOCK_LOCATION
FROM dbo.ICFPM ICFPM INNER JOIN
dbo.PDFBM PDFBM ON ICFPM.PART_ID = PDFBM.ASSEMBLY_ID INNER JOIN
dbo.ICFSS ON ICFPM.PART_ID = dbo.ICFSS.PART_ID
WHERE (PDFBM.COMPONENT_ID LIKE '" + Parameters!componentid.Value + "') AND (PDFBM.ASSEMBLY_ID LIKE '350%')
ORDER BY PDFBM.ASSEMBLY_ID, PDFBM.COMPONENT_ID"

Regards,

Telmo Moreira

|||

try:

LIKE @.parameter + '%')

drop the user adding the wildcard char