Welcome to EMC Consulting Blogs

Jamie,

I have found when using Variables in expressions, the one of the tricks is to set a default value for the variable before building the expression. Since the expression tries to evaluate immediately, not having a "starting" value for the variable can cause it to error on the initial evaluation.

Keep up the good work!
Scott Barrett

Hi Jamie,

I've read on the SQL Server Business Intelligence Development Studio Help that the DT_WSTR data type has a maximum lenght of 4000. As the variable "SourceSQL" type is String, it has only 4000 as max lenght. What can I do if my sql statement has more than 4000 characters? Does it work if I just use that data type? Do you have any other solution?

Thanks!

I couldn't get the parameterised SQL statement option to work with a DB2 OLE DB compliant data source. So I used this option, and although it works, it's unbelievably arduous to set up with a statement of any length....I had a select statement with about 1000 chars, and after taking out all carriage returns(wouldn't take it with them) so that I could paste it into the variable expression box I finally had it working.

Adrian

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....

Jamie, first, thanks for having this out here, its a great resource. I'm an old school DTS developer trying to match the old to the new and this is a great help.

Ok, so you touch on something here I need more info about. Lets say I have a monthly fact table I need to create on the fly each month based on a package var for month id, example: fact_myfact_m72. I have the DDL for this table creation and the variable but I'm not sure how I could A) get the table created dynamically and B) get the ole db connector to accept this table (which will be the same layout month to month but a different name) sight unseen.

In earlier DTS, I would have two ways to do this, but typically I would use activex to seed a template string with the global variable for the month, then send that string of template code to a sql step, and then assign the datapumps destination table property to this new table name.

Doing this in SSIS will be a huge hurdle cleared for me, any thoughts?

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....

This seems to work when only one variable needs to passed if I have to pass two variables, it is not getting updated. I tried using variable as "Exec storedproc " + @[Variable1] + ", " + @[Variable2]

Do you have a work around for this?

Hi, I am Raghu Raman and I am new to this blog. I am stuck here in a situation where I have a package that has a task of copying data from server A.DBA to serverB.dbB. When I execute the package in design mode, it works great. When I create an Agent pointing to the package, It fails with the err

Date 5/1/2006 9:18:51 AM
Log Job History (Daily Routine)

Step ID 1
Server BHFINANCE02
Job Name Daily Routine
Step Name PackageExec
Duration 00:00:20
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: CORP\SQLservice. The package execution failed.  The step failed.

I totally could not understand this.

I am not sure if someone already asked this question. Please help

Thanks
Raghu Raman

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices.

Hey there, i try to get it working but i have some problem.

when i use a script-component  to show the variable in a msgbox it looks okm however when i use the variable in the ole-db-source component it "crashes". I don't know how to debug this...

I am maybe missunderstanding how the OLE DB Command works, but I have been trying to use it to run a stored procedure to update records in a temporary table, so once they get moved to production table they have the right data, but this seems don't have effect at all.

Is this possible to do?

-SmokedIronMade

Jamie,

Thanks it worked for me.

-SmokedIronMade

Hi, i m new to SSIS. I have a question. I really appreciate if any one helps me.

Actually i need to modify OLEDBCommand's SQLStatement property inside a scriptComponent . How can i reference other Components inside ScriptComponent to modify their properties at runtime.

oh REALLY!!...

anywayz thanks Jamie for the answer .

I’m wanting to compile a basic best practices list for SSIS. I don’t really need to get into

Hi Jamie,

Are there some guidelines anywhere on when to use and when not to use dynamic ad-hoc embedded sql selects within an Ole Db Source component in Ssis, vice when to execute a Sql Server database engine stored procedure for selecting data back into the Ssis package?

Thanks.  Sincerely, -Dotnet Fellow

Dear Jamie,

I tried to use your method to get data from an Oracle DB to populate in a SQL Server DB 2005  but get this error:

[OLE DB Source [247]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available.  Source: "OraOLEDB"  Hresult: 0x80040E14  Description: "ORA-00936: missing expression".

Query is: "SELECT DISTINCT StuID FROM Students WHERE SEM =" + @[vSem]

The connection uses Oracle Provider for OLE DB

vSem is the variable.

I also tried MS OLE DB Provider for Oracle but get the same error.

Thank you for your helps,

d-cpt

when I checked the variable value, it shows

SELECT DISTINCT StuID FROM Students WHERE SEM = SELECT DISTINCT StuID FROM Students WHERE SEM ='123'

vSem has default value as '123'

I don't know why it replaces the default value with a whole query.

May you give me some hints.

Regards,

d-cpt

Thank you, Jamie.

d-cpt

I wanted to ask that how should i go ahead if i want to put a filter for month_oa_date entry in my table?

I have tried the first option given by Jamie of dynamic SQL....but i guess its not working for me...please help me out....

it is simply great :)

Please tell me what can I do for it?

I want to create a query containing a DateTime variable, but then the expression type fails. It works for string variables. What kind of functions (for type conversion) can you call in an Expression?

Is there any equivalent of the TSQL CHAR(n) function, or {CR}{LF}, that could be used in a transform expression? I may be missing the point, however, I have reached a dead end.

Thanks

Hello guys, i want to copy data from different tables so, the my source oledb query would be select * from tabl1, select * from table2. Both the tables1, table2 have different columns. And my destination is flat file. I tried constructing a variable for the sql string and dynamically creating fiat files. But it doesnt work. How can i do this ?

Any help appreciated.

Thanks

It was quite useful lesson for me using which I could solve many problems.

Thanks a lot.

Bibhuti Thakuria,Boston,USA

I am new to SSIS. My scenario demands me to do an incremental load. So i have followed your article and have created a one variable 'vDate' (Dataflow Task, Datetime, 3/3/2008) and another variable SourceSQL (Dataflow Task, String). I have set this SourceSQL evaluation to True and have added this;

"select * from stagingperfdata where startdatetime = "+ (DT_WSTR,10) @[vDate].

I want to call this within a OLE DB source, so i select the data access method to SQL command from variable. My variable name is visible and i am able to select it and then save and debug. I also have a derived column transformation to increment the variable vDate by one day.

The flow is successful but there is no records written to the target.

I tried the same query (available in the variable's value column) in SSMS and it is retrieving 12 records.

I went back to the Ole Db source and wanted to preview the data, but i get "Query timeout expired (Microsoft SQL Native Client)" error.

Am i missing something.

The expression for my variable 'SourceSQL' does NOT evaluate to my need. The initial value  (3/3/2008) i passed to my other variable (vDate) is visible. I have pasted below that query.

select * from stagingdumpdata where startdatetime = 3/3/2008

It should have evaluated to

select * from stagingdumpfdata where startdatetime = '3/3/2008'

Am i missing something here.

Thanks. Robin

Played around with the Expression editor and got my package to work when my query was like this.

"select * from stagingperfdata where startdatetime =" + " ' " +  (DT_wSTR,10) @[vDate]  + " ' "

Thanks Jamie.

Any thoughts on assigning the incremental date (which i get from the derived colum transformation) to the variable. Do we have something like "SETMAXVARIABLE" function of Informatica.

Thanks again

Anybody else think that it's ridiculous to go through so much effort for a dynamic query?  I mean, I had to set my query variable to a dummy query with the dynamic values set statically just to get the metadata to map, then use a VB.NET script task to create the query based upon dynamic variables.

I mean, it's just obsurd to me that clicking the "parameters" button doesn't parse my more complicated query.  Took me 4 hours to do a package that should've taken 15 minutes.  Just had to vent.   -Justin

I wanted to adjust a dynamic process, which imports data from excel into SQL, by only retrieving Non-Null values. Your example gave me what I was looking for. I had to make one adjustment to get the result I wanted.

I ended up placing brackets just inside the quotes:

Ie: "SELECT * FROM ["  +@[User::anExcelSpreadsheet] +"]  WHERE [a specific column name] IS NOT NULL

and I get the non-null values imported into SQL.

thanks Jamie!

-- Isondart

I'm kind of new to SSIS - this info was a tremendous help!  

thanks!

Hi ,

I am totally new to SSIS jus 2 weeks old.

I have build a task to get records from table where the table name is like XXX_Email.

and XXX is some prefix  which is there is some other table so i jus added one more column in my prefix table  to get table name records as prefix+_Email.

so i need to pass these table names to my query variable @[User::TableName]

can anyone help how to pass this value to my tablename variable .

PingBack from http://www.keyongtech.com/2091868-syntax

Hi,

I have created one variable called Effectivedate then i set Evaluate Expression as False and assigned default value '20/03/2009' type as string

then i have created another variable Preparesql and i have set evaluate expression as True then assigned the following query to that expression

SELECT POSITION.SECURITY_ALIAS,POSITION.DEP_ACQ_DATE,POSITION1.ORIG_FACE FROM (SELECT LLP.SECURITY_ALIAS AS SECURITY_ALIAS ,CONVERT(VARCHAR(8),LLP.department_acquisition_date,112)

AS DEP_ACQ_DATE FROM HOLDING.DBO.POSITION P, HOLDING.DBO.POSITION_DETAIL PD, HOLDING.DBO.LOT_LEVEL_POSITION LLP

WHERE P.POSITION_ID = PD.POSITION_ID AND P.POSITION_ID = LLP.POSITION_ID AND PD.security_alias = LLP.security_alias

AND P.SRC_INTFC_INST =(SELECT INSTANCE FROM PACE_MASTER.DBO.INTERFACES WHERE SHORT_DESC='DD') AND

CONVERT(VARCHAR(8),P.EFFECTIVE_DATE,112) =("+ @[User::Effectivedate] + ") ) POSITION LEFT JOIN ( SELECT LLP.SECURITY_ALIAS AS SECURITY_ALIAS, LLP.ORIG_FACE FROM HOLDING.DBO.POSITION P, HOLDING.DBO.POSITION_DETAIL PD, HOLDING.DBO.LOT_LEVEL_POSITION LLP WHERE P.POSITION_ID = PD.POSITION_ID AND P.POSITION_ID = LLP.POSITION_ID AND PD.security_alias = LLP.security_alias AND P.SRC_INTFC_INST =(SELECT INSTANCE FROM PACE_MASTER.DBO.INTERFACES WHERE SHORT_DESC='BLACKROCK')AND CONVERT(VARCHAR(8),P.EFFECTIVE_DATE,112) =("+ @[User::Effectivedate] + "))POSITION1 ON POSITION1.SECURITY_ALIAS = POSITION.SECURITY_ALIAS

then i created the OLEBE command and used sql command from variable option.

Data flow task is executing successfully but it is not pulling any record.

can someone guide what could be the problem?

Regards

Senthil