tag:blogger.com,1999:blog-29438013.post6861307232272117885..comments2023-07-01T07:07:08.839-04:00Comments on Rafael Salas: SSIS: Mapping parameter inside of a Execute SQL Task, OLE DB Source Component or Datareader componentRafa Salashttp://www.blogger.com/profile/16375071697865176825noreply@blogger.comBlogger42125tag:blogger.com,1999:blog-29438013.post-72458543394540763522015-05-05T18:35:26.461-04:002015-05-05T18:35:26.461-04:00Thanks. EXACTLY the instructions I needed.Thanks. EXACTLY the instructions I needed.RCLhttps://www.blogger.com/profile/00333290647747858016noreply@blogger.comtag:blogger.com,1999:blog-29438013.post-17055329302211173112014-06-12T20:23:46.928-04:002014-06-12T20:23:46.928-04:00This comment has been removed by the author.Anonymoushttps://www.blogger.com/profile/09775950019057154443noreply@blogger.comtag:blogger.com,1999:blog-29438013.post-33928334834362386372014-06-12T20:21:53.836-04:002014-06-12T20:21:53.836-04:00This comment has been removed by the author.Anonymoushttps://www.blogger.com/profile/09775950019057154443noreply@blogger.comtag:blogger.com,1999:blog-29438013.post-42016370633807270562013-04-10T12:03:32.034-04:002013-04-10T12:03:32.034-04:00hola rafael prodrias ayudarme en un DTS profa mi S...hola rafael prodrias ayudarme en un DTS profa mi Skype es Chuy.Master<br />Bueno soy estremadamente nuevo en esto del itegration services jejeje<br />te comento un poco lo que quiero hacer<br />tengo 2 data flow task y un excecute Execute SQL Task.<br />data flow task1 me sube un excel por medio de un EXCEL DB SOURCE y convierte los datos con un dataconverter ESTO LO QUIERO GUARDAR EN UNA VARIABLE1 <br />data flow task2 es una consulta en mi base de datos para traer la clave del excel por medio de un OLE DB SOURCE ESTE LO QUIERO GUARDAR EN OTRA VARIABLE2<br />en mi Execute SQL Task quiero hacer la insercion en una tabla (ImportarClientesTemporales) con los registros tomados de la VARIABLE1 del data flow task1 y VARIABLE2 del data flow task2<br />Anonymoushttps://www.blogger.com/profile/00645466921864386420noreply@blogger.comtag:blogger.com,1999:blog-29438013.post-78332076665034882842012-03-04T17:55:56.415-05:002012-03-04T17:55:56.415-05:00Thank you, Rafael. A great article; it saved me fr...Thank you, Rafael. A great article; it saved me from continued frustration.Fredhttps://www.blogger.com/profile/07986485226578467160noreply@blogger.comtag:blogger.com,1999:blog-29438013.post-29031324874612940592011-10-17T22:00:00.673-04:002011-10-17T22:00:00.673-04:00This article is a real life saver!!! I really appr...This article is a real life saver!!! I really appreciate your work...<br /><br />Thanks a ton!<br />VinayAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-29438013.post-72355731199559548032011-10-17T16:44:46.029-04:002011-10-17T16:44:46.029-04:00"cutebaby said...
Nice but it does not work ..."cutebaby said... <br />Nice but it does not work if your parameter is DATE type. Got the following error: "The data types "DT_WSTR" and "DT_DATE" are incompatible for binary operator "+". The operand <br />types could not be implicitly cast into compatible types for the operation" <br />How to modify this string? Thanks. "<br /><br />I had a similar problem. What I did to get around it was to bring in my date parameter as a string and then do a cast.<br /><br />"select * from MyTable with (nolock) where (LastModifiedDate >= CAST('" + @[User::incoming_date_parameter] + "' AS DATETIME));"<br /><br />My incoming_date_parameter variable would have the date in the YYYYMMDD format. i.e. 20111001 for October 1, 2011.<br /><br />I don't know if this is the best way to go but it worked for me.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-29438013.post-10839703971916610882011-04-18T22:53:00.880-04:002011-04-18T22:53:00.880-04:00This is a nice article..
Its easy to understand .....This is a nice article..<br />Its easy to understand ..<br />And this article is using to learn something about it..<br /><br /><a href="http://www.java.pakcarid.com/Cpp.aspx?sub=2760&ff=12217&topid=152&sls=30" rel="nofollow">c#, dot.net, php tutorial, Ms sql server</a><br /><br />Thanks a lot..!<br />ri80Muhammad Azeemhttps://www.blogger.com/profile/12974854965953781617noreply@blogger.comtag:blogger.com,1999:blog-29438013.post-90243823495186644422011-03-18T13:40:19.509-04:002011-03-18T13:40:19.509-04:00im getting string cant glue date params to it.
wo...im getting string cant glue date params to it.<br /><br />would be good for simple ID's and strings?sattanoreply@blogger.comtag:blogger.com,1999:blog-29438013.post-6332988292490247222011-01-07T06:10:15.377-05:002011-01-07T06:10:15.377-05:00Can't even begin to explain how grateful I am ...Can't even begin to explain how grateful I am for this solution! I spent hours trying to get variables to pass to a stored procedure in an SQL Task yesterday and could not get it to work - but 10 minutes spent trying your solution and it's done!<br /><br />I've added a link to this post on my blog:<br />http://atominnovation.blogspot.com/2011/01/ssis-mapping-parameter-inside-of.html<br /><br />Thanks again RafaelPhil Reidhttps://www.blogger.com/profile/16329226949908289863noreply@blogger.comtag:blogger.com,1999:blog-29438013.post-20359830282958854692010-11-10T21:53:21.853-05:002010-11-10T21:53:21.853-05:00Awesome Awesome Awesome!!!
Thanks heaps for the po...Awesome Awesome Awesome!!!<br />Thanks heaps for the post!<br /><br />In summary I had prepared a query with CTE due to receursion. The resultset was required to be populted in an excel file and my problem was/were- <br />1) OLE_SRC would not let me pass params to the query since it has CTE in it<br />2) I couldn't plonk the query in a proc since that will not initialise the metadata for OLE_SRC<br />3) I couldn't plonk the query in a table valued UDF since it had a CTE in it<br />etc.. etc.. <br />but then your solution worked beautifully!<br /><br />now that its all over I wonder .. what a loose integration of CTEs MS did in SQL Server 2005?!?<br />thanks<br />GauravGaurav Saininoreply@blogger.comtag:blogger.com,1999:blog-29438013.post-63216645315718982202010-10-12T17:10:23.546-04:002010-10-12T17:10:23.546-04:00Rafael,
Thanks for the post. I was able to create...Rafael,<br /><br />Thanks for the post. I was able to create @[user::Myquery] expression and evaluate it and set [DataReader Source].[SqlCommand] property to @[user::Myquery] in data flow. But my DataReader Source says Error at Data Flow Task [DataReader Source[77]]:The sql command has not been set correctly. What step am I missing?<br /><br />Thanks,<br />SayliUnknownhttps://www.blogger.com/profile/15359899856566369607noreply@blogger.comtag:blogger.com,1999:blog-29438013.post-40857625833293850742010-06-15T09:00:54.903-04:002010-06-15T09:00:54.903-04:00Cookie,
This may not be an issue with your express...Cookie,<br />This may not be an issue with your expression. I haven't tried it, but I don't think excel supports 'DROP' statements. I know the SSIS msdn forum has several discussion around deleting rows and dropping sheets in excel files. I would suggest to go there and search and/or post your own questionRafa Salashttps://www.blogger.com/profile/16375071697865176825noreply@blogger.comtag:blogger.com,1999:blog-29438013.post-438115296703143862010-06-14T16:29:56.324-04:002010-06-14T16:29:56.324-04:00Please help!
My EST, Drop TABLE '`Table Excel...Please help!<br /><br />My EST, Drop TABLE '`Table Excel worksheet name` query throws an error:<br />`Table Excel worksheet name` does not exist.<br /><br />What's wrong?Unknownhttps://www.blogger.com/profile/04127105048526867584noreply@blogger.comtag:blogger.com,1999:blog-29438013.post-77799418920855796242010-06-03T06:06:02.102-04:002010-06-03T06:06:02.102-04:00Greate thanks. How simple and usefulGreate thanks. How simple and usefulUnknownhttps://www.blogger.com/profile/16820598803770849720noreply@blogger.comtag:blogger.com,1999:blog-29438013.post-75290398859594050262010-04-16T12:24:18.422-04:002010-04-16T12:24:18.422-04:00Hello Rapheal,
I created an SQL task containing a...Hello Rapheal,<br /><br />I created an SQL task containing a script that will delete data based on value in a column. The script will go through specific tables and delete records based on the selected value. I am wanting to create a configuration file that other users may use, and enter a different value based on instructions. Question: How would I alter my script in order to use the variable? Many thanksquitoshttps://www.blogger.com/profile/16604277944412941356noreply@blogger.comtag:blogger.com,1999:blog-29438013.post-71646905768118650062010-03-18T16:54:58.434-04:002010-03-18T16:54:58.434-04:00Hi Rafael,
I am creating an SSIS package where we...Hi Rafael,<br /><br />I am creating an SSIS package where we have same tablename with different schema. So I created a variable as object and passing it in a foreach loop container and then adding oledb source and choosing the option " tablename or viewname as variable" and then when i select the variable it doesn't display. Can you tell me if i am missing something here?Unknownhttps://www.blogger.com/profile/17072134809062533999noreply@blogger.comtag:blogger.com,1999:blog-29438013.post-5163884341840441162010-03-18T11:40:51.265-04:002010-03-18T11:40:51.265-04:00very useful specially with DB2 databases! thanks!very useful specially with DB2 databases! thanks!Unknownhttps://www.blogger.com/profile/12025085652972810874noreply@blogger.comtag:blogger.com,1999:blog-29438013.post-15445888744964988942010-03-03T22:53:38.026-05:002010-03-03T22:53:38.026-05:00Wilfredo,
Tendrias que connectar el execute sql ta...Wilfredo,<br />Tendrias que connectar el execute sql task con el ForEachloop. Dentro del for each loop tendrias que usar algunas variables para almacenar los IDs. Mira este otra entrada en mi blog a ver si te ayuda: http://rafael-salas.blogspot.com/2010/01/ssis-loop-through-multiple-database.htmlRafa Salashttps://www.blogger.com/profile/16375071697865176825noreply@blogger.comtag:blogger.com,1999:blog-29438013.post-39593338137151781062010-03-03T11:24:31.619-05:002010-03-03T11:24:31.619-05:00Hola Rafael,
Por tu nombre creo que hablas españo...Hola Rafael, <br />Por tu nombre creo que hablas español.<br />Necesito ayuda en lo siguiente, <br />Tengo que consultar la información de clientes utilizando un SP Oracle.<br />Utilizando SSIS pienso que agregando un Execute SQL Task y obtengo los ID tipos y los ID a traves de un query, y con un Foreach Container consulto todos los resultados desplegados en el query.<br />Como hago para que el Foreach reconozca cada TipoID y ID del query y me ejecute el SP para cada uno.<br />De antemano gracias por tu colaboracion.<br />SaludosWilfredo Sanchezhttps://www.blogger.com/profile/06215422227059344332noreply@blogger.comtag:blogger.com,1999:blog-29438013.post-15018333616251635762010-01-22T08:55:08.260-05:002010-01-22T08:55:08.260-05:00thanks a lot! saved me from headachethanks a lot! saved me from headacheBoris Tyukinhttps://www.blogger.com/profile/15286991293900821710noreply@blogger.comtag:blogger.com,1999:blog-29438013.post-84303463877730129322009-07-13T17:32:18.452-04:002009-07-13T17:32:18.452-04:00Nice but it does not work if your parameter is DAT...Nice but it does not work if your parameter is DATE type. Got the following error: "The data types "DT_WSTR" and "DT_DATE" are incompatible for binary operator "+". The operand <br />types could not be implicitly cast into compatible types for the operation" <br />How to modify this string? Thanks.Unknownhttps://www.blogger.com/profile/04889744324582824454noreply@blogger.comtag:blogger.com,1999:blog-29438013.post-91784949094207077052009-07-01T06:08:35.036-04:002009-07-01T06:08:35.036-04:00Error: The wrapper was unable to set the value of ...Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property. Why ?Oleghttps://www.blogger.com/profile/12955469184736681689noreply@blogger.comtag:blogger.com,1999:blog-29438013.post-1816297626872483352009-06-27T22:52:25.072-04:002009-06-27T22:52:25.072-04:00Remember that you can always click the 'evalua...Remember that you can always click the 'evaluate expression to get the sql statement the expression produces. You can copy and paste it in the native tool (SSMS, TOAD, SQL PLUS) to test the queryRafa Salashttps://www.blogger.com/profile/16375071697865176825noreply@blogger.comtag:blogger.com,1999:blog-29438013.post-59397687072418933582009-06-24T22:12:47.072-04:002009-06-24T22:12:47.072-04:00Hi Rafael,
How would you specify a variable as an...Hi Rafael,<br /><br />How would you specify a variable as an OUTPUT in an expression?<br /><br />I'm trying to call my Oracle procedure aml_position_pkg.get_client_position, which has 1 input and has 28 outputs.<br /><br />My expression value is:<br />"BEGIN aml_position_pkg.get_client_position(" + @[User::CurrentClient] + ", " + @[User::ProductType] + ", " + @[User::CreditLimit] + ", " + @[User::FixedLoanBalance] + ", " + @[User::VariableLoanBalance] + ", " + @[User::TotalLoanBalance] + ", " + @[User::UnsettledAmount] + ", " + @[User::PledgeBalance] + ", " + @[User::PledgeSecurityValue] + ", " + @[User::TotalLiability] + ", " + @[User::PortfolioMarketValue] + ", " + @[User::PortfolioMarketSecurityValue] + ", " + @[User::PortfolioSecurityValue] + ", " + @[User::CashBalance] + ", " + @[User::CashSecurityValue] + ", " + @[User::TotalSecurityValue] + ", " + @[User::SecuritySurplus] + ", " + @[User::TotalBufferValue] + ", " + @[User::FundsAvailable] + ", " + @[User::LimitExceededFlag] + ", " + @[User::CreditLimitFlag] + ", " + @[User::AmountOverCreditLimit] + ", " + @[User::MarginCallFlag] + ", " + @[User::AmountInMarginCall] + ", " + @[User::BufferFlag] + ", " + @[User::AmountInBuffer] + ", " + @[User::PercentageInBuffer] + ", " + @[User::VariableLoanNumber] + ", " + @[User::VariableInterestRate] + "); END;"<br /><br />The error indicates that my variables, which are empty Strings, are being treated as INPUTs when I want them to be treated as OUTPUTs:<br />Error: 0xC002F210 at Get Client Position, Execute SQL Task: Executing the query "BEGIN aml_position_pkg.get_client_position(1162243, , , , , , , , , , , , , , , , , , , , , , , , , , , , ); END;" failed with the following error: "ORA-06550: line 1, column 53:<br />PLS-00103: Encountered the symbol "," ...<br /><br />Please help!Anonymousnoreply@blogger.com