SSIS Design in 32 bit did not work in 64 bit
I have design a few SSIS to export the data into Excel & Text file. The SSIS to export data to Excel was using Microsoft Jet 4.0 OLE DB Provider which was not support in 64 bit base on the result searching from internet. May I get all the professional developer to help, what should i do for this SSIS?Judy
August 12th, 2010 12:54pm

Hi, You need to execute SSIS using 32-bit runtime. To do this in BIDS you can set Run64BitRuntime for false. You can find this at Project Properties -> Debugging If you are execting this from SQL agent or DTEXEC you need change way you are currently execute. For detail kindly visit this link. http://toddmcdermid.blogspot.com/2009/10/quick-reference-ssis-in-32-and-64-bits.html Hope this helps. -Chintak
Free Windows Admin Tool Kit Click here and download it now
August 12th, 2010 1:19pm

I faced this issue yesterday and all i needed to do was set the properties of the solution to RUN6rBitRunTime to FALSE. Solution->Properties->Debugging->RUN64BitRunTime =FALSE For excel we do not have a 64 bit connector in SSIS.
August 12th, 2010 1:23pm

your package will work on any inviroment but the thing is is will work as a 32bit package. how youcan all the package you must use a 32 bit DTEXEC to call the package check http://msdn.microsoft.com/en-us/library/ms162810.aspx it will provide every thing in detail -------------------------------------------------------------------- you may need something like SET @JobCommand = '"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" ' SET @JobCommand = @JobCommand + N' /FILE ' + @MainFolderPath + '\PackageAndConfigFile\PkgETL-SSIS-PKGName.dtsx ' SET @JobCommand = @JobCommand + N' /CONFIGFILE ' + @MainFolderPath + '\PackageAndConfigFile\SSIS-XmlDestinationDBConfig-PKGName.dtsConfig ' SET @JobCommand = @JobCommand + N' /CONFIGFILE ' + @MainFolderPath + '\PackageAndConfigFile\SSIS-XmlFolderConfig-PKGName.dtsConfig ' SET @JobCommand = @JobCommand + N' /CONFIGFILE ' + @MainFolderPath + '\PackageAndConfigFile\SSIS-XmlMailConfig-PKGName.dtsConfig ' SET @JobCommand = @JobCommand + N' /CONFIGFILE ' + @MainFolderPath + '\PackageAndConfigFile\SSIS-XmlSourceDBConfig-PKGName.dtsConfig ' -------------------------------------------------------------------- you may need to use ONE of the mentioned lines depending on your DTEXEC location... SET @JobCommand = '"E:\Program Files\Microsoft SQL Server (x86)\90\DTS\Binn\DTExec.exe" ' SET @JobCommand = '"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" ' -------------------------------------------------------------------- Side note.... to config your pakage http://msdn.microsoft.com/en-us/library/cc895212.aspx Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
Free Windows Admin Tool Kit Click here and download it now
August 12th, 2010 4:38pm

For excel we do not have a 64 bit connector in SSIS. Hi Sudeep Yes you are right but, we kind of do have one, but is for higher versions of Excel (2010) see http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en http://dougbert.com/blogs/dougbert/archive/2010/04/05/download-beta-of-new-64-bit-excel-driver.aspx Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
August 12th, 2010 4:55pm

Hi, You need to execute SSIS using 32-bit runtime. To do this in BIDS you can set Run64BitRuntime for false. You can find this at Project Properties -> Debugging If you are execting this from SQL agent or DTEXEC you need change way you are currently execute. For detail kindly visit this link. http://toddmcdermid.blogspot.com/2009/10/quick-reference-ssis-in-32-and-64-bits.html Hope this helps. -Chintak But development PC is 32 bit, could not found the setting Run64BitRuntime? Is it only apply in 64 bit OS?Judy
Free Windows Admin Tool Kit Click here and download it now
August 13th, 2010 9:46am

I faced this issue yesterday and all i needed to do was set the properties of the solution to RUN6rBitRunTime to FALSE. Solution->Properties->Debugging->RUN64BitRunTime =FALSE For excel we do not have a 64 bit connector in SSIS. How is if we still need to use the Excel 2003 and below? Totally cannot export to those file?Judy
August 13th, 2010 9:48am

