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