your package will work on any inviroment but the thing is is will work as a 32bit package. how youcan all the package you must use a 32 bit DTEXEC to call the package check http://msdn.microsoft.com/en-us/library/ms162810.aspx it will provide every thing in detail -------------------------------------------------------------------- you may need something like SET @JobCommand = '"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" ' SET @JobCommand = @JobCommand + N' /FILE ' + @MainFolderPath + '\PackageAndConfigFile\PkgETL-SSIS-PKGName.dtsx ' SET @JobCommand = @JobCommand + N' /CONFIGFILE ' + @MainFolderPath + '\PackageAndConfigFile\SSIS-XmlDestinationDBConfig-PKGName.dtsConfig ' SET @JobCommand = @JobCommand + N' /CONFIGFILE ' + @MainFolderPath + '\PackageAndConfigFile\SSIS-XmlFolderConfig-PKGName.dtsConfig ' SET @JobCommand = @JobCommand + N' /CONFIGFILE ' + @MainFolderPath + '\PackageAndConfigFile\SSIS-XmlMailConfig-PKGName.dtsConfig ' SET @JobCommand = @JobCommand + N' /CONFIGFILE ' + @MainFolderPath + '\PackageAndConfigFile\SSIS-XmlSourceDBConfig-PKGName.dtsConfig ' -------------------------------------------------------------------- you may need to use ONE of the mentioned lines depending on your DTEXEC location... SET @JobCommand = '"E:\Program Files\Microsoft SQL Server (x86)\90\DTS\Binn\DTExec.exe" ' SET @JobCommand = '"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" ' -------------------------------------------------------------------- Side note.... to config your pakage http://msdn.microsoft.com/en-us/library/cc895212.aspx Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s). Not understand this, need to take my time to explorer first. TQJudy
Free Windows Admin Tool Kit Click here and download it now
August 13th, 2010 9:49am

For excel we do not have a 64 bit connector in SSIS. Hi Sudeep Yes you are right but, we kind of do have one, but is for higher versions of Excel (2010) see http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en http://dougbert.com/blogs/dougbert/archive/2010/04/05/download-beta-of-new-64-bit-excel-driver.aspx Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s). Is it mean if some of my SSIS only can run in 32 bit, i need to change all the SSIS to only run in 32 bit although some of SSIS after re configurate was able to run successful in 64 bit? I have 20++ SSIS, some of the SSIS i was test in 64 bit with no problem after i do some configuration, meaning i need to change back to 32 bit version, and just allow it run in 32 bit enviroment rather than 64 bit althought my OS is 64 bit?Judy
August 13th, 2010 9:53am

HI Jusdy you are right it's hard to make 64bit packages then deploy it as 32 and etc... bla bla bla... i hear what youare saying but to make it dirt simple to remember (not to professional) if you have 1- Excel or any file based (the small boys ) source or destination in you package it will be 32bit 2- anything else (tables, SQL, Oracel,MYSQL ) (the big boys ) can be 32 or 64, depends on your designSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
Free Windows Admin Tool Kit Click here and download it now
August 13th, 2010 4:17pm

you may need to use ONE of the mentioned lines depending on your DTEXEC location... SET @JobCommand = '"E:\Program Files\Microsoft SQL Server (x86)\90\DTS\Binn\DTExec.exe" ' SET @JobCommand = '"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" ' -------------------------------------------------------------------- Side note.... to config your pakage http://msdn.microsoft.com/en-us/library/cc895212.aspx Not understand this, need to take my time to explorer first. TQ Judy if you are calling a 32 bit package you have to use a 32bit DTEXEC.exe file , what i did just to loacate the fies on a production server and the @JobCommand is the command in a job that calls the pacakge by using the 32bit DTEXEC.exe file cehck the link.Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
August 13th, 2010 4:20pm

Hi Nik, I uderstand your solution however i am getting error when add full path to @cmd. I am trying to call SSIS pacakge execution from store procedure which is being called from osql from other server. In store procedure I tried adding full path but it does now work. Here is my code... SET @cmd = ' "c:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /SQL "\ETL_NexusJDE\LoadRetroPD" ' When compile store procedure i get following error.... '\Program' is not recognized as an internal or external command, I can change directory from command line and it works fine without adding full path to store procedure but within store procedure it does not work. any suggestions????? Thank you, -grtGUY_007
Free Windows Admin Tool Kit Click here and download it now
October 29th, 2010 10:26am

Maybe you dont have access to the C: drive or maybe its shared, please type out the SP (Stored procedure) and remove confidential information , and correct me if i am wrong you just want to run it in a SP right? not in a Job? shSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
October 29th, 2010 12:51pm

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[sp_executeRunSSISPackage_LoadRetroPD] @returncode int = null , @Parm1 varchar(10) , @Parm2 varchar(10) , @Parm3 varchar(10) , @Parm4 varchar(10) , @Parm5 varchar(10) , @Parm6 varchar(10) , @Package varchar(200) = null AS BEGIN DECLARE @cmd VARCHAR(2000) , @params nvarchar(400) EXEC @returncode = xp_cmdshell @cmd2 SET @cmd = ' "c:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" -d /SQL "\ETL_NexusJDE\LoadRetroPD"' SET @cmd = @cmd + ' /set \Package.Variables[User::Parm1].Properties[Value];"' + @Parm1 + '"' SET @cmd = @cmd + ' /set \Package.Variables[User::Parm2].Properties[Value];"' + @Parm2 + '"' SET @cmd = @cmd + ' /set \Package.Variables[User::Parm3].Properties[Value];"' + @Parm3 + '"' SET @cmd = @cmd + ' /set \Package.Variables[User::Parm4].Properties[Value];"' + @Parm4 + '"' SET @cmd = @cmd + ' /set \Package.Variables[User::Parm5].Properties[Value];"' + @Parm5 + '"' SET @cmd = @cmd + ' /set \Package.Variables[User::Parm6].Properties[Value];"' + @Parm6 + '"' SET @cmd = @cmd + ' /SERVER <ServerName> /X86 /CHECKPOINTING OFF /REPORTING E ' --Print @cmd EXEC @returncode = xp_cmdshell @cmd SELECT @returncode END /** Test Code! Execute sp_executeRunSSISPackage_LoadRetroPD @Parm1 = '231510101' , @Parm2 = 'Y', @Parm3 = 'Y' , @Parm4 = '109001', @Parm5 = '109365', @Parm6 = '1', @returncode = '0' **/ GO
Free Windows Admin Tool Kit Click here and download it now
October 29th, 2010 5:24pm

Yes I need to keep this as a SP not a Agent Job!
October 29th, 2010 5:28pm

This is my check list as a suggestion to you 1- check if xp_cmdShell is active see sys.xp_cmdShell and "Surface area cinfiguration" in SQL see sp_configure 'xp_cmdshell', '1' and http://www.mssqltips.com/tip.asp?tip=1020 2- lets test the xp_cmdshell by calling the calc.exe like SET @cmd = 'calc' EXEC @returncode = xp_cmdshell @cmd 3- test your package a a simple packge step by step , (Without variables ) SET @cmd = ' "c:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /SQL "\ETL_NexusJDE\LoadRetroPD"' SET @cmd = @cmd + ' /SERVER <ServerName> /CHECKPOINTING OFF ' this is my sample that i use "E:\Program Files\Microsoft SQL Server (x86)\90\DTS\Binn\DTExec.exe" /SQL "\Pkgname" /SERVER MySQLServerName /MAXCONCURRENT " -1 " /CHECKPOINTING OFF QUESTION you are using /X86 and -d i dont know why please check syntax at http://msdn.microsoft.com/en-us/library/ms162810.aspx and read the -d or /d part??????? i think that you havethe syntax wrong 4- add teh variables AFTER the SERVER NAME SET @cmd = ' "c:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /SQL "\ETL_NexusJDE\LoadRetroPD"' SET @cmd = @cmd + ' /SERVER <ServerName> ' SET @cmd = @cmd + ' ADD THE VARIABLES IN THIS SECTION ' SET @cmd = @cmd + ' /CHECKPOINTING OFF ' 5- its a Question , WHAT is cmd2 in your code??? its not defined variable and if it is it has no values. Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
Free Windows Admin Tool Kit Click here and download it now
November 1st, 2010 10:30am

SET @cmd = ' "c:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" -d /SQL "\ETL_NexusJDE\LoadRetroPD"' SET @cmd = @cmd + ' /SERVER <ServerName> /X86 /CHECKPOINTING OFF /REPORTING E ' Are you missing your SERVERNAME ????????. Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
November 1st, 2010 11:16am

Hi Nik, Thank you for your time and support! Here is result of your suggestions. 1 xp_cmdShell was enable. I have double check. 2 When I tried testing calc.exe. I got long wait. I think that might be my 1st problem. Here is my test code for that.. DECLARE @cmd VARCHAR(2000) , @returncode INT SET @cmd = 'calc.exe' EXEC @returncode = xp_cmdshell @cmd Here is what i evantually got back after about 40 mins of wait Msg 15121, Level 16, State 21, Procedure xp_cmdshell, Line 1 An error occurred during the execution of xp_cmdshell. A call to 'CreateProcess' failed with error code: '2'. 3 I still get same error when I try testing my package without parameter. Here is my test code for that. DECLARE @cmd VARCHAR(2000) , @params nvarchar(400) , @returncode INT SET @cmd =' "c:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /SQL "\ETL_NexusJDE\LoadRetroPD" ' SET @cmd = @cmd + ' /SERVER vda0bis02 /X86 /CHECKPOINTING OFF /REPORTING E ' EXEC @returncode = xp_cmdshell @cmd Here is an error i get... 'c:\Program' is not recognized as an internal or external command, operable program or batch file. NULL 4 Have not tried yet... 5 @cmd2 was just test i was trying based on some other article i had read. It does not suppose to be used. Also, a you are right there is no need to use switch -d I have removed it. b I had used x86 to test if its 32 bit related issue or not. c I did have server name but had removed it but now I am pasting actual code I am testing. Again Nik Thank you for your time. I am really out of ideas so any help you provide will be great. Thank you, -grtGUY_007
Free Windows Admin Tool Kit Click here and download it now
November 1st, 2010 11:40am

About the TEST for the cmdShell i mentioned that to use calc.exe sorry you need to use EXEC xp_cmdShell 'Dir C:\' to test , sorry i got i wrong with another testing code. DECLARE @Str as nVarChar(500) SET @Str = 'Dir C:\' SELECT @Str EXEC xp_cmdShell @Str ------------------------------------------------------------------------------------------------- remove the /X86 i never use it and i have provided the exact script that i am using. another solution is 1- make a pacakge that makes a folder on the C drive only, nothing elase 2- in a SQL JOB make a cmd step something like ... "E:\Program Files\Microsoft SQL Server (x86)\90\DTS\Binn\DTExec.exe" /SQL "\Pkgname" /SERVER MySQLServerName /MAXCONCURRENT " -1 " /CHECKPOINTING OFF make it simple so that you know it will run 3- change the package and add a variable to be passed like ..... "E:\Program Files\Microsoft SQL Server (x86)\90\DTS\Binn\DTExec.exe" /SQL "\Pkgname" /SERVER MySQLServerName /set \Package.Variables[User::Parm3].Properties[Value];"12" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF Now you know how to pass and check the parameter to a pacakge through a SQL Job 4- now convert the JOB to a SP 5- once it works remove the package that makes folder only and add your first package (main package) with the right parameters, add the parameters one by one. Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
November 1st, 2010 1:00pm

Hi Nik, xp_cmdshell 'Dir c:\' works fine! 1 I copied my package to C: drive removed /x86 but it still gives me same error. DECLARE @cmd VARCHAR(2000) , @params nvarchar(400) , @returncode INT --SET @cmd = 'DIR c:\' SET @cmd =' "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /F "C:\LoadRetroPD" ' SET @cmd = @cmd + ' /SERVER vda0bis02 /CHECKPOINTING OFF /REPORTING E ' EXEC @returncode = xp_cmdshell @cmd here is the error 'C:\Program' is not recognized as an internal or external command, operable program or batch file. NULL I am unable to move forward with additional testing. I cant get pass this error. Thank you, -grtGUY_007
Free Windows Admin Tool Kit Click here and download it now
November 1st, 2010 2:19pm

Wait a sec, I see that your package is implimented within BI or SQL server and now you are telling me that the package is copied???? Question : is you package exported to SQL server in the BI ? (Open SSMS -> open BI -> see the package) OR its a file based package ? (in a C dirve or any folder)??? Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
November 1st, 2010 4:24pm

Hi Nik, Thank you for you time and I am sorry if i am being short in knowledge. May be I misunderstood you. My packages are installed in Integration Server. When you ask me to test it from C drive; I thought you were asking me to test running package from C: drive. So I export the package from Integration Server to C: drive and then change storage /SQL to /F. Isn't that's what you ask me do? Honestly at this point I dont care where actual package is rested. I would just like to call using following call. DECLARE @cmd VARCHAR(2000) , @params nvarchar(400) , @returncode INT SET @cmd =' "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe"/SQL "\ETL_NexusJDE\LoadRetroPD" ' SET @cmd = @cmd + ' /SERVER vda0bis02 /CHECKPOINTING OFF /REPORTING E ' EXEC @returncode = xp_cmdshell @cmd here is the error i cant get pass. 'C:\Program' is not recognized as an internal or external command, operable program or batch file. NULL I know you are using full directory path to call DTExec.exe but it does not work for me. What am i doing wrong? Thank you, grtGUY_007
Free Windows Admin Tool Kit Click here and download it now
November 1st, 2010 4:45pm

Q: Isn't that's what you ask me do? A: No I am leaving the office and reply tomorrow, sorry i have to go, just email me as SNIKKHAH@Live.ca so that i remeber that i have to reply to you thanksSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
November 1st, 2010 4:50pm

Nik, Thank you for your time. We resolve this. We ended up coping 32 bit DTEXec.exe to 64 bit directory as DTExec32.exe and change out store procedure to call DTExec32.exe instead of DTExec.exe. This resolved above problem. Every one thank you for your time and support. Thank you, -grtGUY_007
Free Windows Admin Tool Kit Click here and download it now
November 4th, 2010 2:40pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics